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
jweitzelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pique_techCommented:
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
0
pique_techCommented:
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.  
0
jweitzelAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pique_techCommented:
Hang on, bad bad day, will follow up tomorrow.
0
pique_techCommented:
Ok, sorry for the delay, finally back.  Bad day continued on for a couple more.

I'll try to provide pretty direct instructions, if anything isn't clear, post back here and we'll work to clarify.

Go to the reports tab and click on Create Report in Design View.  Open up the Properties window if it isn't open (View -> Properties).  On the Data tab, select the name of the query that has the details about your projects.

Open the Sorting and Grouping panel (View -> Sorting and Grouping).  In the first three lines, on the left, select (in this order) ProjectID, Create Date, Test.  For ProjectID, Change "Group Header" and "Group Footer" to True.  For Create Date and Test, change ONLY "Group Header" to True.   You'll notice that some new sections appear on the report labeled ProjectID Header, Create Date Header, Test Header, and ProjectID Footer.

If it isn't open, open the Field list (View -> Field List).  Drag ProjectID from the Field List and drop it into the section labeled ProjectID Header.  Drag and drop Created Date and Test into the section labeled Test Header.  In addition, add two new text boxes in that same section.  For the first one, in its Properties, type the following in the Control Source:
     =Sum([Quantity])
For the second one, in its properties, type the following in its Control Source:
     =Sum([Quantity]*[Amount])
Finally, add a new text box to the section labeled ProjectID Footer and type in its Control Source:
    =Sum([Quantity]*[Amount])

For this report, you don't need any details and you don't need to display the Created Date header, so you can make those sections 0 height--drag the section below them up to effectively "close" them off--or click on the grey label bars (where Created Date Header and Detail appear) and set their heights to 0 in the Properties Panel, Format tab.

Now you'll still have to deal with the labels for all the text boxes.  You can either type something meaningful in them or delete them--just click once on the label itself and press the delete key.

Now open the report.  It should show the data you described above, though you may have to format the spacing to suit your wishes.

I hope this is relatively clear.  It's a lot to read, but I think it will make more sense when you actually try it out.  Post back here when you've tried it out to let me know how close we get, and I'll try to be a little quicker in my followup.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jweitzelAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.