Running a Query and displaying the results in a Message Box 2

[BajanPOET]

How would I create a query in access that adds all the Total Costs of a set of products (Total Cost being a calculated field in the query of Qty * Unit Cost) so that the Total Cost is displayed in a msgbox()?

I'm trying to allow the staff to see the total cost of the requisitions. If I create the query using the Query Design wizard so that it runs when I click the saved name in the database window, how can I get the result to show up when I press a button in a form?

What? Who? ME????

 

[AlexCuse]

I think using the DLookup function (with query name used as your table) would probably work..

Hope this Helps,

Alex



Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[BajanPOET]

That sounds like it might work.

My problem now is the query itself.

I have a line items table that is linked to a requisitions table by ReqNo. I want that for one requisition (One specific ReqNo) I can calculate the Extended Cost for each item (Extended Cost = Qty * Unit Cost) and then sum the Extended Costs for a Grand Total.

I put the Extended Cost calculation into the table as a field so all the records have the final costs in the table. My problem is that I want the total cost of all the records relating to each requisition.

So that if ReqNo # 2 has
3 mats at 2.00 = $6.00
2 coils at 5.00 = $10.00

the total cost for the requisition should be $16.00

As I'm doing it now it totals all the records for the entire table.

Any advice would be appreciated.

What? Who? ME????

 

[GingerR]

Hi. It's best to not store calculated data in tables. What if somehow 4 Widgets are added to that order? Will you then go sum it up again? I suggest instead that you just do calculations in queries, forms and reports.

As for your question above, GROUP BY the ReqNo. What's your query's SQL statement?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[AlexCuse]

It sounds like you need a group by on ReqNo. Can you post the SQL for your query?

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[BajanPOET]

Ok this is the code I got at this point. I did some modifications and got this

SELECT DISTINCTROW Items.Description, Items.Qty, Items.UnitPrice, Sum(Items.TotalPrice) AS Total Cost
FROM Requisition INNER JOIN Items ON Requisition.ReqNo = Items.ReqNo
GROUP BY Items.Description, Items.Qty, Items.UnitPrice, Items.ReqNo
HAVING (((Items.ReqNo)=[]));

This brings up the correct fields and the corresponding information, but it does not sum all the TotalPrices together to give me one lump sum, which is what I want.

What? Who? ME????

 

[BajanPOET]

Ok this is the code I got at this point. I did some modifications and got this

SELECT DISTINCTROW Items.Description, Items.Qty, Items.UnitPrice, Sum(Items.TotalPrice) AS [Total Cost]
FROM Requisition INNER JOIN Items ON Requisition.ReqNo = Items.ReqNo
GROUP BY Items.Description, Items.Qty, Items.UnitPrice, Items.ReqNo
HAVING (((Items.ReqNo)=[]));

This brings up the correct fields and the corresponding information, but it does not sum all the TotalPrices together to give me one lump sum, which is what I want.

What? Who? ME????

 

[BajanPOET]

Sorry for posting twice the adsl line at work was playing the fool.

What? Who? ME????

 

[AlexCuse]

In order to return total price by reqno you would want to only show reqno and sum(price).

Why do you need requisitions table in your query at all? I think that your join conditions may be giving you a cartesian product if a reqno can show up more than once in there...

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[PHV]

A complete query:
SELECT Description, Qty, UnitPrice, Qty * UnitPrice AS TotalCost
FROM Items
WHERE ReqNo = []
UNION ALL
SELECT 'Grand Total', Null, Null, SUM(Qty * UnitPrice)
FROM Items
WHERE ReqNo = []

Just the lump sum:
SELECT SUM(Qty * UnitPrice) AS GrandTotal
FROM Items
WHERE ReqNo = []

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

Perfect! Thanks guys! Now... how do I actually get this to show up in response to a button click?



What? Who? ME????

 

[AlexCuse]

Try setting declaring a string and setting it equal to
DLookup("fieldname", "queryname", "criteria") and placing that in your message box?

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[BajanPOET]

Ok, like DLookup("GrandTotal","ReqQuery", ReqNo.Text) where ReqNo.Text is a textbox with the current requisition number, ReqQuery is the name of the query and GrandTotal is the name of the field as determined by the line

SELECT SUM(Qty * UnitPrice) AS GrandTotal [\code]

So we're looking at something like 

[code]
TotalReqCost = DLookup("GrandTotal","ReqQuery", ReqNo.Text)

msg = msgbox("Grand Total: " & TotalReqCost)
[\code]

Right?

What? Who? ME????

 

[AlexCuse]

That is correct, but criteria argument will be

"ReqNo =""" & Forms("FormName").Controls("ReqNo.text").Value & """"

Hope this helps,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[BajanPOET]

ok... cool. Thanks again... this thing was really getting the best of me...


What? Who? ME????

 

[AlexCuse]

Glad it worked!

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[BajanPOET]

Actually, I have one more problem now... When I run the query after putting in your modification about the query field I get this error message: <app name> cannot find the form 'FormName' referred to in a macro expression or Visual Basic code.

I saved the ReqQuery as a query under the queries tab of the database window, so why can't the database "see" it?


What? Who? ME????

 

[AlexCuse]

You need to replace "FormName" with the name of your form.

hope this helps,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[BajanPOET]

oops... hehehe... silly me... lol.

thanks

What? Who? ME????

 

[BajanPOET]

Now it's telling me

Run time error 2001

You cancelled the previous operation.


waah!

this is the code for the cmd button

Private Sub cmdCalculate_Click()
  Dim TotalReqCost As Double
  
  ReqNo.SetFocus
  TotalReqCost = DLookup("GrandTotal", "ReqQuery", _
  "ReqNo =""" & Forms("Add Requisition").Controls("ReqNo").Value & """")
  
  MsgBox "Total Requisition Cost:  " & TotalReqCost, _
  vbInformation + vbOKOnly, "Calculation"
  
End Sub

What? Who? ME????