By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

calculating a total

P: 98
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?
Feb 11 '09 #1
Share this Question
Share on Google+
2 Replies


DonRayner
Expert 100+
P: 489
Here is the SQL to achieve what you want.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblMain.[Tracking Number], Sum(tblPend.Days) AS SumOfDays
  2. FROM tblPend INNER JOIN tblMain ON tblPend.[Tracking Number] = tblMain.[Tracking Number]
  3. GROUP BY tblMain.[Tracking Number];
Feb 11 '09 #2

NeoPa
Expert Mod 15k+
P: 31,398
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.
Feb 13 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.