## Can this be streamlined/made to run faster? (Acc 97 sr2 on 95b)

Explanation first - Question Last

On my database for an engineering shop, I have a table called Original that the the hours for each job by dept are entered into.

The daily hours are imported from a timeclock system, added and sorted using qryLabours_Crosstab to give hours used by job.

qryGraphTimesAll subtracts time used from time allocated for each active job by dept:

SELECT IIf([original].[fitt]-nz([qryLabours_Crosstab].[fitt])>0,[original].[fitt]-nz([qryLabours_Crosstab].[fitt]),0) AS TotalFitt, IIf([original].[FAB]-nz([qryLabours_Crosstab].[Fab])>0,[original].[FAB]-nz([qryLabours_Crosstab].[Fab]),0) AS Totalfab, IIf([original].[cylgr]-NZ([qryLabours_Crosstab].[cyli])>0,[original].[cylgr]-nz([qryLabours_Crosstab].[cyli]),0) AS TotalCyl, IIf([original].[gen]-nz([qryLabours_Crosstab].[gear])>0,[original].[gen]-nz([qryLabours_Crosstab].[gear]),0) AS TotalGear, IIf([original].[mill]-nz([qryLabours_Crosstab].[mill])>0,[original].[mill]-nz([qryLabours_Crosstab].[mill]),0) AS TotalMill, IIf([original].[profgr]-nz([qryLabours_Crosstab].[grind])>0,[original].[profgr]-nz([qryLabours_Crosstab].[grind]),0) AS TotalPGrind, IIf([original].[slot]-nz([qryLabours_Crosstab].[slot])>0,[original].[slot]-nz([qryLabours_Crosstab].[slot]),0) AS TotalSlot, IIf([original].[turn]-nz([qryLabours_Crosstab].[turn])>0,[original].[turn]-nz([qryLabours_Crosstab].[turn]),0) AS TotalTurn
FROM Original LEFT JOIN qryLabours_Crosstab ON Original.[Job #] = qryLabours_Crosstab.[Job #]
WHERE (((Original.Finished)=False))
WITH OWNERACCESS OPTION;

qryPercentageDoneUp updates HoursTable as follows to get a single value for each dept:

UPDATE HoursTable SET HoursTable.Fab = DSum("[Totalfab]","qryGraphTimesAll"), HoursTable.Turn = DSum("[TotalTurn]","qryGraphTimesAll"), HoursTable.Mill = DSum("[TotalMill]","qryGraphTimesAll"), HoursTable.Gear = DSum("[TotalGear]","qryGraphTimesAll"), HoursTable.Profile = DSum("[TotalPGrind]","qryGraphTimesAll"), HoursTable.Grind = DSum("[TotalCyl]","qryGraphTimesAll"), HoursTable.Slot = DSum("[TotalSlot]","qryGraphTimesAll"), HoursTable.Fitt = DSum("[TotalFitt]","qryGraphTimesAll"), HoursTable.JobPercent = DSum("[pdone]","Original","[Finished]=false")/DCount("[pdone]","Original","[Finished]=false")
WITH OWNERACCESS OPTION;

The main screen has a bar graph on it created using coloured labels that get their width value from HoursTable and is updated every 2 minutes via a timer.

The query that updates the table fires when a new job is added or job is closed. On the machines we have, this creates for the users an annoying downtime while the values are recalculated.

Is there a way to streamline the process or run the query asynchronasly?