Logic for WHERE statement in a combo box?

[vavroom]

I am building a room allocation part of an existing database. This
section of the database uses a table with a date, a session, a room and
a supervisor. My table is using:

fldRoomingID - autonumber - primary key
fldDate - date field
fldSession - Text field
fldRoom - Lookup field, using room numbers from a defined list in
another table.
fldSupervisor - Lookup field, using supervisors name listed in
another table.

I created an index on fldDate, fldSession, fldRoom and flsSupervisor,
so you can't assign the same supervisor to the same room for the same
time, or any other variations on that theme.

To make data entry easier, I want to create a form where the combo box
selection of the supervisor would deliver only supervisors that
haven't been used for that date/session already. I'm not sure how
to write the criteria for it.

The SELECT statement for the combo box I have at the moment is:

SELECT tblInternalSupervisors.fldInternalSupervisorID,
tblInternalSupervisors.fldSupFName, tblInternalSupervisors.fldSupLName
FROM tblInternalSupervisors;

Obviously, I need a WHERE statement in there as well, which I'm not
wrapping my head around the logic needed for it.

Any ideas? Thanks a bunch

 

[KARL DEWEY]

You should not have your room as primary key unless you do not want to keep a
history of the room usage. With a primary key it is unique and appears only
once so you would need to keep editing the same record all the time. Also
you can not schedule the room as there will be only one record so it will
only show the one user for whatever time you enter for them so you can never
put a second one in the same room at the same time.

Your table structure probably needs to look like this –
Room –
RoomID – Autonumber – primary key
RmNUM – text
Description – text

Supervisor –
SupvrID – Autonumber – primary key
LName – text
FName – text
MI – text

RmOccupancy –
SessionID – Autonumber – primary key
RoomID – number integer – foreign key
SupvrID – number integer – foreign key
SessionST – DateTime – starting date/time
SessionEnd – DateTime – ending date/time

RoomID, SupvrID, SessionST, and SessionEnd make a multi-field primary key or
unique index.

Set a one-to-many relationship of the primary key fields to the
corresponding foreign key fields.

Your form will have a Session field to enter date/time to use in a query.
Your first query will pull records of supervisors whose session is between
SessionST and SessionEnd. . In a second query left join Supervisor table to
that query list and use Null as criteria for query supervisor. This will
pull list of supervisors not scheduled for the session.

 

[vavroom]

Hello Karl,

I am afraid I wasn't clear enough. I have not given the room as a
primary key. fldRoomingID is the primary key, but fldRoomingID is
basically what I called your OccupancyID. It is the primary key for a
unique record, in that each record will have a unique Room/Date/Session

I have a multiple field unique index on Date/Room/Session.

I was hoping to avoid having to create queries, or rather, i believe
this can be accomplished with an appropriate SELECT statement in the
combobox calling the supervisor. Technically, it would be querying the
tables, but it's not creating a query in access

Oh! And I can't change the structure of Date / Session. Date is
always "date", and the session is either AM or PM. No flexibility to
change that.

I'm pretty solid about my table(s) structure, even if I'm not able to
express it properly

 

[John Vinson]

Technically, it would be querying the
tables, but it's not creating a query in access

ummm...

Yes, you are. If it's got a SELECT statement it's a query.

Maybe not a *stored, named* query - but it's a query.

John W. Vinson[MVP]

 

[vavroom]

Yes, you are. If it's got a SELECT statement it's a query.

Maybe not a *stored, named* query - but it's a query.

Again, guilty of using English as my 4th language and not explaining
myself properly. I was trying to say "stored named query".

Thanks for picking on my language.

Now, is there an actual suggestion i can use?

 

[Larry Daugherty]

Well clearly you should ask for your money back and then take your
business where it's appreciated. ];-)

None of the volunteers here has to answer anyone's questions.

I clicked on by this thread when it first appeared because it was
pretty clear that it was likely to be "20 questions" and variations on
the theme.

My first recommendation is that you get rid of those "Lookup Fields"
in your tables and create the visible relationships instead. Yep,
it's more work at the table level but it will likely be significantly
less work in your application.

It doesn't matter how many languages you do or don't speak well. The
major burden of communicating your issues falls on you. There are
other support groups using other languages. We can't see your machine
and we don't want any attachments, thank you. By the way, my cousin
Archie's hobby was languages. He confided to me that he could get
along in 39 languages and knew 10 others well enough to cuss the taxi
drivers. I didn't test him. For his day job he was the Dean of
Science at Oregon State. RIP, Arch.

I suggest that you explain what you want to achieve in real world (not
Access) terms. Then you might tell us what you have already done.
All of that so clearly that a reasonably literate child could
understand it. Being mindful of the attitudes; both yours and ours,
don't start listing what you won't change. If you already had a
robust and complete solution you wouldn't have posted here in the
first place. You should approach these things with the attitude of
learning. When all is said and done you are going to do what you are
going to do. However, if people suggest a solution, don't refuse or
ignore it and still ask for help "doing it your way". Maybe people
don't really want to help you dig a deeper hole. We'd rather be
helping people we can help.

People here assume that you are going to develop an application with
Tables, Forms, Reports and their Queries. If that's a wrong
assumption on our part, please let us know.

HTH