Speed Up Query (1 Viewer)

M

Mister-B

New member
Local time
Today, 23:47
Joined
Apr 10, 2020
Messages
26
Hi,

is there any way to speed this query up? It takes a long time to run and is requeried fairly regularly.

SELECT Startup.Datum, Startup.ID, Startup.ArbFrei, Haus1_EG.FD1, Haus1_EG.FD2, Haus1_EG.FD3, Haus1_1OG.FD1, Haus1_1OG.FD2, Haus1_1OG.FD3, Haus1_2OG.FD1, Haus1_2OG.FD2, Haus1_2OG.FD3, Haus1_3OG.FD1, Haus1_3OG.FD2, Haus1_3OG.FD3, Haus2_EG.FD1, Haus2_EG.FD2, Haus2_EG.FD3, Haus2_1OG.FD1, Haus2_1OG.FD2, Haus2_1OG.FD3, Haus2_2OG.FD1, Haus2_2OG.FD2, Haus2_3OG.FD1, Haus2_3OG.FD2, Haus2_3OG.FD3, Haus3_EG.FD1, Haus3_EG.FD2, Haus3_EG.FD3, Haus2_2OG.FD3, Haus3_1OG.FD1, Haus3_1OG.FD2, Haus3_1OG.FD3, Haus3_2OG.FD1, Haus3_2OG.FD2, Haus3_2OG.FD3, Haus3_3OG.FD1, Haus3_3OG.FD2, Haus3_3OG.FD3, Haus4_EG.FD1, Haus4_EG.FD2, Haus4_EG.FD3, Haus4_1OG.FD1, Haus4_1OG.FD2, Haus4_1OG.FD3, Haus4_2OG.FD1, Haus4_2OG.FD2, Haus4_2OG.FD3, Haus4_3OG.FD1, Haus4_3OG.FD2, Haus4_3OG.FD3, Haus5_EG.FD1, Haus5_EG.FD2, Haus5_EG.FD3, Haus1_EG.SD1, Haus1_EG.SD2, Haus1_EG.SD3, Haus1_1OG.SD1, Haus1_1OG.SD2, Haus1_1OG.SD3, Haus1_2OG.SD1, Haus1_2OG.SD2, Haus1_2OG.SD3, Haus1_3OG.SD1, Haus1_3OG.SD2, Haus1_3OG.SD3, Haus2_EG.SD1, Haus2_EG.SD2, Haus2_EG.SD3, Haus2_1OG.SD1, Haus2_1OG.SD2, Haus2_1OG.SD3, Haus2_2OG.SD1, Haus2_2OG.SD2, Haus2_3OG.SD1, Haus2_3OG.SD2, Haus2_3OG.SD3, Haus3_EG.SD1, Haus3_EG.SD2, Haus3_EG.SD3, Haus2_2OG.SD3, Haus3_1OG.SD1, Haus3_1OG.SD2, Haus3_1OG.SD3, Haus3_2OG.SD1, Haus3_2OG.SD2, Haus3_2OG.SD3, Haus3_3OG.SD1, Haus3_3OG.SD2, Haus3_3OG.SD3, Haus4_EG.SD1, Haus4_EG.SD2, Haus4_EG.SD3, Haus4_1OG.SD1, Haus4_1OG.SD2, Haus4_1OG.SD3, Haus4_2OG.SD1, Haus4_2OG.SD2, Haus4_2OG.SD3, Haus4_3OG.SD1, Haus4_3OG.SD2, Haus4_3OG.SD3, Haus5_EG.SD1, Haus5_EG.SD2, Haus5_EG.SD3
FROM Besetzung INNER JOIN (Startup INNER JOIN ((((((((((((((((Haus1_EG INNER JOIN Haus1_1OG ON Haus1_EG.ID = Haus1_1OG.ID) INNER JOIN Haus1_2OG ON Haus1_1OG.ID = Haus1_2OG.ID) INNER JOIN Haus1_3OG ON Haus1_2OG.ID = Haus1_3OG.ID) INNER JOIN Haus2_EG ON Haus1_3OG.ID = Haus2_EG.ID) INNER JOIN Haus2_1OG ON Haus2_EG.ID = Haus2_1OG.ID) INNER JOIN Haus2_2OG ON Haus2_1OG.ID = Haus2_2OG.ID) INNER JOIN Haus2_3OG ON Haus2_2OG.ID = Haus2_3OG.ID) INNER JOIN Haus3_EG ON Haus2_3OG.ID = Haus3_EG.ID) INNER JOIN Haus3_1OG ON Haus3_EG.ID = Haus3_1OG.ID) INNER JOIN Haus3_2OG ON Haus3_1OG.ID = Haus3_2OG.ID) INNER JOIN Haus3_3OG ON Haus3_2OG.ID = Haus3_3OG.ID) INNER JOIN Haus4_EG ON Haus3_3OG.ID = Haus4_EG.ID) INNER JOIN Haus4_1OG ON Haus4_EG.ID = Haus4_1OG.ID) INNER JOIN Haus4_2OG ON Haus4_1OG.ID = Haus4_2OG.ID) INNER JOIN Haus4_3OG ON Haus4_2OG.ID = Haus4_3OG.ID) INNER JOIN Haus5_EG ON Haus4_3OG.ID = Haus5_EG.ID) ON Startup.ID = Haus1_EG.ID) ON Besetzung.ID = Startup.ID
WHERE (((Startup.Datum)=[Formulare]![StartUp]![Datum]));

