spaceclown
|
| Posted: 02/07/2002, 7:21 AM |
|
I need to add the total to a table called users, column called total.
This infomation is on a page called shoppingcart.asp (hmm did I borrow some code from the bookstore,yep) the form is called total. The calculations needed are if sub_total is over $100.00 then make $100.00 then add $40.00 to make the total. The UserId is passed to this form so I know who made the order.
Insert? Update? Join tables?
Question:
How to I transfer the calculated total to table called users (userid is in a column there) and a column called total.
SQL Statement:
select member_id,
iif (sum(quantity*price)>100,100,sum(quantity*price)) as sub_total,
(sub_total+40) as total from items,
orders where orders.item_id=items.item_id
group by member_id
Database: Access 2000
Lang: .ASP
Server: W2K + IIS 5
|
|
|
|
Tom
|
| Posted: 02/07/2002, 10:24 AM |
|
I assume that your select statement works right?
select member_id,
iif (sum(quantity*price)>100,100,sum(quantity*price)) as sub_total,
(sub_total+40) as total from items,
orders where orders.item_id=items.item_id
group by member_id
Then it would be an
insert into yourtable as y(y.member_id, y.total)
select member_id,
iif (sum(quantity*price)>100,100,sum(quantity*price)) +40 as total,
from items, orders
where orders.item_id=items.item_id
group by member_id
hope this helps
|
|
|
|
Spaceclown
|
| Posted: 02/07/2002, 11:09 AM |
|
This is what I tried and it didnt work, is this what you meant?
insert into users as y(y.member_id, y.total)
select member_id,
iif (sum(quantity*price)>100,100,sum(quantity*price)) +40 as total,
from items, orders
where orders.item_id=items.item_id
group by member_id
And yes the other statement did work. hmmmm... Still stumped
|
|
|
|
spaceclown
|
| Posted: 02/07/2002, 11:16 AM |
|
Would it be update instead of insert??
|
|
|
|
Tom
|
| Posted: 02/07/2002, 12:17 PM |
|
If the user already exists and that is a key field, you have to use update
something like this ...
What is the error you are receiving or is it just not working?
update users as y
set y.total = (select iif (sum(quantity*price)>100,100,sum(quantity*price)) +40
from items, orders
where orders.item_id=items.item_id
group by member_id)
WHERE y.member_id = I_can_not_tell_which_table(items or orders).member_id
|
|
|
|
spaceclown
|
| Posted: 02/07/2002, 12:45 PM |
|
Here is the code now
update users as y
set y.total = (select iif (sum(quantity*price)>100,100,sum(quantity*price)) +40
from items, orders
where orders.item_id=items.item_id
group by member_id)
WHERE y.member_id = orders.member_id
When I save the form as a grid I get an error in the browser reading
Error Type:
Microsoft JET Database Engine (0x80040E14)
Extra ) in query expression 'member_id) WHERE y.member_id = orders.member_id'.
/template/Common.asp, line 33
I removed the ) stated and another error.
when saved as record
It does not display sub_total and total but screnn appears, when I push the insert button I get Command text was not set for the command object. (Microsoft JET Database Engine
|
|
|
|
Tom
|
| Posted: 02/07/2002, 1:50 PM |
|
Easiest way to troubleshoot this is to actually work with the sql code in access as it will provide improved error messages over ODBC. Copy the query into Access queries and run it.
One problem I see is that we are returning more than one row in the set portion of the sql statement. (it looks like all of the users are being returned)
we need to work on that. And are you just trying to update one user or the whole table. If one user, that needs to be added to the statement
something like ...
update users as y
set y.total = (select iif (sum(quantity*price)>100,100,sum(quantity*price)) +40
from items, orders
where orders.item_id=items.item_id
and y.member_id = orders.member_id
group by member_id)
WHERE y.member_id = theMemberIdYouAreTryingToUpdate
If you continue to struggle, if you want to send me just your access datbase with just the three tables involved (users,items,orders) I'll can noodle it thru as time permits.tom@salley.info
|
|
|
|
|
|
