Full Version: Date Query
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
shell9400
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
neilio42
You could use the query you have, and run a query on top of it. (Query the query.)
balaji
Look into the group by clause in SQL.
shell9400
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?
truittb
Post the SQL from your query that isn't working.
shell9400
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
truittb
What is the Date field you ned to use in the range?
shell9400
The date field is Check date.
truittb
Try this:

CODE
SELECT  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
Where Royalty.[Check Date] Between [Enter 1st Date] and [Enter last Date]
GROUP BY Royalty.Well,
    Royalty.Product,
    Well.[Royalty Interest]
shell9400
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.
shell9400
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
truittb
You are welcome.

Add a second row in the Sorting & Grouping dialog box with Well and sort it ascending.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.