Combine multiple tables with different date ranges

  • Thread starter Thread starter PamB
  • Start date Start date
P

PamB

Hi everyone,

I really need help please. I apologise in advance if my description is
difficult to follow. I am very new to Access and have the following problem:

Table 1 consists of:
Machine ID (from another table)
Downtime (calculated in queries from DateDiff expression based on Start Date
& Time and End Date & Time)
this table also has a few other fields - descriptive mainly

This table is designed to track the amount of downtime for each machine and
does so quite well, and even when there are multiple incidences of downtime
in a day. It is for Daily entries as required.

Table 2 consists of:
Machine ID (from the same table as table 1 gets it)
Start Date
Start Hours
End Date
End Hours
Planned Hours (for the above period)
Wet Weather Hours (for the above period)

I am able to enter Start, End, Planned and Wet Weather Hours on a weekly
basis and run appropriate queries / reports to give me 'Actual Hours worked'
based on Start and End Hours etc.

I would now like to perform some calculations such as:
(Planned Hours for period - Downtime (for same period)) / Planned Hours for
period

or something like

Actual Hours Worked / (Planned Hours - Downtime)

I would also like the above calculations to include 'wet weather' in the
'downtime' bit.

I have tried lots of different ways to link the tables together but nothing
so far has worked and I'm simply getting more confused and frustrated. The
best I have managed is to do an outer join query on each of the tables, then
put these two queries into a new query. This returns me the correct count of
results, but where there are multiple entries of 'downtime' from the first
table on the same day, the 'actual', 'planned' and 'wet' repeat across the
downtime records (or vice versa, depending on the join type).

Also, if no Start or End Hours for a machine have been entered in the 2nd
table, the Downtime entry/s don't show at all.

Is there an easier way to accomplish what I am attempting? Any help would be
greatly appreciated.

Thank you.
 
but where there are multiple entries of 'downtime' from the first table on
the same day,
But your description of Table1 did not include a DateTime field at all.
Does it have one? Why a table based on queries of downtime instead of just
using the query?
 
Thank you for your response.

I meant 'queries' not 'tables' in this bit:
My description of Table 1 wasn't very accurate sorry.

The actual fields in Table 1 (named "Entry") are:
ID: primary key
Shift: Lookup
Start Date: Date/Time
Time Down: Date/Time
End Date: Date/Time
Time Up: Date/Time
Company: Lookup
Machine ID: Lookup
SMU: Text
Tradesperson Name: Lookup
Job Description: Text
Follow up required: Text
End of Shift Status: Lookup
Component: Lookup
Code 1: Lookup
Code 2: Lookup

The entries in this table are to be made on a daily basis as required, so a
particular machine may 'break' several times a day, usually only for a few
hours or less, but sometimes over that midnight barrier from one day to the
next, and also sometimes for a period of longer than 24 hours. 'Downtime' is
calculated in queries/reports using the DateDiff expression on the above
Date/Time fields.

I can get lots of queries and reports to work from the above data entry.

Table 2 (named "Machine Hours") is as follows:
ID: primary key
Machine ID: Lookup (from the same table as table 1 gets it)
SMU Start Date: Date/Time
Start Hours: Number
SMU End Date: Date/Time
End Hours: Number
Planned Hours (for the above period covered by SMU Start & SMU End)
Wet Weather Hours (for the above period covered by SMU Start & SMU End)

This table is set up this way to enable entries to be made in the Start Date
& Start Hours and End Date & End Hours to calculate Actual Hours the machine
worked.
eg: Truck "X" started at 12300 hours on 1st March 2007 and finished at 12400
hours on 7th March 2007. We had 'Planned' to do 150 hours work in that time
and possibly also had 6 hours downtime due to a rain event.

So.... after that terribly long-winded explanation (are you still reading?!
:) ) I would like to combine the results from queries based on the "Entry"
table with the results from queries based on the "Machine Hours" table (so I
can perform the calculations mentioned in my original question), but because
the 'Machine Hours' aren't entered daily the resultant query has varying
results depending on the join type.

For example (I know there's double ups on Machine ID and Company Name but I
left these in to help me notice the problems!):
SELECT [Machine Hrs Qry].[SMU Start Date], [Machine Hrs Qry].[SMU Finish
Date], [Maint DT Qry].[Start Date], [Machine Hrs Qry].MachineID, [Maint DT
Qry].MachineID, [Machine Hrs Qry].CompanyName, [Maint DT Qry].CompanyName,
[Machine Hrs Qry].[Total Hours Planned], [Machine Hrs Qry].[Total Hours in
period], Sum([Maint DT Qry].Downtime) AS SumOfDowntime, [Machine Hrs
Qry].[Wet Weather Hours in Period]
FROM [Machine Hrs Qry] LEFT JOIN [Maint DT Qry] ON [Machine Hrs Qry].[SMU
Start Date] = [Maint DT Qry].[Start Date]
GROUP BY [Machine Hrs Qry].[SMU Start Date], [Machine Hrs Qry].[SMU Finish
Date], [Maint DT Qry].[Start Date], [Machine Hrs Qry].MachineID, [Maint DT
Qry].MachineID, [Machine Hrs Qry].CompanyName, [Maint DT Qry].CompanyName,
[Machine Hrs Qry].[Total Hours Planned], [Machine Hrs Qry].[Total Hours in
period], [Machine Hrs Qry].[Wet Weather Hours in Period];

does not include in the results any 'downtime' occurrence that didn't start
on a SMU Start Date.

If I enter SMU Hours for each day, it works fine. I have considered this but
would prefer to avoid it if at all possible - that's an awful lot of data
entry of very similar figures into the 'planned hours' and 'wet hours'
fields, and also 30 or 31 entries a month for each machine anyway.

Thank you so much for your time. I do appreciate it very much.
 
Back
Top