Google Answers Logo
View Question
Q: Simply query to build an invoice ( Answered 5 out of 5 stars,   1 Comment )
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

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)

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

Please have a look at the access file here 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.


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

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

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



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

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


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

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 with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

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