Thanks and kind regards,
Martin
 
That looks very much like very poorly stored data, and I suspect would benefit from being correctly normalised.

Any time a table name or field name looks like it is holding data e.g. Haus1 , Haus2 etc. it probably shouldn't be in a separate table but identified by a field with the table.

Perhaps you can describe, in plain terms, what these tables do and the relationship between them.
I've formatted the query below so it's easier to see/decipher.

Code: 
SELECT Startup.Datum,
       Startup.ID,
       Startup.ArbFrei,
       Haus1_EG.FD1,
       Haus1_EG.FD2,
       Haus1_EG.FD3,
       Haus1_1OG.FD1,
       Haus1_1OG.FD2,
       Haus1_1OG.FD3,
       Haus1_2OG.FD1,
       Haus1_2OG.FD2,
       Haus1_2OG.FD3,
       Haus1_3OG.FD1,
       Haus1_3OG.FD2,
       Haus1_3OG.FD3,
       Haus2_EG.FD1,
       Haus2_EG.FD2,
       Haus2_EG.FD3,
       Haus2_1OG.FD1,
       Haus2_1OG.FD2,
       Haus2_1OG.FD3,
       Haus2_2OG.FD1,
       Haus2_2OG.FD2,
       Haus2_3OG.FD1,
       Haus2_3OG.FD2,
       Haus2_3OG.FD3,
       Haus3_EG.FD1,
       Haus3_EG.FD2,
       Haus3_EG.FD3,
       Haus2_2OG.FD3,
       Haus3_1OG.FD1,
       Haus3_1OG.FD2,
       Haus3_1OG.FD3,
       Haus3_2OG.FD1,
       Haus3_2OG.FD2,
       Haus3_2OG.FD3,
       Haus3_3OG.FD1,
       Haus3_3OG.FD2,
       Haus3_3OG.FD3,
       Haus4_EG.FD1,
       Haus4_EG.FD2,
       Haus4_EG.FD3,
       Haus4_1OG.FD1,
       Haus4_1OG.FD2,
       Haus4_1OG.FD3,
       Haus4_2OG.FD1,
       Haus4_2OG.FD2,
       Haus4_2OG.FD3,
       Haus4_3OG.FD1,
       Haus4_3OG.FD2,
       Haus4_3OG.FD3,
       Haus5_EG.FD1,
       Haus5_EG.FD2,
       Haus5_EG.FD3,
       Haus1_EG.SD1,
       Haus1_EG.SD2,
       Haus1_EG.SD3,
       Haus1_1OG.SD1,
       Haus1_1OG.SD2,
       Haus1_1OG.SD3,
       Haus1_2OG.SD1,
       Haus1_2OG.SD2,
       Haus1_2OG.SD3,
       Haus1_3OG.SD1,
       Haus1_3OG.SD2,
       Haus1_3OG.SD3,
       Haus2_EG.SD1,
       Haus2_EG.SD2,
       Haus2_EG.SD3,
       Haus2_1OG.SD1,
       Haus2_1OG.SD2,
       Haus2_1OG.SD3,
       Haus2_2OG.SD1,
       Haus2_2OG.SD2,
       Haus2_3OG.SD1,
       Haus2_3OG.SD2,
       Haus2_3OG.SD3,
       Haus3_EG.SD1,
       Haus3_EG.SD2,
       Haus3_EG.SD3,
       Haus2_2OG.SD3,
       Haus3_1OG.SD1,
       Haus3_1OG.SD2,
       Haus3_1OG.SD3,
       Haus3_2OG.SD1,
       Haus3_2OG.SD2,
       Haus3_2OG.SD3,
       Haus3_3OG.SD1,
       Haus3_3OG.SD2,
       Haus3_3OG.SD3,
       Haus4_EG.SD1,
       Haus4_EG.SD2,
       Haus4_EG.SD3,
       Haus4_1OG.SD1,
       Haus4_1OG.SD2,
       Haus4_1OG.SD3,
       Haus4_2OG.SD1,
       Haus4_2OG.SD2,
       Haus4_2OG.SD3,
       Haus4_3OG.SD1,
       Haus4_3OG.SD2,
       Haus4_3OG.SD3,
       Haus5_EG.SD1,
       Haus5_EG.SD2,
       Haus5_EG.SD3
