Guidance on merging queries please? (1 Viewer)

[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

 

[Pat Hartman]

There are a couple of problems that need to be fixed first.
1. Trans_Date includes time but it doesn't seem to beeither the start or stop time
2. What is the Time field used for? It doesn't match the time in Trans_Date or either the Start or stop time. All is very confusing.

You will need to run update queries to get rid of the time part of trans_date and you will need to switch to using Date() rather than Now() to populate it.

More importantly though is what is the structure of the table/tables? The fact that you are using two queries indicates that the start/stop are in the same table. How are you determining which record is a start and which is a stop?

Is there ALWAYS a match between Start and Stop? So that there is NEVER a Start without a Stop or a Stop without a Start?

If there is always a match, a simple inner join between the two tables will work. However, if there are rows without matches, then you need to do a full outer join and that requires a union query with three queries:

Select a.*, b.* from a inner join b on a.Job_No = b.Job_Jo and a.Emp_ID = b.Emp_ID and a.Workcentre = b.Workcentre
Union Select a.*, b.* from a left join b on a.Job_No = b.Job_Jo and a.Emp_ID = b.Emp_ID and a.Workcentre = b.Workcentre where b.Emp_ID = Null
Union Select a.*, b.* from a Right join b on a.Job_No = b.Job_Jo and a.Emp_ID = b.Emp_ID and a.Workcentre = b.Workcentre Where a.Emp_ID = Null

Of course posting a db with data would make it ever so much easier for us to get the query right.

 

[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