How to Combine/Consolidate Query Results

[GrayDave]

To track the manufacture of a product, I have 2 tables, Raw Materials and Finished Product. The primary key in Raw Materials is the unique batch number. When a batch of Finished Product is made, it will use 1 or 2 batches of Raw Material, on more than one occassion. In the Finished Product table, this is handled by 2 separate fields, RM Lot A and RM Lot B. The raw material batch has a one to many relationship with RM Lot A / RM Lot B on the Finished Product table. Ultimately, I need to see all Finished Product batches where a specific batch of raw material was used (along with other information from the tables).

Currently, I use a query to see where the raw material lot was used alone as RM Lot A, a query to see where the lot was pooled as RM Lot B, and a third query to consolidate the responses from the other queries. However the results for one record come out as a field reporting use as RM Lot A and a field reporting use as RM Lot B. Is there a way to see the results combined into a single field? Or should the Finished Prduct table be set up with one field that can accommodate more than one batch of raw material?

Thanks in advance for any assistance.

[GrayDave]

I found an earlier thread in the forum. I used UNION to marry the output of the 2 queries. This will work, everything else it just prettying it up - such as removing the repeated Product name, etc. when the same raw material is used in muliple finished product batches.

Thanks.