Speed Up Query

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:
1759341324024.png

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?
 

Attachments

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.
 
Last edited:
Here is your query reformatted to give us a chance of understanding it!
@Minty 's version is far easier to comprehend since it has a minimum of white space. If I write SQL in VBA, that is the pattern I use, not that I write much embedded SQL. I'm a huge fan of querydefs for all the reasons I have explained in the past despite hating the QBE almost as much as the SQL purists do. At least we have a far better version now than we've had for the past 30 years.

my re-fit rate was a lot lower
Mine is also but it's because I'm essentially lazy which is also why I love Access so much. I hate doing the same thing over and over. All it takes is a little step backwards so you can see more of the big picture and ask yourself, "what could change and how would I handle it". Rushing never actually saves time and it usually produces inferior results.

This is very difficult for novices because for them, a line of code written is a line of code that must be saved because it took them so long to write it and debug it and they think that normalizing data by making columns into rows in a second table is more complex for some reason. Maybe it is all that looking at spreadsheets (matrices) that causes this. The more applications you design, the easier it is for you to see the wider picture and the fewer expensive mistakes you will make. And once you learn to recognize a "repeating group", you'll never make this mistake again. Think of this as a very worthwhile lesson.
 
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.
 
Access is a unique environment. If you work in an IT department, they have a tendency to break down the work so they have people who interview the customers and define the requirements, people who design the solution, people who write code ,and people who test the solution to see if it works. In a parallel track, they have people who design the schema based on the requirements and then others who handle the care and feeding of the database once it is operational. Access is an environment where YOU become the jack of all trades. You have to be competent in a large number of discrete technical specialties. That is what makes Access development difficult and vastly rewarding. I love the "instant" gratification of talking to a customer and then coming back a little later with a working product.
 
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.
 

Users who are viewing this thread

Back
Top Bottom