date query and duplication

[Jason Franks]

Hello

I am an amateur but enjoy a good go at access! I have a small, probably
easy, query but I cant work the bloomin thing out!

I need to create a report or 5 to show how classrooms are booked (4
classrooms). I can enter the bookings etc and can produce reports to show
by month and also by classroom. Both in date order. sorted!

However I want 2 extra things really.
1- To show what classrooms are available on what days
2- To not allow an input if the date and classroom have been taken.

For example:
CR (classroom) 1 has booking for 1st Jul and 3rd July. I want a report to
show CR1 has space on the 2nd (and further obviouslly)

I also want to not be able to enter another booking for the 1st in that
classroom.

The report has to show all 4 CR's.

I have created a table with the 4 CR's and a table for the booking sheet. I
also have a booking form and the reports as outlined above.

I am more than willing to send my basic attempt if this is needed.

Can anyone help?

Many thanks in advance

Jason Franks

 

[Jason Franks]

Jay

Many thanks for your reply.

I have looked, and looked again yet am confused!

Is there a way you could explain at a more basic level, or able to show an
example of the codes etc?

Many thanks in advance

Jay Franks

Hi Jason,

Between these 2 queries, you should be able to solve both problems. For

problem #2, you can either validate it in code or let the composit PK in
ClassroomBookings do it for you and you can handle the error in code.

If you have 2 tables:

Classrooms
--------------
CRID (Long PK)
CRName (String)

ClassroomBookings
----------------------
CRID (Long PK)
BookingDate (Date PK)

This will give you the classrooms that are vacant on one date.

Parameters dtBookingDate Date;
Select CRID, CRName From Classrooms
Where CRID Not In
(Select CRID From ClassroomBookings Where BookingDate = dtBookingDate)

If you need to know what dates a classroom is vacant in a range of dates,

create a temp table and add all the dates in your range.

ClassroomDateRange
-------------------------
DateValue (Date, PK)

Parameters lngCRID Long;
Select DateValue
From ClassroomDateRange
Where DateValue Not In
(Select Distinct ClassroomDateRange.DateValue
From ClassroomDateRange Inner Join ClassroomBookings On

ClassroomDateRange.DateValue = ClassroomBookings.BookingDate

 

[Jay Vinton]

Is there a way you could explain at a more basic level, or able to show an

example of the codes etc?

Hi Jay,

Not sure which bits to explain. If you want to know how the queries work, a book about SQL will probably be the best course. The one I use for general reference is The Practical SQL Handbook by Sandra Emerson, et. al. It covers all the basics very nicely with plenty of examples.

http://www.amazon.com/exec/obidos/tg/detail/-/0201447878/102-9117917-3990550?v=glance


If you'd like to contact me privately, I'll send the db I used to work out the examples. It's just 3 tables with a little sample data, 2 queries, and enough code to create the temp table and load the date range.

Jay
(e-mail address removed)

 

[Jason Franks]

Personal reply sent. Many thanks

Hi Jay,

Not sure which bits to explain. If you want to know how the queries work,

a book about SQL will probably be the best course. The one I use for general
reference is The Practical SQL Handbook by Sandra Emerson, et. al. It covers
all the basics very nicely with plenty of examples.

http://www.amazon.com/exec/obidos/tg/detail/-/0201447878/102-9117917-3990550?v=glance


If you'd like to contact me privately, I'll send the db I used to work out

the examples. It's just 3 tables with a little sample data, 2 queries, and
enough code to create the temp table and load the date range.