Hi,

I Have a sql select query that multiplies 2 fields from 2 different tables and gets a subtotal called linetotal. Now, what I want to do is to add that subtotal(linetotal) and get a total. How would I do that? Any Ideas? I do not know how to pass the values from the query results to the next step.

I'm trying to keep it as simple as possible since I'm fairly new to SQL, but with lots of enthusiasm. I'm using classic ASP/MS Access and DWMX2004. This is what I have so far:

SELECT prodID, product_code, intQuantity * catalogue_price AS linetotal
FROM tblOrders, Products
WHERE prodID = product_code

help please, thanks.

Javier

The approach I'd take is to create another query which is broadly similar to the one you're using but instead sums intQuantity and catalogue_price before muliplying.

SELECT SUM(intQuantity) * (catalogue_price) AS total
FROM tblOrders, Products

You'll need to work on what you'd like to group by though, so I've taken out the ProdID and product_code fields

Hi Barry,

Thanks for the info, it has been very valuable for me to understand a little bit more about SQL and ASP, the road seems still long. <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>
here is the code that worked:

SELECT SUM (intQuantity * catalogue_price) AS subtotal
FROM tblOrders, Products
WHERE prodID = product_code AND userID = MMColParam

I hope you don't mind if I ask one more thing:

Now that I have a subtotal, I would like to be able to add an X Percentage for taxes. I have created a table with the tax values and as I imagine, perhaps it could be put on my page as a pull down select menu so they can use the value they need.

Would that happen in ASP or SQL? In the meantime I'll try figuring it out on my own but if anyone could give me a hint will be great and appreciated.

Cheers,

Javier

Javier,

I'm glad you're getting somewhere.

With regard to the taxes, it simply depends on how many items in the dropdown and whether you'll ever want a user to change these values via a form on your site. I tend to make everything changeable so that if I get run over by a bus, someone else can still do this without having to go to the Db.

When you create the menu/list item on your form, simple point the dynamic values to your recordset. The tax values will then be in your dropdown.

Barry

Hi Barry,

Thanks for your input. I have been able to successfully calculate taxes by creating another table tblTaxes and making a relation to Provinces, which is also part of the relation with Users. All that so the tax calculation happens as soon as the User places an order.
Now, once they login, they only have to input the Product code and the quantity they want. The cost is added automatically as well as the taxes. However, the tax calculation is only visible if I go back on the browser and refresh. A simple refresh does not work. I have to go back and refresh. Any Ideas, how can I reflect the calculation values the same way the subtotal does. Do I make any sense? Also, I'm trying to do this on one page, should I be using a second page?

I'm using the following query:

SELECT SUM (intQuantity * catalogue_price* GST/100) AS GSTsub
FROM tblOrders, Products, tblTaxes
WHERE prodID = product_code AND userID = MMColParam
-------------------------------------------------------------------------
MMColParam 0 Session("intUserID"


Cheers,

Javier

Javier,

I recently had a similar problem which I solved by effectively posting the form back to itself. The label on the submit button read finalize order or Calculate total or some such thing.

You can use the textfieldvalue = request.form("mytextfield" method to hold the data in your form.

Ideally if you can use 2 pages since its easier to develop however the user suffers!

Hi Barry,
Thanks for your input. I was however able to solve that problem. My error was that my Insert form was redirected to the wrong page. Now, I have something else hapenning. I have 3 kinds of taxes: GST, PST and HST. These taxes are not used by all provinces. i.e. Alberta uses only GST that is 7%. If a user from Alberta logs in and puts an order, on the calculation all 3 taxes are shown with a value when only GST should be calculated, since the other 2, PST and HST have a value of 0. I'm using the same calculated query for the 3 tax options by changing GST and GSTsub to get the result. Where am I wrong? it's getting to me. Thanks for the help.

Javier

SELECT SUM (intQuantity * catalogue_price* GST/1000) AS GSTsub
FROM tblOrders, Products, tblTaxes, tblProvince
WHERE prodID = product_code AND userID = MMColParam AND tbltaxes.ProvinceID = tblProvince.ProvinceID
-------------------------------------------------------------------------
MMColParam 0 Session("intUserID"


Edited by - am7555 on 10 Jan 2006 19:56:45

Javier,

Which of the four tables contains the field userID?

Barry

tblUserID contains the main user ID which is then passed through the session as intUserID.

I think my SQL is flawed and perhaps that is not the way to do it. As you know by now, I want to automate as much as possible the process.
first query multiplies, unit price with quantity ordered given me the line total for each item. The second query adds all the line totals, giving me the subtotal. The 3rd query adds the appropriate tax amount (depending on which province the User is) to the subtotal. and the 4 query should ideally give me the grand total. However, I'm lost now.

1st Query:
SELECT prodID, product_code, orderDate, intQuantity, intQuantity * catalogue_price AS linetotal, userID
FROM tblOrders, Products
WHERE userID = MMColParam AND orderDate &gt; # MMColParam1 # AND prodID = product_code
---------------------------------------------------------------------------------------------------------------------
MMColParam 0 Session("MM_intUserID"
MMColParam1 1/1/2003 Now() -7


2nd Query:

SELECT SUM (intQuantity * catalogue_price) AS subtotal
FROM tblOrders, Products
WHERE prodID = product_code AND userID = MMColParam
--------------------------------------------------------------------------------------------------------------
MMColParam 0 Session("intUserID"


3rd Query: tests found it to be outputting wrong results <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>

SELECT SUM (intQuantity * catalogue_price* GST/1000) AS GSTsub
FROM tblOrders, Products, tblTaxes, tblProvince
WHERE prodID = product_code AND userID = MMColParam AND tbltaxes.ProvinceID = tblProvince.ProvinceID
-----------------------------------------------------------------------------------------------------------------------------------------
MMColparam 0 Session("intUserID"


4th. Query: I havent got there yet.

thanks,


Javier