Speed Up Query

[LarryE]

This database is used in a hospital comprising of 5 buildings each comprising of 1 - 4 floors (stations). Each station is crewed by up to 3 people and each station has its own table in the database and the structure of these tables are mostly identical. The query is supposed to go through all the tables and throw out a list of all the 54 people crewing the stations on any given day. The day in question is defined by the date shown on an already opened form "StartUp". I hope this is understandable. BTW I am far from an Access expert and am just happy that the database runs.

Then based upon what limited information you have given, you will need to structure your database something like this to at least begin to do what you wanted:

but this is just a very beginning basic design suggestion. See how you only keep each piece of information in one place, and tables are related by Primary Keys (with the Key Symbol) and Foreign Keys. They call it a "relational" system for this very reason.

1. WorkDateID in the WorkDate table is the Primary Key for WorkDateID in the Building table
2. BuildingID in the Building table is the Primary Key for BuildingID in the Floor table
3. Floor table also has a Foreign Key relationship with the Employee table because each employee may work on any floor in any building on any (multiple) WorkDates.

See how that works?

 

[Ken Sheridan]

I don't think that will work. Firstly you need tables to model the principal entity types. In broad outline:

Employees
....EmployeeID (PK)
....FirstName
....LastName
....other attributes

Buildings
....BuildingID (PK)
....BuildingName
....Address
....other attributes

Floors
....FloorID (PK)
....FloorNumber
....BuildingID (FK)
....other attributes

All attributes of the above must be functionally determined solely by the whole of the key of course.

Then a table to model the binary relationship type between employees and floors:

FloorManning
....FloorID (FK)
....EmployeeID (FK)
....WorkDate

The above three columns are a candidate key, so can other be made the composite primary key, or can be included in a single unique index and the table given a surrogate autonumber key. This table should not include a BuildingID column as this is determined by FloorID, which is only part of the key. WorkDate is an attribute of the relationship between employees and floors.

It might be necessary to include a table which models the constraint as to which employees can work at each station by a further table:

EmployeeStations
....EmployeeID (FK)
....FloorID (FK)

This table is all key, with the composite primary being referenced by the composite foreign key in FloorManning in an enforced relationship.