SQL to select records not in other table by date 1

[Kennelbloke]

Hi Folks

I have two tables, one that stores pet booking info (tblBookings) it has a field in it called cage. this is for the allocation of a cage at time of booking.
I also have a table (tblCages) with a list of all cages we have for both dogs and cats. What I'd like to do is show a list of cages available (based on cat or dog) at time of booking for the dates booked.
The common fields are tblCages.CageID and tblBookings.Cage with tblBookings.DateIn and tblBookings.DateOut as selectors. tblBookings.PetTypeId is 1 for cats and 2 for dogs.
Any help appreciated.

 

[SkipVought]

Hi,

So any cage can be used for either a dog or cat?

There's no indication of the cage size? Are they all the same size?

The bookings table contains historical data as well?


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

More questions:

If this is what you have:

[pre]
tblBookings
cage DateIn DateOut PetTypeId
1 7/1/2018 7/15/2018 1
1 7/15/2018 8/1/2018 2
2 7/20/2018 7/25/2018 2

tblCages
CageID
1
2
[/pre]
Do you book a cage for entire day?
Or can you have it so customer picks pet in the morning, so this cage is available already the same day for another pet? And - if so - what if someone picks their dog just before you close for the day? This cage cannot be used this day any more.

Can you move a pet from one cage to another while at your place?
Scenario: you have 2 cages. Cage 1 is booked Monday and Tuesday, cage 2 is booked Friday and Saturday. New customer wants you to keep his dog for the entire week: Monday to Sunday. Can you accommodate his pet?


---- Andy

There is a great need for a sarcasm font.

 

[Kennelbloke]

The kennels and cattery are separate so there are 80 cat cages and 30 dog ones. Cage size s not an issue.

When bookings are make from reception area which serves both. Presently the structure is similar to this...

tblBookings
BookingID
PetName
PetType
Datein
DateOut
CageID
etc...

tblCages
CageID (linked to CageID in tblBookings)
CageNo
PetType

Based upon the type of pet selected I envisage a popup form showing cages available between those dates based upon the PetType where the user selects the cage.
Cages are on a daily basis.


Hope that makes more sense.

 

[SkipVought]

Why do you have PetType in both tables since the PetType is determined by either a cat cage or dog cage?


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kennelbloke]

Sorry for the delay. Have been away working.

The PetType in tblBookings is brought in via the bookings type i.e 1 cat, 2 cats or 1 dog etc. So there is a another table tblPetTypes that has this info in it.

What I was looking at doing was using the PetTypeID to show what cages are available for, say cats etc.

Hope this makes more sense.

 

[dhookom]

I expect you would have a form for entering the booking information with a combo box [cboCageID] for selecting a cage. After updating the from and to dates and pet type, you would need code to change the Row Source property of the combo box like:

Dim strSQL as string
strSQL = "SELECT C.CageID, CageNo FROM tblCages C " & _
    "WHERE C.PetTypeID = " & Me.TxtPetType & " AND Cancelled = 0 AND " & _
    "C.CageID NOT IN (SELECT B.CageID FROM tblBookings B " & _
    "WHERE B.DateIN < #" & Me.txtEndDate & "# AND B.DateOut > #" & Me.txtInDate & "#)"
Me.cboCageID.RowSource = strSQL

I haven't tested this since I was too lazy to create the tables, form, and enter a bunch of sample records .

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Kennelbloke]

All good Duane

Looks like what I want. Will play tonight. I could get the the unused cages couldn't work out the dates bit.

Ta
Let you know.

 

[Kennelbloke]

Works a treat thanks Duane