Query Totals

[UT227]

I'm trying to make a total of ammunition by caliber. There are different quantities per box, so I did calculations by multiplying boxes by amount per box. Ammunition goes in and out, so I have a field for each. I need to keep track of how much ammunition we have on hand. I made a query, but can't get it to group by caliber and make subtotals to calculate the total on hand.

[UT227]

[UT227]

[UT227]

Here's the query

[RayMilhon]

The reason you're having problems is your data structure.


what's the difference between checkedout fields and issueto fields aren't they the same thing? Secondly, you're using a flatfile structure with a relational DB. You need to break it down a little.


CustomerTable
CustomerID
lastname
firstname

AMMOTable
AMMOID
Caliber
Roundsperbox

IssuedTable
CustomerID - Foreign key to Customertable
AmmoId - Foreign Key to AmmoTable
Status - yes for checked out no for checked in
Date
AmmoUsed

[UT227]

I didn't think about an issued table. I'll give it a try. Thanks.