Making a selection on two tables and then RIGHT JOIN

[j1nk5]

Hello,

I want to comine the following queries into one so that I can alter the parameters from a webpage.

I have a users table that has the department in and I have a TimeTable Table which has dates and option codes.

I need to select everybody in a department in the example it's Logistics.
I then want to link these people to the timeTable Table based upon a field.
The problen is that I want to select only a specific week. I can do this using two queries but that isn't going to work in ASP.

The Results should be as follows:

Person Date Option
A
B 01/10/2005 1
B 02/10/2005 2
C 01/10/2005 1


The two Queries look as follows:


SELECT TimeTable.*
FROM TimeTable
WHERE (((TimeTable.Date)>=#10/24/2005# And (TimeTable.Date)<=#10/30/2005#));

The above is then saved as Query1

SELECT Users.*, Query1.*, Query1.TimeOfDay, Query1.Date
FROM Query1 RIGHT JOIN Users ON Query1.UserID = Users.ID
WHERE (((Users.Department)="Logistics"))
ORDER BY Users.Surname, Users.Firstname, Query1.TimeOfDay, Query1.Date;

Any help would be great!

Cheers,

Matt

 

[Jon K]

In Access 2000 and later versions, you can incorporate the first SQL statement in the second statement like this:

SELECT Users.*, Q1.*
FROM (SELECT TimeTable.* FROM TimeTable WHERE [TimeTable].[Date]>=#10/24/2005# And [TimeTable].[Date]<=#10/30/2005#) AS Q1 RIGHT JOIN Users ON Q1.UserID = Users.ID
WHERE Users.Department="Logistics"
ORDER BY Users.Surname, Users.Firstname, Q1.TimeOfDay, Q1.Date;

Not sure if the syntax is supported in ASP though.
.

 

[j1nk5]

Cheers!

That worked a treat cheers!!!!