spaceclown
|
| Posted: 02/08/2002, 7:04 AM |
|
This is the 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
On my form (grid) that I use to view the orders made I see member_id as a number, I want to view the member_id as the Users name. So I need to look at the table called users and the column called user_name. Member_ID in the users table is called user_id (confusing, i know). So basically I guess i need a join but I don't know how to put it in my sql statement. Any ideas, and ideas on a good book that explains sql statements? Thanks again for alll your guys help
|
|
|
 |
Nicole
|
| Posted: 02/08/2002, 7:48 AM |
|
Hello,
try this one (but put correct aliases to all the fields):
select i.member_id, u.user_name
iif (sum(quantity*price)>100,100,sum(quantity*price)) as sub_total,
(sub_total+40) as total
from items as i, orders as o, users as u
where o.item_id=i.item_id and i.member_id = u.user_id
group by i.member_id
Test it in query analyzer (or directly in Access if you use it), then paste to CC.
|
|
|
|
spaceclown
|
| Posted: 02/08/2002, 8:15 AM |
|
It didnt work this is the statement i used
select items.member_id, users.user_name
iif (sum(quantity*price)>100,100,sum(quantity*price)) as sub_total,
(sub_total+40) as total
from items as item_id, orders as order_id, users as user_id
where orders.item_id=items.item_id and items.member_id = users.user_id
group by items.member_id
Orders table and users table are the only 2 tables that have the user_id/member_id. I think the statement is looking in the items able to maybe?
This is Access 2000
Can you suggest a good query analyzer?
|
|
|
|
spaceclown
|
| Posted: 02/08/2002, 9:22 AM |
|
This worked but doesnt give me the total (sub_total + 40)
select user_name,
iif (sum(quantity*price)>100,100,sum(quantity*price)) as sub_total,
from items, orders , users
where orders.item_id=items.item_id
and orders.member_id = users.user_id
group by user_name
Tried this
select user_name,
iif (sum(quantity*price)>100,100,sum(quantity*price)) as sub_total,
(sub_total+40) as total
from items, orders , users
where orders.item_id=items.item_id
and orders.member_id = users.user_id
group by user_name
But get an error and on the form in the field section total is no longer available.
Oh ya Nicole you rock, you have helped so many of us and I want to thank you for all your help.
|
|
|
|
spaceclown
|
| Posted: 02/08/2002, 11:23 AM |
|
This is getting better
Thi shows a total field on my page but it is empty
select user_name,
iif (sum(quantity*price)>100,100,sum(quantity*price)) as sub_total,
sum(sub_total+40) as total
from items, orders , users
where orders.item_id=items.item_id
and orders.member_id = users.user_id
group by user_name
I am still losted!
|
|
|
|
|
