Can Queries be related?

[Mari]

I am fairly new to Access.

I have two tables with a one-to-many relationship.

TABLE1
Model Number (primary key)
type
Comments


TABLE2
Model Number (foreign key)
Order Number
Order Date
Projected Delivery Date

I am running a report based on both tables directly, where ModelNumber, Type
and Comments are listed once, and all Order Info (fields in TABLE2) comprise
multi-line detail under each ModelNumber. Everything is great, however...

The whole reason I have split the info into two tables is so that I can
create my report with that ModelNumber-to-many-Orders type format via
grouping.

However, I would really like to have everything in one table, if possible
(to make weekly data importing easier).

I hoped I could possibly have all data in one table, then create two select
queries based on how the data is currently split, then form a relationship
between the two queries, and run my report from both queries. In essence,
just switching out tables for queries in my original process.

But, I see nothing that indicates there can be relationships between queries.

Any suggestions as to how I can get my report with that
ModelNumber-to-many-Orders type format using just one table?

Thanks so much!

m-

 

[Damian S]

Hi Mari,

I would strongly recommend not joining them into a single table, as you lose
the whole point of having a relational database design - data integrity and
consistency.

Be that as it may, you can relate two queries together by using a 3rd
query... eg:

Query1 - select * from TABLE1

Query2 - select * from TABLE2

Query3 - select * from Query1 inner join Query2 on Query1.FIELD = Query2.FIELD

Hope that helps.

Damian.

Damian.

 

[Mari]

Damian, thanks for your response. So, would that solve my problem with how I
want the report?

 

[Damian S]

Hi Mari,

Your current table structure should solve your reporting needs... Set up
your report with grouping on Model Number, and in the group header, put Model
Number, Type and Comments.

In the Detail section of your form put Order Number, Order Date, Projected
Delivery Date. This will format it as you have asked for.

This assumes that you have a query running your report similar to "select *
from TABLE1 left join TABLE2 on TABLE1.[Model Number] = TABLE2.[Model Number]"

Hope that helps.

Damian.

 

[Mari]

Sorry Damian,

To clarify, currently have the report setup the way I like it, based on the
two tables. I am wanting to know how to get this same report based on one
table with workarounds.

Thanks.

Hi Mari,

Your current table structure should solve your reporting needs... Set up
your report with grouping on Model Number, and in the group header, put Model
Number, Type and Comments.

In the Detail section of your form put Order Number, Order Date, Projected
Delivery Date. This will format it as you have asked for.

This assumes that you have a query running your report similar to "select *
from TABLE1 left join TABLE2 on TABLE1.[Model Number] = TABLE2.[Model Number]"

Hope that helps.

Damian.

Damian, thanks for your response. So, would that solve my problem with how I
want the report?

 

[Damian S]

Just grab the data out of the table and use groupings etc as described
earlier. Of course, since it is one table, the chance of someone making a
typo and ending up with two records instead of one (eg: Smith and Smiith are
two different things, even though we can see it's just a typo).

Damian.

Sorry Damian,

To clarify, currently have the report setup the way I like it, based on the
two tables. I am wanting to know how to get this same report based on one
table with workarounds.

Thanks.

Hi Mari,

Your current table structure should solve your reporting needs... Set up
your report with grouping on Model Number, and in the group header, put Model
Number, Type and Comments.

In the Detail section of your form put Order Number, Order Date, Projected
Delivery Date. This will format it as you have asked for.

This assumes that you have a query running your report similar to "select *
from TABLE1 left join TABLE2 on TABLE1.[Model Number] = TABLE2.[Model Number]"

Hope that helps.

Damian.

Damian, thanks for your response. So, would that solve my problem with how I
want the report?

:

Hi Mari,

I would strongly recommend not joining them into a single table, as you lose
the whole point of having a relational database design - data integrity and
consistency.

Be that as it may, you can relate two queries together by using a 3rd
query... eg:

Query1 - select * from TABLE1

Query2 - select * from TABLE2

Query3 - select * from Query1 inner join Query2 on Query1.FIELD = Query2.FIELD

Hope that helps.

Damian.

Damian.

:

I am fairly new to Access.

I have two tables with a one-to-many relationship.

TABLE1
Model Number (primary key)
type
Comments


TABLE2
Model Number (foreign key)
Order Number
Order Date
Projected Delivery Date

I am running a report based on both tables directly, where ModelNumber, Type
and Comments are listed once, and all Order Info (fields in TABLE2) comprise
multi-line detail under each ModelNumber. Everything is great, however...

The whole reason I have split the info into two tables is so that I can
create my report with that ModelNumber-to-many-Orders type format via
grouping.

However, I would really like to have everything in one table, if possible
(to make weekly data importing easier).

I hoped I could possibly have all data in one table, then create two select
queries based on how the data is currently split, then form a relationship
between the two queries, and run my report from both queries. In essence,
just switching out tables for queries in my original process.

But, I see nothing that indicates there can be relationships between queries.

Any suggestions as to how I can get my report with that
ModelNumber-to-many-Orders type format using just one table?

Thanks so much!

m-

 

[Mari]

Thanks for ALL your time!

Just grab the data out of the table and use groupings etc as described
earlier. Of course, since it is one table, the chance of someone making a
typo and ending up with two records instead of one (eg: Smith and Smiith are
two different things, even though we can see it's just a typo).

Damian.

Sorry Damian,

To clarify, currently have the report setup the way I like it, based on the
two tables. I am wanting to know how to get this same report based on one
table with workarounds.

Thanks.

Hi Mari,

Your current table structure should solve your reporting needs... Set up
your report with grouping on Model Number, and in the group header, put Model
Number, Type and Comments.

In the Detail section of your form put Order Number, Order Date, Projected
Delivery Date. This will format it as you have asked for.

This assumes that you have a query running your report similar to "select *
from TABLE1 left join TABLE2 on TABLE1.[Model Number] = TABLE2.[Model Number]"

Hope that helps.

Damian.

:

Damian, thanks for your response. So, would that solve my problem with how I
want the report?

:

Hi Mari,

I would strongly recommend not joining them into a single table, as you lose
the whole point of having a relational database design - data integrity and
consistency.

Be that as it may, you can relate two queries together by using a 3rd
query... eg:

Query1 - select * from TABLE1

Query2 - select * from TABLE2

Query3 - select * from Query1 inner join Query2 on Query1.FIELD = Query2.FIELD

Hope that helps.

Damian.

Damian.

:

I am fairly new to Access.

I have two tables with a one-to-many relationship.

TABLE1
Model Number (primary key)
type
Comments


TABLE2
Model Number (foreign key)
Order Number
Order Date
Projected Delivery Date

I am running a report based on both tables directly, where ModelNumber, Type
and Comments are listed once, and all Order Info (fields in TABLE2) comprise
multi-line detail under each ModelNumber. Everything is great, however...

The whole reason I have split the info into two tables is so that I can
create my report with that ModelNumber-to-many-Orders type format via
grouping.

However, I would really like to have everything in one table, if possible
(to make weekly data importing easier).

I hoped I could possibly have all data in one table, then create two select
queries based on how the data is currently split, then form a relationship
between the two queries, and run my report from both queries. In essence,
just switching out tables for queries in my original process.

But, I see nothing that indicates there can be relationships between queries.

Any suggestions as to how I can get my report with that
ModelNumber-to-many-Orders type format using just one table?

Thanks so much!

m-