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

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

it should be

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

on the first line...