grouping and summing query

[ShostyFan]

I have a table that stores a physical inventory count. Users scan products into the table with a default quantity of 1, so if there are three items, the product will be scanned 3 times.


I have been exporting the data into excel and doing a sumif but I want a query to do this for me. Ideally, the query will sum the quantities for each unique date. Thanks.

[June7]

Have you tried to build an aggregate query with the query builder? Or build a report using its Sorting & Grouping features with aggregate calcs in group and report footers? Access Help has guidelines or search web.

[ShostyFan]

I would like the results to be in a query, and I have done the aggregate query but the date seems to be using the time as well so everything is unique. at least that is what I think is going on, or it could just not be working. I thought I would throw my post/problem out to see what others have done, seems like a solved problem but I could not find something that made sense on the "internet".

[orange]

You may have some issues with Access if your background is Excel. Database is different than spreadsheet.

See this article re Inventory and stock taking for basic concepts.

Access Date() function shows date only; Now() function shows date and time

[June7]

Extract the date portion from value and apply filter or grouping criteria to that constructed field.

CDate(Format([fieldname], "mm/dd/yyyy"))

[ShostyFan]

that worked exactly as desired, thanks June7.