Need to update Start data based on End data

[DaveMac]

I need a query that runs and updates a beginning reading from an ending reading.

What I have.

Tbl-

Date, Shift, Machine_Nbr, Start_Counter, End_Counter

Since shift and date would be unique I need to say make shift 3 start equal shift 2 end and so on. The problem is I cannot guarantee that they will be entered in order so any logic that requires shift reports are entered in order would die. I think I need something like a shift sequence number that is based on the date serial number and the shift ( not sure how) and then look for empty start_counters when one is found determine the date and shift serial number and look for the date and shift serial number of the needed end_counter. I am not opposed to VBA just not my norm.


Thanks for any help!

 

[rac2]

So Diligent Gal enters Date, Shift, and End_Counter for Shift No. 2. Then Lazy Guy enters Date, Shift, and End_Counter for Shift No. 1.

Then Report Person (you) comes along and fills in the Start_Counter for both shifts.

Is this the scenario?

 

[DaveMac]

That is about the sum of it. I did have a form for entry that had an on update in the end field that made the default value for the start field the same. Two problems with that were if the miss a shift and then I also had to move to an .asp page for data entry. SO here I am typing in numbers!!!!!

 

[rac2]

I hear you. If you could devise an Update Query to fillin the missing values, that would be a step ahead.

Here are some ideas that may help.

Using SQL to get the value needed for Shift 2.

SELECT End_Counter
FROM MachineUsage
WHERE Date = #Oct 13, 2006#
  AND Machine_Nbr = 7
  AND Shift = 1

An update query then would be

UPDATE MachineUsage SET
  Start_Counter = (
        SELECT End_Counter
        FROM MachineUsage
        WHERE Date = #Oct 13, 2006#
          AND Machine_Nbr = 7
          AND Shift = 1 )

WHERE Date = #Oct 13, 2006#
  AND Machine_Nbr = 7
  AND Shift = 2

Good for one machine on one date and one shift, but we want it all.

UPDATE MachineUsage SET
  a.Start_Counter = b.End_Counter

FROM MachineUsage a
JOIN MachineUsage b ON b.Date = a.Date
                   AND b.Machine_Nbr = a.Machine_Nbr
                   and b.Shift = 1
WHERE a.Shift = 2

This uses a self-join that matches up the two rows which have the data we need, the Start_counter
from shift 1 and the End_Counter from shift 2.


That can be extended to cover all three shifts by forming two more self-joins.

UPDATE MachineUsage SET
  a.Start_Counter = b.End_Counter,
  b.Start_Counter = c.End_Counter,
  c.Start_Counter = a.End_Counter

FROM MachineUsage a
JOIN MachineUsage b ON b.Date = a.Date
                   AND b.Machine_Nbr = a.Machine_Nbr
                   and b.Shift = 1
JOIN MachineUsage c ON c.Date = a.Date
                   AND c.Machine_Nbr = a.Machine_Nbr
                   and c.Shift = 3
WHERE a.Shift = 2

To check this, and to clarify things take a look at the results of this query.

SELECT m.Machine_Nbr, 
       m.Date,
       m.Shift,
       m.Start_Counter, m.End_Counter,
       a.Start_Counter, a.End_Counter,
       b.Start_Counter, b.End_Counter,
       c.Start_Counter, c.End_Counter

FROM MachineUsage m
JOIN MachineUsage a ON a.Date = m.Date
                   AND a.Machine_Nbr = m.Machine_Nbr
                   and a.Shift = 1
JOIN MachineUsage b ON b.Date = m.Date
                   AND b.Machine_Nbr = m.Machine_Nbr
                   and b.Shift = 2
JOIN MachineUsage c ON c.Date = m.Date
                   AND c.Machine_Nbr = m.Machine_Nbr
                   and c.Shift = 3
ORDER BY m.Date, m.Machine_Nbr, m.Shift

What this does. For every Date, Machine, and Shift show the start and end couunters
for all shifts as well as for that shift.

SQL must have all of the data it needs in the same row in order to use the values for
the update.

I am confident that last query will work in Access. The UPDATE query, I would be very cautious.
The syntax may not be correct. And more importantly, UPDATEs cannot be reversed. You should make
a copy of the table for testing before you run the UPDATE queries on the real data.