Query to compare 3 total fields and calculate total (1 Viewer)

andyeastes

Registered User.
Local time
Today, 11:36
Joined
Dec 29, 2010
Messages
31
Hello,

I have three tables that have OnOrder, OpenReqs, and OnHand. They are not totalled by the primary key which is PartCode. I need to total each of them and then make a field that will include every part code from another table, RM Description Table, and give the inventory position for every part code. (OnHand+OnOrder-OpenReqs) One problem is that the OnHand, OnOrder, and OpenReqs tables do not have every part code in them. These are linked tables that are updated daily.

I have tried doing three total queries but then it will not let me do anything with those since they are not updateable???

Any ideas would be much appreciated!

Thanks,
Andy
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:36
Joined
Jan 20, 2009
Messages
12,849
Use outer joins between [RM Description] and the three other tables to return all the records from RM Description with values in fields for those that match PartCode in the others.

The field expression will be something like:

TotalQty: Nz(OnHand.Qty) + Nz(OnOrder.Qty) - Nz(OpenReqs.Qty)
 

andyeastes

Registered User.
Local time
Today, 11:36
Joined
Dec 29, 2010
Messages
31
That worked perfectly using the left outer joins and that formula.

THANKS!
 

Users who are viewing this thread

Top Bottom