Link to home
Start Free TrialLog in
Avatar of jweitzel
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
Avatar of pique_tech
pique_tech

This is a little kludgy, but will get close to what you want.  To get exactly the output with exactly the formatting you want, you'd need an Access report with appropriate grouping.  But as a query:

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
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.  
Avatar of jweitzel

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
Hang on, bad bad day, will follow up tomorrow.
ASKER CERTIFIED SOLUTION
Avatar of pique_tech
pique_tech

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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