jweitzel
asked on
Access 2000 Select Query Sum Total, etc. help please.
Hi Experts!
I have a select query, built out of 8 tables. I can't change the tables. I have a query that takes a info from each table. Here are the columns I am battling with:
CreatedDate Quantity Test InvoiceID Price ProjectID
What I want to do is get the "Quantity" of the "Test" * "Price" for each "ProjectID"
The problem is that the original designer of the Database has the data appearing is in this example:
10/04/04 1 ammonia 3334 10.00 04la002
10/04/04 1 ammonia 3334 10.00 04la002
10/04/04 1 Lead 3334 12.00 04la002
10/04/04 1 Lead 3334 12.00 04la002
10/04/04 1 ammonia 3338 10.00 04la033
10/04/04 1 ammonia 3338 10.00 04la033
What I need to report would be:
04LA002
10/04/04 2 ammonia 20.00
2 lead 24.00
Total: 48.00
04la033
10/04/04 2 ammonia 20.00
Total: 20.00
I look forward to your responses, I am a relative newbie - so please be gentle!
Janine
I have a select query, built out of 8 tables. I can't change the tables. I have a query that takes a info from each table. Here are the columns I am battling with:
CreatedDate Quantity Test InvoiceID Price ProjectID
What I want to do is get the "Quantity" of the "Test" * "Price" for each "ProjectID"
The problem is that the original designer of the Database has the data appearing is in this example:
10/04/04 1 ammonia 3334 10.00 04la002
10/04/04 1 ammonia 3334 10.00 04la002
10/04/04 1 Lead 3334 12.00 04la002
10/04/04 1 Lead 3334 12.00 04la002
10/04/04 1 ammonia 3338 10.00 04la033
10/04/04 1 ammonia 3338 10.00 04la033
What I need to report would be:
04LA002
10/04/04 2 ammonia 20.00
2 lead 24.00
Total: 48.00
04la033
10/04/04 2 ammonia 20.00
Total: 20.00
I look forward to your responses, I am a relative newbie - so please be gentle!
Janine
I'm not sure how "relatively newbie" you might be (no offense intended), but if you need additional assistance with how to implement this, or if you really meant to have a report rather than a query, just post back and I can be a little more detailed.
ASKER
HI pique tech!
Thanks for the reply, no offense taken! I do "OK" with Access considering I'm sort of learning as I go (with the help of you experts!) My intention is to create a report off of this query - so..if this can be solved at the report level - I'm happy with that option. To give you an idea of my ability level - my first question about your answer would be - where would I put that text? I presume in the SQL view? I only provided the column headings I am having trouble with - there are other things going on with this query, could I add your formatting to the end of what already appears in SQL view without upsetting the other stuff?
Janine
Thanks for the reply, no offense taken! I do "OK" with Access considering I'm sort of learning as I go (with the help of you experts!) My intention is to create a report off of this query - so..if this can be solved at the report level - I'm happy with that option. To give you an idea of my ability level - my first question about your answer would be - where would I put that text? I presume in the SQL view? I only provided the column headings I am having trouble with - there are other things going on with this query, could I add your formatting to the end of what already appears in SQL view without upsetting the other stuff?
Janine
Hang on, bad bad day, will follow up tomorrow.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are the best! Thank you so much - you explained it perfectly and I was able to follow along and get the report I needed. AA++ Well worth the wait.
Janine
Janine
SELECT ProjectID, CreatedDate, Sum(Quantity) As SumQty, Test, Sum(Quantity*Price) AS ExtendedPrice
FROM SomeTableOrQuery
GROUP BY ProjectID, CreatedDate, Test
UNION
SELECT ProjectID, CreatedDate, Null, "Total: ", Sum(Quantity*Price)
FROM SomeTableOrQuery
GROUP BY ProjectID, CreatedDate
ORDER BY ProjectID, CreatedDate, SumQty DESC