FROM Besetzung
INNER JOIN (Startup
            INNER JOIN ((((((((((((((((Haus1_EG
                                       INNER JOIN Haus1_1OG ON Haus1_EG.ID = Haus1_1OG.ID)
                                      INNER JOIN Haus1_2OG ON Haus1_1OG.ID = Haus1_2OG.ID)
                                     INNER JOIN Haus1_3OG ON Haus1_2OG.ID = Haus1_3OG.ID)
                                    INNER JOIN Haus2_EG ON Haus1_3OG.ID = Haus2_EG.ID)
                                   INNER JOIN Haus2_1OG ON Haus2_EG.ID = Haus2_1OG.ID)
                                  INNER JOIN Haus2_2OG ON Haus2_1OG.ID = Haus2_2OG.ID)
                                 INNER JOIN Haus2_3OG ON Haus2_2OG.ID = Haus2_3OG.ID)
                                INNER JOIN Haus3_EG ON Haus2_3OG.ID = Haus3_EG.ID)
                               INNER JOIN Haus3_1OG ON Haus3_EG.ID = Haus3_1OG.ID)
                              INNER JOIN Haus3_2OG ON Haus3_1OG.ID = Haus3_2OG.ID)
                             INNER JOIN Haus3_3OG ON Haus3_2OG.ID = Haus3_3OG.ID)
                            INNER JOIN Haus4_EG ON Haus3_3OG.ID = Haus4_EG.ID)
                           INNER JOIN Haus4_1OG ON Haus4_EG.ID = Haus4_1OG.ID)
                          INNER JOIN Haus4_2OG ON Haus4_1OG.ID = Haus4_2OG.ID)
                         INNER JOIN Haus4_3OG ON Haus4_2OG.ID = Haus4_3OG.ID)
                        INNER JOIN Haus5_EG ON Haus4_3OG.ID = Haus5_EG.ID) ON Startup.ID = Haus1_EG.ID) ON Besetzung.ID = Startup.ID
WHERE (((Startup.Datum)=[Formulare]![StartUp]![Datum]));
 
SQL: 
SELECT 
  Startup.Datum, 
  Startup.ID, 
  Startup.ArbFrei, 
  Haus1_EG.FD1, 
  Haus1_EG.FD2, 
  Haus1_EG.FD3, 
  Haus1_1OG.FD1, 
  Haus1_1OG.FD2, 
  Haus1_1OG.FD3, 
  Haus1_2OG.FD1, 
  Haus1_2OG.FD2, 
  Haus1_2OG.FD3, 
  Haus1_3OG.FD1, 
  Haus1_3OG.FD2, 
  Haus1_3OG.FD3, 
  Haus2_EG.FD1, 
  Haus2_EG.FD2, 
  Haus2_EG.FD3, 
  Haus2_1OG.FD1, 
  Haus2_1OG.FD2, 
  Haus2_1OG.FD3, 
  Haus2_2OG.FD1, 
  Haus2_2OG.FD2, 
  Haus2_3OG.FD1, 
  Haus2_3OG.FD2, 
  Haus2_3OG.FD3, 
  Haus3_EG.FD1, 
  Haus3_EG.FD2, 
  Haus3_EG.FD3, 
  Haus2_2OG.FD3, 
  Haus3_1OG.FD1, 
  Haus3_1OG.FD2, 
  Haus3_1OG.FD3, 
  Haus3_2OG.FD1, 
  Haus3_2OG.FD2, 
  Haus3_2OG.FD3, 
  Haus3_3OG.FD1, 
  Haus3_3OG.FD2, 
  Haus3_3OG.FD3, 
  Haus4_EG.FD1, 
  Haus4_EG.FD2, 
  Haus4_EG.FD3, 
  Haus4_1OG.FD1, 
  Haus4_1OG.FD2, 
  Haus4_1OG.FD3, 
  Haus4_2OG.FD1, 
  Haus4_2OG.FD2, 
  Haus4_2OG.FD3, 
  Haus4_3OG.FD1, 
  Haus4_3OG.FD2, 
  Haus4_3OG.FD3, 
  Haus5_EG.FD1, 
  Haus5_EG.FD2, 
  Haus5_EG.FD3, 
  Haus1_EG.SD1, 
  Haus1_EG.SD2, 
  Haus1_EG.SD3, 
  Haus1_1OG.SD1, 
  Haus1_1OG.SD2, 
  Haus1_1OG.SD3, 
  Haus1_2OG.SD1, 
  Haus1_2OG.SD2, 
  Haus1_2OG.SD3, 
  Haus1_3OG.SD1, 
  Haus1_3OG.SD2, 
  Haus1_3OG.SD3, 
  Haus2_EG.SD1, 
  Haus2_EG.SD2, 
  Haus2_EG.SD3, 
  Haus2_1OG.SD1, 
  Haus2_1OG.SD2, 
  Haus2_1OG.SD3, 
  Haus2_2OG.SD1, 
  Haus2_2OG.SD2, 
  Haus2_3OG.SD1, 
  Haus2_3OG.SD2, 
  Haus2_3OG.SD3, 
  Haus3_EG.SD1, 
  Haus3_EG.SD2, 
  Haus3_EG.SD3, 
  Haus2_2OG.SD3, 
  Haus3_1OG.SD1, 
  Haus3_1OG.SD2, 
  Haus3_1OG.SD3, 
  Haus3_2OG.SD1, 
  Haus3_2OG.SD2, 
  Haus3_2OG.SD3, 
  Haus3_3OG.SD1, 
  Haus3_3OG.SD2, 
  Haus3_3OG.SD3, 
  Haus4_EG.SD1, 
  Haus4_EG.SD2, 
  Haus4_EG.SD3, 
  Haus4_1OG.SD1, 
  Haus4_1OG.SD2, 
  Haus4_1OG.SD3, 
  Haus4_2OG.SD1, 
  Haus4_2OG.SD2, 
  Haus4_2OG.SD3, 
  Haus4_3OG.SD1, 
  Haus4_3OG.SD2, 
  Haus4_3OG.SD3, 
  Haus5_EG.SD1, 
  Haus5_EG.SD2, 
  Haus5_EG.SD3
