Guidance on merging queries please?

[Jonny45wakey]

Hi

I'm wondering if someone could help me out with the following please?

I have 2 x queries, qryOpStartBooked and qryOpStopBooked (see images below)

What i would like to do is generate a query structured as follows:-

Transaction ID / Job_No / Emp_ID / Trans_Date / Time / Workcentre / OpStart / OpStop / Time between Opstart & Opstop in hours

I would like this ordered by Job_No so for each job (using SCHED22 as example) the result would be:-

My current query attempting the above looks like this:-

Thanks

Jonny

 

[ebs17]

I have 2 x queries, qryOpStartBooked and qryOpStopBooked

You could do something with the SQL statements as code (text) from the SQL view.
With images you would need excellent image processing and very high skills.

 

[arnelgp]

SELECT
    qryOpStartBooked.TrainsactionID,
    qryOpStartBooked.Job_No,
    qryOpStartBooked.Workcentre,
    qryOpStartBooked.Emp_ID,
    EmployeesTable.Name,
    qryOpStartBooked.OpStart,
    qryOpStopBooked.OpStop,
    DateDiff("n",[OpStart],[opStop])/60 AS ActHrs
FROM
    (qryOpStartBooked LEFT JOIN
        qryOpStopBooked ON (qryOpStartBooked.Emp_ID = qryOpStopBooked.Emp_ID) AND
        (qryOpStartBooked.Job_No = qryOpStopBooked.Job_No))
    INNER JOIN EmployeesTable ON qryOpStartBooked.Emp_ID = EmployeesTable.Employee_ID;

 

[plog]

I don't know if "merging" the queries is the correct method. Just writing one better one might be the answer.

Can you post the SQL of your existing queries?

 

[Jonny45wakey]

SELECT
    qryOpStartBooked.TrainsactionID,
    qryOpStartBooked.Job_No,
    qryOpStartBooked.Workcentre,
    qryOpStartBooked.Emp_ID,
    EmployeesTable.Name,
    qryOpStartBooked.OpStart,
    qryOpStopBooked.OpStop,
    DateDiff("n",[OpStart],[opStop])/60 AS ActHrs
FROM
    (qryOpStartBooked LEFT JOIN
        qryOpStopBooked ON (qryOpStartBooked.Emp_ID = qryOpStopBooked.Emp_ID) AND
        (qryOpStartBooked.Job_No = qryOpStopBooked.Job_No))
    INNER JOIN EmployeesTable ON qryOpStartBooked.Emp_ID = EmployeesTable.Employee_ID;

Thanks Arnelgp

I nearly got there but it's not quite right, hoping you can point me in the right direction please?

I have the below data in a query when capturing the OpStart Date and time for the MTL workcentre

I have the same data for the OpStop date and time for the MTL workcentre

What i would like is one line which captures total time between MTL Workcentre OpStart & OpStop in Hours in the ActualHrs field.

Currently its showing like below, the total time should be around 24 Hours?

Many Thanks

Jonny

 

[arnelgp]

Currently its showing like below, the total time should be around 24 Hours?

can you upload a db with only those records you showed on post#5.
upload the "real" table name.

 

[Jonny45wakey]

No problem sir, DB uploaded.

The two queries in question are qryOpStartBooked and qryOpStoppedBooked.

As you know i would like a query which shows 1 line for Job No 79019 for the guillotine workcentre start and stop time and actual Hrs between start and stop (ActHrs: DateDiff("n",[OpStart],[OpStop])/60)

And the same for all other bookings such as the PRESS Workcentre.

I hope this makes sense, appreciate your continued guidance and support

Thanks

Jonny

 

[arnelgp]

see this demo based on the data you showed on post#8.

 

[CJ_London]

given your queries are just effectively listing all the records from separate tables, you might as well have one query linking the two tables much as Arnel has provided.

Problem is potentially your example data is not extensive enough - what if a job goes to the guillotine or press more than once? Whether you link tables or queries, which 'stop' record should you link to? Presumably the one which is later and nearest the start time?

Then you have the potential issue of two jobs being on the same machine at the same time - job A starts at 9am and stops at 10am whilst job B is recorded as starting at 8am and finishing at 9:30am. An overlap.

Since the only difference between the two tables is the start/stop time fields, it would be more efficient to include both fields in one table.

 

[Jonny45wakey]

see this demo based on the data you showed on post#8.

Thanks Arnelgp

Unfortunately i cant open the query as it says event is blocked by disabled mode?

see this demo based on the data you showed on post#8.

Perfect Arnelgp, works like a charm, many thanks for your support, much appreciated

Jonny