Hi!
I'm trying to do a summary query (and then report). I'm trying to show well production for lots of different entries subtotaled by type (oil, gas, liquid products). I want the total production for each type based on a time period that the user inputs. I have the form of the input. I just can't get my query. It keeps pulling by date only instead of summing the type and sorting by well name. For example, it will pull 2 line items for well 1-4 because payment was received on 2 different dates instead of adding them together. I need them to sum up the volume for the time period. Is this a query problem?
Thank you!
Shelley

You could use the query you have, and run a query on top of it. (Query the query.)

Look into the group by clause in SQL.

Well, I'm trying to query the query and it's not working....must be me!? I'll keep trying it. Thanks for the suggestion! Anything else?

Post the SQL from your query that isn't working.

This is the first query:

SELECT DISTINCTROW Royalty.Well, Royalty.Product, Well.[Royalty Interest], Sum(Royalty.[Total Volume]) AS [Sum Of Total Volume], Avg(Royalty.[Product Price]) AS [Avg Of Product Price], Count(*) AS [Count Of Royalty]
FROM Well INNER JOIN Royalty ON Well.ID = Royalty.Well
GROUP BY Royalty.Well, Royalty.Product, Well.[Royalty Interest];


Then the second query (query of the query):
SELECT Production1.Well, Production1.Product, Production1.[Royalty Interest], Production1.[Sum Of Total Volume], Production1.[Avg Of Product Price], Production1.[Count Of Royalty], Royalty.[Check Date]
FROM (Well INNER JOIN Royalty ON Well.ID = Royalty.Well) INNER JOIN Production1 ON Well.ID = Production1.Well;

Thanks for looking at this and helping me. I just want to pull by date and have those entries total the volume and average the price.
Shelley

What is the Date field you ned to use in the range?

The date field is Check date.

Try this:

Oh you are brilliant! I was looking for that 'where' clause but I didn't get what it did. It's working. Wow! Thanks so much.

One more question...how do I get the report that runs from this query to print in well name order? I have it grouped by well and it says it is sorting in ascending order...but it's not or it is in it's own world but not the way I want!?! Thanks

You are welcome.

Add a second row in the Sorting & Grouping dialog box with Well and sort it ascending.