Consolidating records by 2 different fields

[zippy483]

Good Afternoon all

I'd like a little help if you will

I have an access database that in part is used for stock accounting for a stores/parts system.

I can build a query to export the raw data that isn't a huge problem the datasheet looks something like this (simplified)

ProductTable

PartNumber	Site	ItemIn	ItemsOut	Adjustment
12345	Site1	2	0	0
12345	Site2	3	0	0
12345	Site1	0	1	0
12345	Site1	0	1	0
12345	Site2	0	1	0
12345	Site2	0	1	0
12345	Site1	1	0	1
12345	Site2	2	0	-1

However i need to consolidate the Records into by PartNumber and Site with a grand total of parts (ItemsIn - ItemsOut + Adjustment)something like below

PartNumber	Site	TotalOnHand
12345	Site1	1
12345	Site2	2

is this achievable at all, the consensus among my colleagues is that it isn't ?

Kind Regards
Zippy483

[pbaldy]

Looks easy. Start a query with the two fields plus your calculation. Click on the Totals icon in the ribbon. Change Group By to Sum on the calculation and see what you get.

[CJ_London]

is the Site2 total in hand correct? your example has 5 in, 2 out and a -1 adjustment which leaves 2, not 0

[zippy483]

is the Site2 total in hand correct? your example has 5 in, 2 out and a -1 adjustment which leaves 2, not 0

Ajax, you are of course correct, a little bit of dodgy arithmetic on my part, corrected that now