I need help with an access query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Back
Top