Too many left joins, slow execution

[BABM]

I am trying to gather data in a query. There are too many left joins and it takes far too long to execute. I have attached a database file that shows what I am trying to achieve. I have 6 employees at the moment and that works fine but I need up to 20 and then it is too slow. I know my programming is clumsy. Can anyone suggest a better more efficient way for me to achieve my aims.

On the example database open frmAssyHours and with the first record selected click on the View employee Hours button. The query that has the problem is qryEmployeeHoursTotal.

 

[BABM]

Here's some more info about my problem.

I've attached an image of the table.

I am trying to first of all filter out and separate the employees on a job and each date that they are on. Then I am trying to put them together on one line.

I first of all list the Distinct employees for a job and put their names in textboxes on the form. Then I query for each employee in each textbox.

I'm looking for a better more efficient way to achieve this:


qryEmployeeHours1

SELECT tblLabour.*
FROM tblLabour
WHERE (((tblLabour.EmployeeKey)=[forms]![frmAssyHours]![Employee1]));


qryEmployeeHours2

SELECT tblLabour.*
FROM tblLabour
WHERE (((tblLabour.EmployeeKey)=[forms]![frmAssyHours]![Employee2]));


qryEmployeeHoursTotal

SELECT DISTINCT qryEmployeeHoursDate.WeekEnding, qryEmployeeHoursDate.WeekNo, qryEmployeeHoursDate.SISkey, qryEmployeeHours1.Employee, IIf(IsNull([qryEmployeeHours1].[Time]),0,[qryEmployeeHours1].[Time]) AS T1, IIf(IsNull([qryEmployeeHours1].[TimeHalf]),0,[qryEmployeeHours1].[TimeHalf]) AS TH1, qryEmployeeHours2.Employee, IIf(IsNull([qryEmployeeHours2].[Time]),0,[qryEmployeeHours2].[Time]) AS T2, IIf(IsNull([qryEmployeeHours2].[TimeHalf]),0,[qryEmployeeHours2].[TimeHalf]) AS TH2
FROM (qryEmployeeHoursDate LEFT JOIN qryEmployeeHours1 ON (qryEmployeeHoursDate.WeekEnding = qryEmployeeHours1.WeekEnding) AND (qryEmployeeHoursDate.SISkey = qryEmployeeHours1.SISkey)) LEFT JOIN qryEmployeeHours2 ON (qryEmployeeHoursDate.WeekEnding = qryEmployeeHours2.WeekEnding) AND (qryEmployeeHoursDate.SISkey = qryEmployeeHours2.SISkey);

 

[BABM]

Here's the reason I am doing it. The attached image shows the way I want to display the data.

 

[namliam]

The problem isnt in the left joins, its the fact that you are query-ing your table tblLabour 7 times without having any indexes on the TEXT FIELD Employee which you are using the query on.

You can .... probably do away with the 6 queries and do it all in a single query.
However what is it that you are trying to do, here you have now 6 employees working in a single week... what happens if you get 7,8,9,20 employees working on a day/week?
Designing queries like this is a nightmare waiting to happen IMNSHO!

Having employee as a text column is a bad idea to start with tbh.

 

[BABM]

I want to make it a maximum of 20 employees. I have a employee key field which is a number field. This query maybe is a nightmare, that is why I am asking for help on how to do it a better way. The original reason for doing it is to mimic an excel spreadsheet that is currently being used to display the data. I want to have the data in access instead and get rid of the excel.

 

[namliam]

But you can do it in one query, something like

SELECT 
  sum(iif(tblLabour.EmployeeKey=[forms]![frmAssyHours]![Employee1];[Time];0) AS T1
, sum(iif(tblLabour.EmployeeKey=[forms]![frmAssyHours]![Employee1];[TimeHalf];0) AS TH1
, sum(iif(tblLabour.EmployeeKey=[forms]![frmAssyHours]![Employee2];[Time];0) AS T2
, sum(iif(tblLabour.EmployeeKey=[forms]![frmAssyHours]![Employee2];[TimeHalf];0) AS TH2
... etc....

May be better done I think using some VBA code...
Which you feed normally the data and write the data into unbound text boxes as appropriate

FYI: Queries on NUMBER fields and INDEXED numbers are significantly faster on larger tables rather than using a text of indexed text field
If you have an ID field I suggest you want to use it

 

[BABM]

Thankyou for your help.
I tried:

SELECT tblLabour.Key, tblLabour.Job, tblLabour.WeekEnding, IIf([tblLabour].[EmployeeKey]=[forms]![frmAssyHours]![Employee1],[Time],0) AS T1, IIf([tblLabour].[EmployeeKey]=[forms]![frmAssyHours]![Employee1],[Time],0) AS TH1, IIf([tblLabour].[EmployeeKey]=[forms]![frmAssyHours]![Employee2],[Time],0) AS T2, IIf([tblLabour].[EmployeeKey]=[forms]![frmAssyHours]![Employee2],[Time],0) AS TH2
FROM tblLabour;

qryEmployeeHoursTotalShort2 Key Job WeekEnding T1 TH1 T2 TH2 T3 TH3 T4 TH4 271 1498 04/01/2015 2 2 0 0 0 0 0 0 272 1498 04/01/2015 0 0 0 0 3 3 0 0 273 1498 11/01/2015 3 3 0 0 0 0 0 0 274 1498 25/01/2015 3 3 0 0 0 0 0 0 276 1498 22/02/2015 0 0 3 3 0 0 0 0 278 1498 25/01/2015 0 0 0 0 40 40 0 0 279 1498 25/01/2015 0 0 0 0 0 0 10 10

I followed what you suggested but without the Sum because I want to have a line for each date not just the sum of all the dates together. However, the query above has duplicate outputs for dates. How can I total the hours for each employee according to date? I want to show one line for a date with the total hours worked by each employee on that job.

 

[BABM]

Sorry! This image show the results more clearly!

 

[namliam]

That is exactly what you need the SUM for, also remove the KEY field.
Group by Jobno and Weekending

Your employees after all are now columns not rows