I want to calculate work hours in each day using MS Access.I have query for same thing in MySQL but this thing is not working in MS Access using pdo php.

query in MySql

    SELECT MachineNo, Empcardno, Date, SEC_TO_TIME(SUM(tsecs)) AS total_time
  SELECT CASE WHEN MachineNo = @mn AND Empcardno = @en AND `Date` = @dt
              THEN @rn := @rn + 1
              ELSE @rn := 1
         END AS rn,
         @mn := MachineNo AS MachineNo, @en := Empcardno AS Empcardno, 
         @dt := `Date` AS `Date`,
         CASE WHEN @rn % 2 = 1 THEN -TIME_TO_SEC(`Time`)
              ELSE TIME_TO_SEC(`Time`)
         END AS tsecs
  FROM timesheet
  CROSS JOIN (SELECT @mn := 0, @en := 0, @dt := '', @rn := 0) init
  ORDER BY Date, Time
) t
GROUP BY MachineNo, Empcardno, Date

how to convert this query to make compatible with ms access pdo connection using php

table structure and data in ms-access


  • This query cannot be adopted to MS Access. Build the query for it from the beginning.
    – Akina
    Commented Jun 10, 2020 at 7:25
  • I am familiar with MySQL but i don't have much knowledge about ms access can you help me to build query for ms access Commented Jun 10, 2020 at 7:28
  • I am familiar with MySQL but i don't have much knowledge about ms access Perform the same task in MySQL without user-defined variables - all another constructions have direct equivalent in MS Access (subqueries) or may be easily adopted (functions).
    – Akina
    Commented Jun 10, 2020 at 7:35

1 Answer 1

    SELECT MachineNo, Empcardno, [Date], 
       FORMAT(SUM(IIF(rn MOD 2 = 0, -TimeValue([Time]), TimeValue([Time]))),'hh:nn:ss') AS total_time
  SELECT t1.MachineNo, t1.Empcardno, t1.Date, t1.Time, COUNT(t2.MachineNo) AS rn
  FROM timesheet t1
  LEFT JOIN timesheet t2 ON t2.MachineNo = t1.MachineNo 
                        AND t2.Empcardno = t1.Empcardno
                        AND t2.Date = t1.Date
                        AND TimeValue(t2.Time) < TimeValue(t1.Time)
  GROUP BY t1.MachineNo, t1.Empcardno, t1.Date, t1.Time
)  AS t
GROUP BY MachineNo, Empcardno, Date

for complete detail please see the link https://stackoverflow.com/questions/62297919/how-to-convert-query-related-to-calculate-work-hours-in-mysql-to-ms-access/62317847#comment110225804_62317847

Not the answer you're looking for? Browse other questions tagged or ask your own question.