sql - Calculate total number of items received when item is received on more than one shipment -

i have set of tables in access 2007 need display total number of items received. order items against job using job number common reference (like id).

each job has multiple items required. items have multiple shipments receive. each shipment given unique receiving ticket number, need entered individually , totaled.

i have:

• tbljobitems :jobnumber, item, qtyneeded
• tblreceived :jobnumber, item, qtyrecvd, rtnumber (rt = receiving ticket)
• tbljobs : jobnumber, jobqty (and more, not relevant issue)

(jobqty not same item's qtyneeded. job run of model of computer, job qty how many of model. items 1:1, case or power supply, can 2:1 or 3:1 having multiple hard drives.)

i have query works fine show number of items placed on order, want expand (or combine other queries) show total number of items received per job number on same line. i'll use number change status , other functions.

select tbljobitems.jobnumber, tbljobitems.item, tbljobitems.qtyneeded, tbljobitems.partstatus, first(tbljobs.bdt) firstofbdt, first(dateadd("ww",-2,[bdt])) dueby tbljobitems inner join tbljobs on tbljobitems.jobnumber = tbljobs.jobnumber group tbljobitems.jobnumber, tbljobitems.item, tbljobitems.qtyneeded, tbljobitems.partstatus; 

this shows me in listbox items ordered , how many, jobnumber stored ([tempvars]![jobnum]), , listbox shows records match jubnumber. (the tempvar global, can used in query if helps anyone)

i'm not opposed having go through 2 or 3 queries answer.

it turns out key piece needed in query's sql was:

sum(tblreceived.receivedqty) sumofreceivedqty, iif(isnull([sumofreceivedqty]),0,[sumofreceivedqty]) recqty 

this sums quantity , creates new column in query totals.