[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

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
0
jweitzel
Asked:
jweitzel
  • 4
  • 2
1 Solution
 
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now