Query for Totals

[rbtrout]

I read several of the posts on queries with totals, supposedly, just as simple as clicking the summation button.....except it doesn't work properly for me.

I've got an inventory list with the partnumber, description, qtycounted and datecounted fields. It's about 5000 records. Since the same partnumber was counted in multiple places, I've could have 1 partnumber listed 6 times. I'm trying to create a final table that has each partnumber listed once with the total qty counted.
I sorted the table on partnumber and can see all the data listed that way. When I click the summation button, it drops records and still doesn't total the counts.

I've tried several different things and I'm stumped.

[Rhino373]

Do you have an example database? This should be as easy and making a totals query and grouping by partnumber and using the sum function for qtycounted. I'm guessing this isn't correct though and am puzzled as to why a partnumber would be counted twice. I'm assuming for each of the same part numbers you are not wanting to add them up, you are wanting the count for the partnumber with the newest datecounted.

If that were the case you would make 2 queries. The first is grouping by partnumber and max of datecounted. The second is not a totals query. You add the original table plus the query you just built and match on partnumber and datecounted and pull in the qtycounted.

[rpeare]

Do the simplest thing first

Create a query that has the PARTNUMBER and the QTYCOUNTED, make it a summation query, and put SUM in the TOTALS line of the query for QTYCOUNTED. Once you do this you can start adding other fields and see how it affects the the query. I suspect you are not using the TOTALS line correctly (likely having COUNT in the totals line instead of SUM for QTYCOUNTED)

[rbtrout]

@Rhino373 - The company is a nursery (plants, not babies ). We'll have the same plant (partnumber) in several different places in the yard, so each plant could be counted multiple times.

@rpeare - this worked. I feel stupid for not doing this myself, but some times we can't see the forest through all the trees. I added only the essential columns for the final table. I knew what my final line count should be, as I did the same routine in an old version of FoxPro and they matched up. Thanks for the quick, easy solution.