Combining values from several records into one record

[Duck]

I am trying to develop a timeclock application, which contains a table
with fields for the employee name, StartTime, LunchOut, LunchIn, and
EndTime. The problems the table that contains these records only
contains for each record the user name and one of the other values.
So one record will contain a value for user and StartTime...next
record will contain values for user and LunchOut, etc.

How do I devise a query to get all the values for each day into the
same record, so that I can perform calculations to determine hours
worked, lunchtime hours, etc.??

 

[Armen Stein]

I am trying to develop a timeclock application, which contains a table
with fields for the employee name, StartTime, LunchOut, LunchIn, and
EndTime. The problems the table that contains these records only
contains for each record the user name and one of the other values.
So one record will contain a value for user and StartTime...next
record will contain values for user and LunchOut, etc.

How do I devise a query to get all the values for each day into the
same record, so that I can perform calculations to determine hours
worked, lunchtime hours, etc.??

First, I have to point out that your structure is quite rigid - what
if someone clocks out for another reason at another time during the
day? Or has a long shift and takes two lunches? But perhaps it's
okay for your purposes.

But to your problem. The main question is why you don't just put them
in to begin with? When someone clocks out for Lunch, can't you find
their record for today and update it, instead of adding a new record?

If you can't do that, then you could create "missing record" (see the
Wizard for help) queries to find all the matching employee records
that have a LunchOut time with those on the same date that don't. Then
you could update the missing LunchOut fields in the "main" record and
delete the original LunchOut records. This approach would involve
creating "queries of queries", and is pretty clunky - it would be
better to just update the correct records to begin with if you can.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com