CODEWITHSUNDEEP
sqlms-access
Hussein Sherif
Hussein Sherif

Reputation: 91

MS Access Query to get entry time and exit time for each ID

I need help to with an attendance table in access, in my company we have a single card reader which provides access in/out of the facility.

I was able to extract from the software an excel sheet which I later imported to access, the column headers(fields) are in a temporary table as follows.

The problem is staff go in & out of the facility a lot during the course of the day, I need to get the first and last entry time for each day per CardID to add to another table.

*AttendanceDate* - *AttendanceTime* - *CardID*
10/09/2017 - 08:00:00 - 123  
10/09/2017 - 09:20:00 - 123  
10/09/2017 - 10:00:00 - 123  
10/09/2017 - 15:00:00 - 123  
10/09/2017 - 09:00:00 - 675  
10/09/2017 - 12:00:00 - 675  
11/09/2017 - 09:00:00 - 675  
11/09/2017 - 16:00:00 - 675

I need to create a query that will get the for every cardID the first and last record of time per day and append it to another sheet to get the results like this.

*AttendanceDate* - *EntryTime* - *ExitTime* - *CardID*
10/09/2017 - 08:00:00 - 15:00:00 - 123  
10/09/2017 - 09:00:00 - 12:00:00 - 675  
11/09/2017 - 09:00:00 - 16:00:00 - 675

Is this even possible?

Upvotes: 0

Views: 132

Answers (3)

Parfait
Parfait

Reputation: 107697

Consider running two action queries back to back:

  1. Append query with aggregation and NOT IN / NOT EXISTS / LEFT JOIN...NULL clause (still compliant in MS Access though an SQL Server link) where only new CardIDs and AttendanceDate pairs are appended.
  2. Update query using domain aggregates, DMin and DMax (as aggregate query joins are not updateable queries) where existing CardIDs and AttendanceDate pairs are updated.

Append Query (appends only new CardIDs and AttendanceDate pairings, using NOT EXISTS)

INSERT INTO otherTable (CardID, AttendanceDate, EntryTime, ExitTime)
SELECT t.CardId, t.AttendanceDate, MIN(t.AttendanceTime), MAX(t.AttendanceTime)
FROM originTable t
WHERE NOT EXISTS
   (SELECT 1 FROM otherTable sub
    WHERE sub.CardID = t.CardID AND sub.AttendanceDate = t.AttendanceDate)
GROUP BY t.CardID, t.AttendanceDate;

Update Query (updates only existing CardIDs and AttendanceDate pairings, using domain aggregates)

UPDATE otherTable t1 INNER JOIN originTable t2 
    ON t1.CardID = t2.CardID AND t1.AttendanceDate = t2.AttendanceDate
SET t1.entrytime = DMin("AttendanceTime", "originTable", "CardID=" & t1.CardID & 
                        " AND AttendanceDate=#" & t1.AttendanceDate & "#"),
    t1.exittime = DMax("AttendanceTime", "originTable", "CardID=" & t1.CardID & 
                       " AND AttendanceDate=#" & t1.AttendanceDate & "#")

Upvotes: 1

Gustav
Gustav

Reputation: 55961

An "upsert" is certainly possible, if the tables have a unique key.

This old tip from Smart Access is one of my favourites:

Update and Append Records with One Query

By Alan Biggs

Did you know that you can use an update query in Access to both update and add records at the same time? This is useful if you have two versions of a table, tblOld and tblNew, and you want to integrate the changes from tblNew into tblOld.

Follow these steps:

Create an update query and add the two tables. Join the two tables by dragging the key field of tblNew onto the matching field of tblOld.

  1. Double-click on the relationship and choose the join option that includes all records from tblNew and only those that match from tblOld.

  2. Select all the fields from tblOld and drag them onto the QBE grid.

  3. For each field, in the Update To cell type in tblNew.FieldName, where FieldName matches the field name of tblOld.

  4. Select Query Properties from the View menu and change Unique Records to False. (This switches off the DISTINCTROW option in the SQL view. If you leave this on you'll get only one blank record in your results, but you want one blank record for each new record to be added to tblOld.)

  5. Run the query and you'll see the changes to tblNew are now in tblOld.

This will only add records to tblOld that have been added to tblNew. Records in tblOld that aren't present in tblNew will still remain in tblOld.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270633

This is an aggregation query:

select AttendanceDate, min(AttendanceTime) as entrytime,
       max(AttendanceTime) as exittime, CardId
from t
group by AttendanceDate, CardId;

To add this to another table, you would use either select into (to create the table) or insert (if the table is already created).

The insert would look like this:

insert into t2(AttendanceDate, entrytime, exittime, CardId)
    select AttendanceDate, min(AttendanceTime) as entrytime,
           max(AttendanceTime) as exittime, CardId
    from t
    group by AttendanceDate, CardId;

I don't believe MS Access supports "upsert", which would be a single operation to do both. So, create a unique index on the two columns:

create unique index unq_t_AttendanceDate_CardId on t(AttendanceDate, CardId);

Then attempt to do an insert. If that fails, then do an update. Here is a question on that topic.

Upvotes: 1

Related Questions