Problem with SQL displaying the data I want 1

[nunan]

Hi

Could anyone help please?

I have 2 tables:

TblVehicles
Reg (pk)
Model
Colour

TblReservations
ID (pk)
Reg
ResStartDate
ResEndDate

The tables are joined by the reg fields.

I have an unbound list box and the row source is the following SQL which mainly works great:

SELECT DISTINCT TblVehicles.Reg, TblVehicles.Colour, TblVehicles.Model FROM TblVehicles INNER JOIN TblReservations ON TblVehicles.Reg=TblReservations.Reg WHERE (((TblReservations.ResStartDate) Not Between #5/1/2007# And #5/3/2007#));

The problem I am having is this:
In TblVehicles there are 10 records (i.e. 10 different vehicles.
In TblReservations there are 3 records, 1 vehicle has been reserved once and another vehicle has been reserved twice.
What I want to see in the list box is 9 vehicles because one of them is reserved during the dates in the SQL but instead, wat I am getting is 2 records because it seems to be giving me the records from TblReservations but I need them from TblVehicles as I need to see what vehicles are still available.

I do hope this makes sense and that someone can help!

Thanks in advance

 

[adalger]

Try an OUTER JOIN.

TMTOWDI - it's not just for Perl any more

 

[lespaul]

read the JOIN link below for more....


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[nunan]

Hi

I tried OUTER JOIN but got the following error:

Syntax error in FROM clause???

Leslie - I have read the aricle suggested but still can't seem to get this to work, I either get errors on the FROM clause or the JOIN clause.

Any ideas please???

Thanks

 

[lespaul]

what's the SQL you're trying now?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[nunan]

I have tried changing the join type a couple of times:

This one doesn't make any difference:
SELECT DISTINCT TblVehicles.Reg, TblVehicles.Colour, TblVehicles.Model FROM TblVehicles LEFT JOIN TblReservations ON TblVehicles.Reg=TblReservations.Reg WHERE (((TblReservations.ResStartDate) Not Between #5/1/2007# And #5/3/2007#));

This one gives a error in the FROM clause:
SELECT DISTINCT TblVehicles.Reg, TblVehicles.Colour, TblVehicles.Model FROM TblVehicles OUTER JOIN TblReservations ON TblVehicles.Reg=TblReservations.Reg WHERE (((TblReservations.ResStartDate) Not Between #5/1/2007# And #5/3/2007#));

I don't know if I should be changing or rearranging anything else?

 

[PHV]

And what about this ?
SELECT Reg, Colour, Model FROM TblVehicles WHERE Reg Not In (SELECT Reg FROM TblReservations WHERE ResStartDate Not Between #5/1/2007# And #5/3/2007#);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nunan]

Thanks PHV

I will have at look at this tonight and let you know.

 

[PHV]

Sorry, I had a typo:
SELECT Reg, Colour, Model FROM TblVehicles WHERE Reg Not In (SELECT Reg FROM TblReservations WHERE ResStartDate Between #5/1/2007# And #5/3/2007#)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nunan]

PHV you are a genius, thank you very much.

 

[nunan]

Hi

PHV I wonder if you can help with this please?

When you originally answered my problem, I changed the code slightly so that dates could be entered through a form so I have the code below:

SELECT Reg, Colour, Model, ChargeGroup FROM TblVehicles WHERE Reg Not In (SELECT Reg FROM TblReservations WHERE ResStartDate Between [FrmReservationDates].Form![StartDate] And [FrmReservationDates].Form![EndDate] OR ResEndDate Between [FrmReservationDates].Form![StartDate] And [FrmReservationDates].Form![EndDate]);

for a while I thought this was working however, I found some instances where it wasn't:

Now, supposing I have a reservation made for 5th - 10th June 07 for a certain registration number:

If I select 4th June to 6th June, the vehicle is filtered out which is correct as it will not be available on 5th or 6th.
If I select 9th June to 11th June, the vehicle is filtered out which is correct as it will not be available on 9th or 10th.
If I select 4th June to 11th June, the vehicle is filtered out which is correct as it will not be available from 5th to 10th.

BUT, if I select 6th June to 8th June, the vehicle isn't filtered out which is incorrect as it is not available on those dates.

I have tried numerous variations of the query but I can never get it to filter out the vehicle for all four possibilities.

I hope this makes sense and that you/someone can help.

Thanks very much

 

[PHV]

I'd try this:
(SELECT Reg FROM TblReservations WHERE ResStartDate <= [Forms]![FrmReservationDates]![EndDate] AND ResEndDate >= [Forms]![FrmReservationDates]![StartDate])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nunan]

Hi PHV

Thanks for your quick response.

I had to change it slightly but this seems to be working perfectly now (fingers crossed), thank you sooooooooooooo much that has been driving me mad, I can now sleep!!!

SELECT Reg, Colour, Model, ChargeGroup FROM TblVehicles WHERE Reg Not In
(SELECT Reg FROM TblReservations WHERE ResStartDate <= [FrmReservationDates].Form![EndDate] AND ResEndDate >= [FrmReservationDates].Form![StartDate])