FROM Besetzung 
INNER JOIN (
  Startup 
  INNER JOIN (
    (
      (
        (
          (
            (
              (
                (
                  (
                    (
                      (
                        (
                          (
                            (
                              (
                                (
                                  Haus1_EG 
                                  INNER JOIN Haus1_1OG 
                                          ON Haus1_EG.ID = Haus1_1OG.ID
                                ) 
                                INNER JOIN Haus1_2OG 
                                        ON Haus1_1OG.ID = Haus1_2OG.ID
                              ) 
                              INNER JOIN Haus1_3OG 
                                      ON Haus1_2OG.ID = Haus1_3OG.ID
                            ) 
                            INNER JOIN Haus2_EG 
                                    ON Haus1_3OG.ID = Haus2_EG.ID
                          ) 
                          INNER JOIN Haus2_1OG 
                                  ON Haus2_EG.ID = Haus2_1OG.ID
                        ) 
                        INNER JOIN Haus2_2OG 
                                ON Haus2_1OG.ID = Haus2_2OG.ID
                      ) 
                      INNER JOIN Haus2_3OG 
                              ON Haus2_2OG.ID = Haus2_3OG.ID
                    ) 
                    INNER JOIN Haus3_EG 
                            ON Haus2_3OG.ID = Haus3_EG.ID
                  ) 
                  INNER JOIN Haus3_1OG 
                          ON Haus3_EG.ID = Haus3_1OG.ID
                ) 
                INNER JOIN Haus3_2OG 
                        ON Haus3_1OG.ID = Haus3_2OG.ID
              ) 
              INNER JOIN Haus3_3OG 
                      ON Haus3_2OG.ID = Haus3_3OG.ID
            ) 
            INNER JOIN Haus4_EG 
                    ON Haus3_3OG.ID = Haus4_EG.ID
          ) 
          INNER JOIN Haus4_1OG 
                  ON Haus4_EG.ID = Haus4_1OG.ID
        ) 
        INNER JOIN Haus4_2OG 
                ON Haus4_1OG.ID = Haus4_2OG.ID
      ) 
      INNER JOIN Haus4_3OG 
              ON Haus4_2OG.ID = Haus4_3OG.ID
    ) 
    INNER JOIN Haus5_EG 
            ON Haus4_3OG.ID = Haus5_EG.ID
  ) 
          ON Startup.ID = Haus1_EG.ID
) 
        ON Besetzung.ID = Startup.ID
WHERE Startup.Datum = [Formulare]![StartUp]![Datum];

Here is your query reformatted to give us a chance of understanding it!

Really, the first problem is that you ought to normalise your tables. Not only do you have repeating field groups, you also appear to have repeating table groups!

In the short term you must ensure that you have indexes in all fields used in joins (ON clauses), and also on field Startup.Datum
 
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.
 
