Google Answers Logo
View Question
 
Q: Simply query to build an invoice ( Answered 5 out of 5 stars,   1 Comment )
Question  
Subject: Simply query to build an invoice
Category: Computers > Programming
Asked by: apcs_uk-ga
List Price: $20.00
Posted: 22 Feb 2004 09:25 PST
Expires: 23 Mar 2004 09:25 PST
Question ID: 309498
Hi,

I am new to asp / sql and need help with a simple query.

I have a table containing orders. I need a total price calculating for
each order. Yes thats it!

Table layout is

ID (primary key)
orderID
ProductDescription
Price
Quantity

I just need a query that will tell me the total price of orderid x

Please have a look at the access file here
http://www.apcs-uk.co.uk/test.mdb and write the query in the db, and
upload to somewhere so i can download it. I am posting this hear
because i need this finishing in a hurry.

Regards

APCS
Answer  
Subject: Re: Simply query to build an invoice
Answered By: answerguru-ga on 22 Feb 2004 11:28 PST
Rated:5 out of 5 stars
 
Hello apcs_uk-ga,

I have completed the query you have requested and it is shown in SQL syntax below. 

--begin query

SELECT orderID, SUM(price) AS TotalOrderPrice
FROM tblorder
GROUP BY orderID;

--end query

The query was written and designed within the file you provided, and
the output when running the query looks like this:

orderID	TotalOrderPrice
1	$87.20
2	$70.00
3	$15.00
4	$74.00

Since Google Answers does not provide any file uploading capabilities
I will explain how to create a new query from the code I have posted
above.

1. Open your database as usual in MS Access
2. From the main database window, select "Queries" from the object list
3. Within Queries, create a new query in design view.
4. Close the "Show Table" dialog that appears
5. Go to View > SQL View (you should now see a blank text window)
6. Paste the contents of the query into this window, replacing any
text that was there from before.
7. Save your query, naming your query as desired.

You should now have a new query listed in the main database window
under "Queries". Simply double click that entry to obtain the output
of the query.

Hopefully you were able to understand all of the information above -
if not please post a clarification and I will attempt to respond
promptly.

Cheers!

answerguru-ga

Request for Answer Clarification by apcs_uk-ga on 23 Feb 2004 01:47 PST
Thanks for your reply but i dont think you have read the question properly.

If order ID 1 has a product priced at 55, and the quantity is 4, then
thats 220 (just for that product), so how can the total be 87.20?

Clarification of Answer by answerguru-ga on 23 Feb 2004 08:07 PST
Hello again,

Yes from your original question it wasn't entirely clear whether
"price" was the unit price of the price for that line item. From a
database design point of view you may want to avoid doing that in the
future, but I have revised the query to meet your clarified
requirements as follows:

SELECT orderID, SUM(quantity*price) AS TotalOrderPrice
FROM tblorder
GROUP BY orderID;


Let me know if you have any problems with this one - the import
procedures are identical as those in the original question.

Cheers!

answerguru-ga
apcs_uk-ga rated this answer:5 out of 5 stars

Comments  
Subject: Re: Simply query to build an invoice
From: andrewjb-ga on 23 Feb 2004 06:49 PST
 
it should be

Select SELECT orderID, SUM(price*quantity) AS TotalOrderPrice

on the first line...

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy