CODEWITHSUNDEEP
ms-access
Edmond
Edmond

Reputation: 149

Queries in MS Access 2003

I am working on a database for a small hotel. Once a guest gets a room reserved they are given a parking lot card that needs to be returned as the guest check-out. I need help on writing a query that would let the user know that Parking Lot card 01 (or any card #, that has already been given out to a guest) is unavailable. Can anyone please help me set this up. Thanks.

Tbls used

Guest tbl
GuestID PK
Check in date
Check out date
Room #
Room Type Access Key Id

Access Key tbl
Access Key ID PK
Distributed - yes or no

Upvotes: 0

Views: 118

Answers (2)

Fionnuala
Fionnuala

Reputation: 91376

From your tables it seems that it is possible to get the room access key, and if Access Key table hold parking lot cards, it is possible to get that information, but these tables could be better structured.

 SELECT [Access Key ID] 
 FROM [Access Key tbl] 
 WHERE Distributed = True

It is never a good idea to have spaces in table names or field names, because it just leads to stress remembering to put square brackets round everything.

Consider a table set up like so:

Guests
GuestID
Etc.

Cards
CardID
CardType (Room or Parking)

CardsIssued
CardID
GuestID
IssueDate
ReturnDate

Upvotes: 2

Benjamin Gale
Benjamin Gale

Reputation: 13177

The details you give do not specify any relationships so based on the information you have provided I am assuming that all you want to be able to do is tell which keys are available/unavailable at any given time.

For starters you can use the following query to list all keys that have not been distributed.

SELECT tblKey.KeyID, tblKey.Distributed
FROM tblKey
WHERE (((tblKey.Distributed)=False));

Change the =False to =True to reverse the logic and show all cards that have been distributed.

If you want to look for a specific key ID to find out if it has been distributed or not use the following:

SELECT tblKey.KeyID, tblKey.Distributed
FROM tblKey
WHERE (((tblKey.KeyID)=[Enter Key ID]));

This will present the user with a window where the key ID can be entered. Query Parameters are explained in greater detail here

Upvotes: 0

Related Questions