calculating a total

[trixxnixon]

heres the deal,
access 97
i have 2 tables,

tblmain and tblpend

tblmain has a "tracking number" which is a primary key and has a 1 to many with tblpend.
tbl pend has a field "days".

for 1 "tracking number" from tblmain, there can be several records with "days" in tblpend

how would i have a query tally up the number of days for the 1 tracking number?

[DonRayner]

Here is the SQL to achieve what you want.

SELECT tblMain.[Tracking Number], Sum(tblPend.Days) AS SumOfDays
FROM tblPend INNER JOIN tblMain ON tblPend.[Tracking Number] = tblMain.[Tracking Number]
GROUP BY tblMain.[Tracking Number];

[NeoPa]

To get this in the Query design window :

1. Add both tables.
2. Join them by dragging the [Tracking Number] from one table over that of the other.
3. Click on the Totals button (The sigma in the Query Design toolbar).
4. Add both fields ([Tracking Number] from tblMain & Days from tblPend)
5. Leave [Tracking Number] as "Group By".
6. Change "Days" to "SumofDays: Days".
7. Change "Group By" to "Sum".

This will produce the same SQL in the query as that shown by Don.
You can always access (See; update; copy; paste) the SQL for a QueryDef (Access stored query) by selecting SQL from the View menu while the QueryDef is open. You can easily create the query that Don posted by creating a new QueryDef and simply pasting the SQL in to that window from here. If you switch back to "Design" view you will see the same thing displayed in the usual way.