J
Jay
Hello,
I had a question about how to appropriately design a query and report that
uses that query based on two tables that have a many to many relationship
between them.
Here are the tables involved:
1. Production table - This table has one entry for each Lot ID of product
that we produce
2. Lot Grouping table - this table has a one to many relationship with the
Production table. For various reasons we group some Lot's together into a
"Lot Group" and this table maintains that relationship. Each entry in the
Production Table has a "Group ID" field which links back to the Lot
Grouping table
3. Sample Test Scheduling table - this table maintains our testing schedule
for samples from the various Lot Groups. Periodically we have to send
samples from Lot Groups to third parties to be tested for quality and this
table has entries for that schedule. Each record in this table tells the
date when the sample should be shipped (it can be up to 17 weeks in the
future), the tests to be run on the sample, and the third party that will
be doing the testing for us. Each entry also has a "Group ID" field which
links it back to the Lot Grouping Table.
So what I would like to do is create a report that shows all sample's that
need to be shipped between two user specified dates. Now if this were all
there were then it would of course be straightforward because all of the
information would be contained in the Sample Test Scheduling Table.
However, in addition to the information contained in that table I also want
to show all of the various Lots from the Production Table that are
associated with the Group ID from the Sample Test Schedule Table.
What is happening when I make my query though is that the data from the
Sample Test Scheduling table is being repeated once for each Lot ID in the
Lot Group. What I would like to do is to have the query collapse those
down so that for each entry in the Sample Test Scheduling Table there is an
extra field in the query which lists all of the Lot ID's associated with
the appropriate Group ID, i.e. a string concatenation of the appropriate
Lot ID's.
If anyone knows how to do this I would appreciate it.
Thanks,
Jay
I had a question about how to appropriately design a query and report that
uses that query based on two tables that have a many to many relationship
between them.
Here are the tables involved:
1. Production table - This table has one entry for each Lot ID of product
that we produce
2. Lot Grouping table - this table has a one to many relationship with the
Production table. For various reasons we group some Lot's together into a
"Lot Group" and this table maintains that relationship. Each entry in the
Production Table has a "Group ID" field which links back to the Lot
Grouping table
3. Sample Test Scheduling table - this table maintains our testing schedule
for samples from the various Lot Groups. Periodically we have to send
samples from Lot Groups to third parties to be tested for quality and this
table has entries for that schedule. Each record in this table tells the
date when the sample should be shipped (it can be up to 17 weeks in the
future), the tests to be run on the sample, and the third party that will
be doing the testing for us. Each entry also has a "Group ID" field which
links it back to the Lot Grouping Table.
So what I would like to do is create a report that shows all sample's that
need to be shipped between two user specified dates. Now if this were all
there were then it would of course be straightforward because all of the
information would be contained in the Sample Test Scheduling Table.
However, in addition to the information contained in that table I also want
to show all of the various Lots from the Production Table that are
associated with the Group ID from the Sample Test Schedule Table.
What is happening when I make my query though is that the data from the
Sample Test Scheduling table is being repeated once for each Lot ID in the
Lot Group. What I would like to do is to have the query collapse those
down so that for each entry in the Sample Test Scheduling Table there is an
extra field in the query which lists all of the Lot ID's associated with
the appropriate Group ID, i.e. a string concatenation of the appropriate
Lot ID's.
If anyone knows how to do this I would appreciate it.
Thanks,
Jay