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.

 

[The_Doc_Man]

The more applications you design, the easier it is for you to see the wider picture

If you have ever read the "Goedel, Escher, Bach: The Eternal Golden Braid" book by Douglas Hofstadter, you know there is a chapter on "the forest and the trees" - the ability to see the wider picture or to redirect your attention to detail-level interactions. Hofstadter expresses the idea that this ability - to alter your scope of attention at will - is a crucial skill for a system analyst or designer.

 

[The_Doc_Man]

My best Access "algorasm" was my security status tracker that told us which servers needed application of various patches and gave us the ability to generate reports for the department or for any specific project, or for specific O/S's. My boss HAD been doing tracking by hand and NEVER had time to do anything else as the department's project count grew. But then I built a system to allow each system admin to enter data directly and all the boss needed was to print out a selected report. He said he had saved seven hours a day from his schedule when I powered that beast up. Though given the scope of the department, with 1500 servers and 80 projects, "instant gratification" might not have been what I got. But the first version fit for real-time consumption was up in less than 3 months despite me having my own set of servers to manage.

But this is a digression. The OP needs to normalize tables first, apply indexes second, and re-think the details of the SQL queries third.