Help with View

[VonFranzken]

I have to create a view that uses 3 tables

Orders:
OrderDetails:
Users:

The orders table has a one to many relationship with the ordersdetails tables as such:

orders 'OrderReferenceID'
&
orderdetails 'DetailOrderID'

And the users table has a one to many relationship with the orders table as such:

users 'UserID'
&
orders 'OrdersUserID'

I need to "SEE" all the orders placed by orderid and user.

Here are the fields I would require for the view.

Orders: OrderDetails:

OrderReferenceID(PK) DetailOrderID(FK)
OrderUserID DetailQuantity
OrderTotal
OrderDate
OrderStatus

Users:
UserID
FullName

Thanks

 

[amrita418]

Did you try something like this?

create 		view OrderInformation
as
select 		o.orderreferenceID, 
		o.orderuserid, 
		o.ordertotal,
		o.orderdate,
		o.orderstatus,
		od.detailorderid,
		od.detailquantity,
		u.userid,
		u.fullname
from		orders o 
		inner join orderdetails od
		on (o.orderreferenceid = od.detailorderid)
		inner join users u
		on (o.orderuserid = u.userid)
order by	o.orderreferenceid,
		u.userid

Regards,
AA

 

[VonFranzken]

Hey thanks for your help on this. btw.

I got the following error messages at this line
.
Microsoft ODBC Microsoft Access Driver Syntax error in query expression (o.oderreferenceid = od.detailorderid)
inner join users us
on (o.orderuserid = u.userid)

 

[amrita418]

Well, the query was designed for sql server.

It think you should post it in the access forum.

Also try removing the '(' braces in the on clause.

something like this:

on o.oderreferenceid = od.detailorderid
inner join users u
on o.orderuserid = u.userid

Regards,
AA

 

[amrita418]

maybe you need to add an AS clause for the table aliases.

something like this
from orders as o ...

Regards,
AA