I need help with an access query

[Guest]

..When I run my query, it takes forever and then I get duplicates. My main
table has 39,795 records. When I run the query, I get 69,251 records. I have
joined all the like fields in the two tables, but still get the duplicates.
Here's the details: Table 1 germaine fields are JON, ORG, TEC. Table 2
germane fields are ORG, TEC, AMOUNT. I need a new table listing JON and
AMOUNT for each ORG (organization) and TEC (Type Equip Code). I first created
two queries that combined the ORG and TEC into a filed named ORGTEC . I used
these two queries in Query 3, which is where I'm getting the dupes. I'm sure
there's a better way to do this. Can anyone help?

Jan

 

[Guest]

Jan,

If you post your SQL, it would be easier to determine where your problem
might lie.

Your duplicate records come from having a one to many relationship between
Table 1 and Table 2. You will need to create an aggregate query that would
look something like:

SELECT T1.ORG, T1.JON, T1.TEC, SUM(T2.Amount) as TotalAmount
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.Org = T2.Org AND T1.TEC = T2.Tec
GROUP BY T1.ORG, T1.JON, T1.TEC

HTH
Dale