Complicated Query - combine two records into one record (kind of)

[jonathanjilla]

Hi All,

I'm not really an access expert, just figured it out as I needed to. I have googled and searched every forum possible for an answer for my current problem but have had no luck. I'd appreciate any and all help from you folks.

Here's the situation... I have an inventory management system for a small chain of gas stations (There are three types/grades of gasoline - they are 87,89,91 - which are tracked separately).
1. I enter how much gas we sold of each grade every day.
2. I also enter inventory readings taken at the end of the day for each grade.
3. I also enter any gasoline delivery we receive.

I need a report at the end of the month for each grade showing if there is any shortage or overage in gasoline (due to theft, leaks and so on...). My query linked to the report ALMOST gives me everything I need except for one small problem!

If I have two deliveries on the same day, the day shows up twice on my report and messes it up!! I need the query to add the two delivery quantities and show the total delivery for that day on the report. I have tried everything I can think of and had no luck.

You can run the report titled "123" and see what I mean.
the start day is "9/1/12", end day is "9/30/12" and station is "2".
the problem arises on 9/13/12 when we have two deliveries.

sample.mdb

Thanks again for you time people

[June7]

Could try using report Grouping & Sorting with aggregate calcs. Otherwise, base the report on an aggregate (Group By) query.

[jonathanjilla]

I made a Group By query and summed it. Thanks!