Help calculating totals from linked table in datasheet view

  • Thread starter Thread starter Barry A&P
  • Start date Start date
B

Barry A&P

I am trying to calculate available inventory on a datasheet form.
I have
T_PartNumbers with PNID (pk) and PartNumber (plus some other irrelevant
Fields)
T_InventoryTransactions TransID (pk), PNID (fk), QtyRecieved, QtySold.
F_BrowseParts A datasheet Form that displays a Filtered list of PartNumber
records.

I am trying to create a control on my F-Browseparts that will show the Sum?
of all Inventory transactions for the PNID on the datasheet.
so my datasheet would contain..
PNID, Description, QtyAvailable:(Calculated control sum of all records from
T_InventoryTransactions (QtyRecieved-QtySold) where PNID matches This record)

Any help would be appreciated
 
Use a totals query with PNID and Sum of all Inventory transactions for the
PNID.
Then in the query used to feed F-Browseparts, left join to the query on PNID.
 
Karl
Thanks for the help. the query totals query worked great except that i did
not mention that i have a 1 to 1 join with a temporary table T_TempAdd the
join between T_Partnumbers and T_TempAdd is on PNID i use the temp table with
a append query to send the quantities in my T_TempAdd to various places like
a parts order table ect. when i join your update query and my part numbers
table the T_TempAdd becomes un updatable.. Any Ideas??? I read a little on
un updatable queries but was lost.. i have the 1 to 1 join because each
Front end user has the Temp table on their computer for whatever data moving
they may be doing while the T_Partnumbers is in the backend.. Please Help

Thanks
Barry
 
Back
Top