Query based on related tables

[connerlowen]

HI,

I have a database that is used to create Quotations. I need help with a query. I have a table "NewPartT" and a table "NewMetalT". There is a one to many relationship between the two tables. NewPartT is on the one side and NewMetalT is on the many side. I would like a query that shows some data from each table but grouped by the record in the NewPartT.

For example the following fields are from the table NewPartT:
PartNumber = 1000
Description = Contact
EAU = 10000

The following fields are form the table NewMetalT:
Metal: Gold
MetalWeight: .5
MetalPrice: $10.00
Metal: Copper
MetalWeight: 1.6
MetalPrice: $2.00

The part has two metals that are used for some calculations in other queries. I would Like a Query to look like this.



The Query would need to put as many or as little columns as needed based on the number of metals related to the part.

Thanks,

Conner Owen

 

[Minty]

There appears to be no correlation between the two tables - how are they supposed to join?

 

[connerlowen]

that is not the full table structure. there is a PartID field as a PK in the table NewPartT and a PartID field as FK in the table NewMetalT.

 

[Minty]

Okay - what do you have so far? A picture of your query design might help and/or the SQL of the query.

 

[connerlowen]

I have many queries that I use for all of the needed calculations. I start by doing all of the calculations with the Metals in one query, then I have a second query that is based off of the first that basically takes all of the calculations from the metals and sums them all up to make a query based on only the part and then I run another query for all of the calculations at the part level.

I have not started on the query being discussed in this thread because I do not know where to start or if it is even possible. The query I need is much more complex and has a lot more fields, but the general idea is covered in my first post. Currently if I include data from the table NewMetalT the Query created a new record for each metal unless all of those fields are summed. This is not what I want, and I have no idea where to go from there.