Mister-B said:
each station has its own table in the database and the structure of these tables are mostly identical
Click to expand...
you need to put all tables into one and just add fields like, PK field, building Number, Station Number (floor number) to
distinguish each stations.
this will be your Master table. then on your daily records, you just add a FK and the details of the record.
 
If you pulled all the station data into one table with an couple of identifier fields for the Building and floors, you would only need then link that to your station team data.

You team data should be in a separate table referencing the location.

Something like
1759224356912.png


1759224606893.png
 
Last edited:
Mister-B said:
each station has its own table in the database and the structure of these tables are mostly identical.
Click to expand...
Irrespective of expertise in Access, what you have is a problem with the design of the database, as the above statement illuminates the issue. The development of a database requires that analysis is performed to determine how best to store the data to minimize redundancy, remove ambiguity in transaction processing systems: the process of database normalisation is a series of steps that if followed correctly addresses these issues.
Your focus is on the concept of "Station" and "People" that "Staff" the stations.
A Station is a unique location within the hospital. It will exist in a building on a floor. The Station will be assigned a type - ED, Orthopaedics, etc as needed (the location may be assigned to a different type as wards etc close and open, get renamed, absorbed etc) - so this may be held in a lookup table. for People, you will probably have a staff id as well as name and various other needed attributes for the purposes of your app (role? .. ), and Staff is the junction table of Station to People - containing the foreign key to Station and People indicating who is allocated to the Station - along with the attributes needed to indicate the date they are assigned to the specified Station. (surprised there is not more needed here - shift, shift-role, timestart/end - which could exist in a linked table for shifts).
Just a thought - you indicated that the date on the "startup" form is used as the filter used to determine the date for the query, but given the way in which the current data is structured, are you capturing this data via direct data entry in this app or by an import process involving multiple files/spreadsheets? It just seems that the current db design is mimicking a spreadsheet approach in which you have separate islands of data that really should be merged- that is a fundamental error.
However the comment
Mister-B said:
and am just happy that the database runs.
Click to expand...
would indicate that the above will not be actioned. Good luck. Others are far more experienced and knowledgeable of ways that may improve the performance of the query itself, but will not resolve the db design problem.
 
I would normalise your data.
Then you could build an index on building and floor as a compound index.
 
The truth here is to index your queries and ensure that every table involved has a primary key and properly indexed, what is delaying your query is the sorting part. I had that same problem with the query below it used to take something like five minutes before indexing now it's taking 1.5 seconds to bring the results:


