What type of query do I use? 1

[lynxvoodoo]

Hi!

I have an access dbase with more than one table each linked to one another in some way. The question I have is what type of query do I use to call data from the different tables. e.g I would like to call "customer surname and firstname" from table-1 and "customer status and description" from table-2. I would like to display this information on the sereen.

Could somone please advise as to what type of query to use.
e.g select * FROM ..........

Thanks
Yogas

 

[jlsmithhartfiel]

assuming table-1 & table-2 are related where table-1 is the primary...

select c.surname,
       c.firstname,
       s.status,
       s.description
from table-1 c
left join table-2 s on s.custnum = c.custnum

I would use a left join if there are not always records in table-2 that match table-1, otherwise I think an inner join would suffice.

HTH,


Jessica

 

[lynxvoodoo]

Thankyou Jessica for reply.
Your reply was very useful to myself and really apprciate the help.

I just have one further question. Is it possible to link up more than 2 tables. If yes, how can this be done

Yogas

 

[jlsmithhartfiel]

It is possible. Can you give us the 3 tables you have & how they relate to each other? It will make it easier to give you the appropriate example.



Jessica

 

[lynxvoodoo]

The three example tables I am using are:

customerdetails orderdetails itemsordered

customerID orderID itemOrderedID
customerFirstName orderCustomerID orderID
customerLastName orderDate productQuantity
customerUserName orderAmount
customerPassword orderStatus


Relationship - - - >

customerId --------> orderCustomerID

orderID -------> orderID

*********************************************************************
Currently with 2 tables:

RS.Open "SELECT o.*, i.* FROM orderdetails o inner join itemsordered i on i.orderID = o.orderID WHERE orderStatus = 'Shipped' "

The above query displays the following data :
orderID
orderDate
orderQuantity
orderAmount
orderStatus

I would like to be able to display items from all 3 tables. Thus also include details from customerdetails table to the above output.

Hope you can help with this.

Also one Final question -->

I have another following query:
RS.Open "SELECT c.*, o.* FROM customerdetails c inner join orderdetails o on o.orderCustomerID = c.customerID WHERE customerUserName='" & strUserName & "'"

The above displays orderdetails of selected customers. How can I extend the query to include WHERE orderStatus = 'Shipped' "

I tried the following but it does not seem to work:

RS.Open "SELECT c.*, o.* FROM customerdetails c inner join orderdetails o on o.orderCustomerID = c.customerID WHERE customerUserName='" & strUserName & "' & orderStatus = 'Shipped' "

Thanks again in advance for you help.
Yogas

 

[jlsmithhartfiel]

Try this:

1.

RS.Open "SELECT o.*, i.*, c.* FROM orderdetails o inner join itemsordered i on i.orderID = o.orderID right join customerdetails c on o.ordercustomerid = c.customerid WHERE orderStatus = 'Shipped' "

2.

RS.Open "SELECT c.*, o.* FROM customerdetails c inner join orderdetails o on o.orderCustomerID = c.customerID WHERE customerUserName='" & strUserName & "' and o.orderStatus = 'Shipped' "

HTH,

Jessica

 

[lynxvoodoo]

Hi again.

The second select query works perfectly. As for the first one I still seem to get an error, as follows -->

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'i.orderID = o.orderID right join customerdetails c on o.orderCustomerID = c.customerID'.
/viewallshippedorders.asp, line 16

Any suggestion.

Yoags

 

[jlsmithhartfiel]

I'm not sure what to say about that error, as if you copy the code & run in sql query analyzer it seems to run fine.

Perhaps you can post that section of your .asp page? Maybe something will jump out at me.

Jessica