Code: 
SELECT tblEmployees.EmpID, tblEmployees.Fname, tblEmployees.Lname, tblEmployees.StaffNumber, tblEmployees.JobTitle, tblEmployees.SocialSecurity, tblEmployees.NRC, tblEmployees.PayMethod, tblEmployees.BankName, tblEmployees.AccountCode, tblEmployees.PaymentType, tblPayslip.BasicSalary, tblPayslip.HousingAllowance, tblPayslip.TransportAllowance, tblPayslip.LunchAllowance, tblOtherSalary.TTDate, tblOtherSalary.XMasBonus, tblOtherSalary.OtherBonuses, tblOtherSalary.Gratuity, tblOtherSalary.LeavePay, tblOtherSalary.OtherArears, tblOtherSalary.Overtime, tblOtherSalary.DoubleTime, tblOtherSalary.OtherIncentive, tblOtherSalary.StaffLoans, tblOtherSalary.OtherDeductions, tblOtherSalary.Advances, tblNAPSA.Rate, tblNAPSA.CelingRate, tblNAPSA.NAPSAFree, tblTaxation.FreeTax, tblTaxation.BandOne, tblTaxation.BandTwo, tblTaxation.TaxRateone, tblTaxation.TaxRateTwo, tblTaxation.TaxRateThree, tblTaxation.EducationLevy, Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([FinGratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+(Nz(([Overtime]),0)*((([GrossBasic])/208)*1.5))+(Nz(([DoubleTime]),0)*((([GrossBasic])/208)*2))+Nz(([OtherIncentive]),0)+Nz(([Covid19Allowance]),0) AS Gross, IIf(((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([Covid19Allowance]),0)+Nz(([OtherBonuses]),0)+Nz(([Gratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+Nz(([Overtime]),0)+Nz(([DoubleTime]),0)+Nz(([OtherIncentive]),0))*([Rate]))>[NAPSAFree],[NAPSAFree],((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([Covid19Allowance]),0)+Nz(([OtherBonuses]),0)+Nz(([Gratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+Nz(([Overtime]),0)+Nz(([DoubleTime]),0)+Nz(([OtherIncentive]),0))*([Rate]))) AS Pension, ((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([Gratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+(Nz(([Overtime]),0)*((([GrossBasic])/208)*1.5))+(Nz(([DoubleTime]),0)*((([GrossBasic])/208)*2))+Nz(([OtherIncentive]),0))-Nz([Gratuity],0)) AS Taxable, IIf(DateDiff("yyyy",[DateofBirth],Date())>=55,0,(IIf(((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([FinGratuity]),0)+Nz(([Covid19Allowance]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+(Nz(([Overtime]),0)*((([GrossBasic])/208)*1.5))+(Nz(([DoubleTime]),0)*((([GrossBasic])/208)*2))+Nz(([OtherIncentive]),0))*([Rate]))>[CelingRate],[CelingRate],((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([FinGratuity]),0)+Nz(([Covid19Allowance]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+(Nz(([Overtime]),0)*((([GrossBasic])/208)*1.5))+(Nz(([DoubleTime]),0)*((([GrossBasic])/208)*2))+Nz(([OtherIncentive]),0))*([Rate]))))) AS HHPension, tblPayslip.Closures, [Gross] AS FFGross, tblCostCentre.CostID, tblCostCentre.CostName, tblOtherSalary.PersonalLevy, tblOtherSalary.UniDues, tblOtherSalary.NHI, tblOtherSalary.Absentism, (Nz(([Overtime]),0)*((([GrossBasic])/208)*1.5)) AS OTTIME, (Nz(([DoubleTime]),0)*((([GrossBasic])/208)*2)) AS DTTIME, tblEmployees.GratuityRate, tblOtherSalary.LeaveDays, tblOtherSalary.LeaveTaken, Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([Covid19Allowance]),0) AS GrossBasic, DateDiff("yyyy",[DateofBirth],Date()) AS NAPSAYEAR, tblOtherSalary.PriorGratuity, ([Gratuity]+[PriorGratuity]) AS FinGratuity, tblEmployees.NHIAccount, tblPayslip.Covid19Allowance, tblOtherSalary.Loans
FROM ((tblCostCentre INNER JOIN tblEmployees ON tblCostCentre.CostID = tblEmployees.CostID) INNER JOIN (tblNAPSA INNER JOIN (tblTaxation INNER JOIN tblPayslip ON tblTaxation.TaxID = tblPayslip.TaxID) ON tblNAPSA.NAPSAID = tblPayslip.NAPSAID) ON tblEmployees.EmpID = tblPayslip.EmpID) INNER JOIN tblOtherSalary ON tblPayslip.IDPAY = tblOtherSalary.IDPAY
WHERE (((tblEmployees.EmpID) Between [Forms]![frmpayslipsprint]![CboFirstName] And [Forms]![frmpayslipsprint]![CboLastName]) AND ((tblOtherSalary.TTDate) Between Format(([Forms]![frmpayslipsprint]![txtSalaryStartDate]),"yyyy\/mm\/dd") And Format(([Forms]![frmpayslipsprint]![txtSalaryEnddate]),"yyyy\/mm\/dd")) AND ((tblPayslip.Closures) Is Null) AND ((tblCostCentre.CostID) Between [Forms]![frmpayslipsprint]![CboFirstCostCentre] And [Forms]![frmpayslipsprint]![CboLastCostCentre])) OR (((tblPayslip.Closures)<>"2"))
ORDER BY tblEmployees.EmpID;


Or

SELECT tblEmployees.EmpID, tblEmployees.Fname, tblEmployees.Lname, tblEmployees.StaffNumber, tblEmployees.JobTitle, tblEmployees.SocialSecurity, tblEmployees.NRC, tblEmployees.PayMethod, tblEmployees.BankName, tblEmployees.AccountCode, tblEmployees.PaymentType, tblPayslip.BasicSalary, tblPayslip.HousingAllowance, tblPayslip.TransportAllowance, tblPayslip.LunchAllowance, tblOtherSalary.TTDate, tblOtherSalary.XMasBonus, tblOtherSalary.OtherBonuses, tblOtherSalary.Gratuity, tblOtherSalary.LeavePay, tblOtherSalary.OtherArears, tblOtherSalary.Overtime, tblOtherSalary.DoubleTime, tblOtherSalary.OtherIncentive, tblOtherSalary.StaffLoans, tblOtherSalary.OtherDeductions, tblOtherSalary.Advances, tblNAPSA.Rate, tblNAPSA.CelingRate, tblNAPSA.NAPSAFree, tblTaxation.FreeTax, tblTaxation.BandOne, tblTaxation.BandTwo, tblTaxation.TaxRateone, tblTaxation.TaxRateTwo, tblTaxation.TaxRateThree, tblTaxation.EducationLevy, Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([FinGratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+(Nz(([Overtime]),0)*((([GrossBasic])/208)*1.5))+(Nz(([DoubleTime]),0)*((([GrossBasic])/208)*2))+Nz(([OtherIncentive]),0)+Nz(([Covid19Allowance]),0) AS Gross, IIf(((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([Covid19Allowance]),0)+Nz(([OtherBonuses]),0)+Nz(([Gratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+Nz(([Overtime]),0)+Nz(([DoubleTime]),0)+Nz(([OtherIncentive]),0))*([Rate]))>[NAPSAFree],[NAPSAFree],((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([Covid19Allowance]),0)+Nz(([OtherBonuses]),0)+Nz(([Gratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+Nz(([Overtime]),0)+Nz(([DoubleTime]),0)+Nz(([OtherIncentive]),0))*([Rate]))) AS Pension, ((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([Gratuity]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+(Nz(([Overtime]),0)*((([GrossBasic])/208)*1.5))+(Nz(([DoubleTime]),0)*((([GrossBasic])/208)*2))+Nz(([OtherIncentive]),0))-Nz([Gratuity],0)) AS Taxable, IIf(DateDiff("yyyy",[DateofBirth],Date())>=55,0,(IIf(((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([FinGratuity]),0)+Nz(([Covid19Allowance]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+(Nz(([Overtime]),0)*((([GrossBasic])/208)*1.5))+(Nz(([DoubleTime]),0)*((([GrossBasic])/208)*2))+Nz(([OtherIncentive]),0))*([Rate]))>[CelingRate],[CelingRate],((Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([XMasBonus]),0)+Nz(([OtherBonuses]),0)+Nz(([FinGratuity]),0)+Nz(([Covid19Allowance]),0)+Nz(([LeavePay]),0)+Nz(([OtherArears]),0)+(Nz(([Overtime]),0)*((([GrossBasic])/208)*1.5))+(Nz(([DoubleTime]),0)*((([GrossBasic])/208)*2))+Nz(([OtherIncentive]),0))*([Rate]))))) AS HHPension, tblPayslip.Closures, [Gross] AS FFGross, tblCostCentre.CostID, tblCostCentre.CostName, tblOtherSalary.PersonalLevy, tblOtherSalary.UniDues, tblOtherSalary.NHI, tblOtherSalary.Absentism, (Nz(([Overtime]),0)*((([GrossBasic])/208)*1.5)) AS OTTIME, (Nz(([DoubleTime]),0)*((([GrossBasic])/208)*2)) AS DTTIME, tblEmployees.GratuityRate, tblOtherSalary.LeaveDays, tblOtherSalary.LeaveTaken, Nz(([BasicSalary]),0)+Nz(([HousingAllowance]),0)+Nz(([TransportAllowance]),0)+Nz(([LunchAllowance]),0)+Nz(([Covid19Allowance]),0) AS GrossBasic, DateDiff("yyyy",[DateofBirth],Date()) AS NAPSAYEAR, tblOtherSalary.PriorGratuity, ([Gratuity]+[PriorGratuity]) AS FinGratuity, tblEmployees.NHIAccount, tblPayslip.Covid19Allowance, tblOtherSalary.Loans
FROM ((tblCostCentre INNER JOIN tblEmployees ON tblCostCentre.CostID = tblEmployees.CostID) INNER JOIN (tblNAPSA INNER JOIN (tblTaxation INNER JOIN tblPayslip ON tblTaxation.TaxID = tblPayslip.TaxID) ON tblNAPSA.NAPSAID = tblPayslip.NAPSAID) ON tblEmployees.EmpID = tblPayslip.EmpID) INNER JOIN tblOtherSalary ON tblPayslip.IDPAY = tblOtherSalary.IDPAY
WHERE (((tblEmployees.EmpID) Between [Forms]![frmpayslipsprint]![CboFirstName] And [Forms]![frmpayslipsprint]![CboLastName]) AND ((tblOtherSalary.TTDate) Between Format(([Forms]![frmpayslipsprint]![txtSalaryStartDate]),"yyyy\/mm\/dd") And Format(([Forms]![frmpayslipsprint]![txtSalaryEnddate]),"yyyy\/mm\/dd")) AND ((tblPayslip.Closures) Is Null) AND ((tblCostCentre.CostID) Between [Forms]![frmpayslipsprint]![CboFirstCostCentre] And [Forms]![frmpayslipsprint]![CboLastCostCentre])) OR (((tblPayslip.Closures)<>"2"))
ORDER BY tblEmployees.EmpID;
 
Let's be clear here... Indexing will help somewhat. Restructuring to normalize the tables will help A LOT.

When you have a lot of things in separate but identically structured tables, you are multiplying work when trying to correlate table contents. Instead of having tables XBldg, YBldg, ZBldg, you need a Bldg table that contains all of what XBldg, YBldg, ZBldg contained, plus a FIELD in that single table that is X, Y, or Z. Then when you need things for each building, you can simply query "...WHERE BldgID = 'Z' ..." or "... GROUP BY BldgID... " and have a MUCH simpler query to process.

Having multiple tables brought together by JOIN clauses causes the amount of internal work (done by Access in preparing that query) to jump tremendously. We don't get to see the internals of Access because it is not OpenSource. However, during the merging that has to occur to fulfill that multi-layered JOIN, I would guess that each table contributes work based on combinatorial math rules, which means a factorial processing profile - and that is one of the worst profiles you can have. By reducing the number of tables (via normalization), you reduce the number of JOIN operations during that time that combinatorial math is significant. That should pay off MASSIVE dividens in speed improvement.
 
Further to Minty's post in which they suggested a simple model in which the many to many relationship is modelled by a third table which resolves the relationship into two one to many relationships, I would add one thing, viz that in each relationship referential integrity must be enforced. An unenforced relationship is as useful as a chocolate poker.

As others have stressed, normalization of the tables which make up the database is of paramount importance, and a sound knowledge of the principles of normalization is essential to anyone designing a relational database. I attach a little file which describes each Normal Form as simply as possible. But no more so! Concentrate on gaining a good understanding of the first three Normal Forms in the first instance. You can come back to the higher normal forms later. Don't worry too much about the formal definitions, but try to get a grasp of the principles underlying each. You'll then be in a far better position to design a robust and efficient database.

With a set of correctly normalized tables, what you are trying to achieve with your query will be very simple, and given appropriate indexing of the columns in the tables, should open very quickly.
 

Attachments

Mister-B said:
I am far from an Access expert and am just happy that the database runs.
Click to expand...
But it doesn't run. You have just identified the first of what will over time be many problems, all due to incorrect database design. Your organization may have to hire the necessary talent to fix that, after which you can move it forward again. There is no shame in that. Nobody is born with this knowledge. In my view it is an essential step to get your database operational.
 
nector said:
The truth here is to index your queries and ensure that every table involved has a primary key and properly indexed, what is delaying your query is the sorting part.
Click to expand...
Sorry, this is just wrong - indexes won't help with very badly stored data.
It's like putting a small band-aid on a broken leg.
 
Indexes WILL help. Just not very much given the combinatorial implications that lead to factorial performance profiles.
 
Agree with the previous comments. You need to restructure your tables using proper normalization techniques before you spend time trying to optimize this query.

Once you have done that, study the techniques explained in my article and related app / video:
www.isladogs.co.uk

Speed Comparison Tests 8

This article covers various methods of optimising queries and the use of the JET ShowPlan feature. It is the eighth in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your...
www.isladogs.co.uk www.isladogs.co.uk
 
@Mister-B, if you want help on restructuring this and moving the data this group can help. You may think that is too much work, but you will quickly find out a little work now will save lots of time later. For example this query would be a one liner with a properly designed db. Moving all the data into a new structure can be done via queries and would not require a manual effort of copying and pasting. Managing many tables with like fields is extremely painful and tedious compared to a couple of normalized tables. Do the work now and save 1000 times the work later.
 
MajP said:
Do the work now and save 1000 times the work later.
Click to expand...
Some of us has learned this the hard way. As my Lt. (US Navy) was fond of telling me: "TAKE time to do it right or MAKE time to do it over!"
 
NauticalGent said:
Some of us has learned this the hard way. As my Lt. (US Navy) was fond of telling me: "TAKE time to do it right or MAKE time to do it over!"
Click to expand...
In my government experience it was always the opposite. There never seemed to be time or money to do it right the first time, but there always seemed to be some miracle where we found the time and money to do it a second time.
 
That was one of the reasons the Navy liked my work. Because I took just a LITTLE BIT of extra time up front, my re-fit rate was a lot lower and so it appeared to them that I was more efficient than the other guys. Well, I was... but not for the reasons they thought. Besides, I wasn't about to correct their mistaken impressions. ;)
 
You must log in or register to reply here.

Similar threads

J
Solved Changed relationships has stopped a form working
2
Replies
22
Views
468
John O'Malvern
J
P
SQL Hangs
2
Replies
30
Views
1,905
GaP42
G
K
Solved Using dynamic sql instead of saved query in Insert Into queries...
Replies
17
Views
1,180
The_Doc_Man
The_Doc_Man
C
Query return on alias fields
Replies
8
Views
718
June7
June7
H
Solved Inner Join Table Query Not Run properly
Replies
11
Views
908
Pat Hartman
P

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Back
Top Bottom