Convert Row to Columns

[Victor Torres]

Hi, I'm working on a payroll program for a doctor. My payroll program works
great with my time ticket program. Now, they have a small timecard program
that is a little bit confusing. Their database is in Access and I can access
the data but when I look into the tables the times are in rows (Just one
column for employee and one for the time/date) instead of "IN","OUT TO
LUNCH","IN FROM LUNCH",OUT. How can I change this so I can see the data in a
more clear way?? somebuddy give me a formula in a query but I have to do one
for every time and when I get to the "OUT" it crash. How can I make this
simple so I can use it with my payroll program? Thanks to anyone that can
help me with this problem.

 

[Allen Browne]

Victor, you will need to come to learn to handle subqueries to handle this.

See:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

BTW, IN is a reserved word, and OUT is a future-reserved word. You might
want to rename your fields to something else such as DateTimeIn and
DateTimeOut. For a list of words to avoid when designing tables, refer to:
http://allenbrowne.com/AppIssueBadWord.html

 

[Victor Torres]

First Allen is an honor to be talking to you. I consider you as one of the
gods of Access. Now, I don't know a lot about subqueries in access but I'm
trying. I create a subquery to manage this but I have 4 queries with sub
query to create a full Time table (DateTimeIn, DateTimeOutLunch,
DateTimeInLunch, DateTimeOut). is there anyway to simplify this?? thanks a
lot!!!

 

[Allen Browne]

The idea is to create a query to select the check-out values only.
Then use a subquery to get the maximum prior check-in value.
You can then use DateDiff() to calcuate the number of minutes worked, and
divide by 60.

That should make good sense of the data you are being asked to query. It
would be possible to split it up for lunchtime columns with more subqueries,
but I think that would be more problems than it is worth. It would fail on
lots of scenarios, e.g.:
- Someone who did not take a lunch break (including part timers who only
worked 4 hours)
- Shift workers whose meal break is not when you expect (e.g. 2am after
starting at 10pm the night before)

 

[Victor Torres]

Thanks Allen, but my client use the other timecard program to fix any
anomaly that have in shift missing. Anyway I will take any your advise into
using subqueries.

Thanks...

 

[Gary Walter]

Hi, I'm working on a payroll program for a doctor. My payroll program
works
great with my time ticket program. Now, they have a small timecard
program
that is a little bit confusing. Their database is in Access and I can
access
the data but when I look into the tables the times are in rows (Just one
column for employee and one for the time/date) instead of "IN","OUT TO
LUNCH","IN FROM LUNCH",OUT. How can I change this so I can see the data
in a
more clear way?? somebuddy give me a formula in a query but I have to do
one
for every time and when I get to the "OUT" it crash. How can I make this
simple so I can use it with my payroll program? Thanks to anyone that can
help me with this problem.

Hi Victor,

In addition to Allen's sage help...

If you are saying you always have 4 rows
for an employee on a certain date, I might
think about bringing the time-span data you
want into a local table that has a third number
field "Rank."

Then run an update query to determine rank.

UPDATE tblLocal
SET Rank =
DCount("*","tblLocal", "[Emp] = '" & [Emp] & "' AND
DateValue([datefield]) = #" & DateValue([datefield]) & "#
AND [datefield] <= #" & [datefield] & "#" )

result of query on simple example data:

Emp datefield Rank
A 3/15/2008 8:00:00 AM 1
A 3/15/2008 12:00:00 PM 2
A 3/15/2008 1:00:00 PM 3
A 3/15/2008 5:00:00 PM 4
A 3/16/2008 8:00:00 AM 1
A 3/16/2008 12:00:00 PM 2
A 3/16/2008 1:00:00 PM 3
A 3/16/2008 5:00:00 PM 4

that might be one way...

good luck,

gary

 

[Gary Walter]

Hi, I'm working on a payroll program for a doctor. My payroll program
works
great with my time ticket program. Now, they have a small timecard
program
that is a little bit confusing. Their database is in Access and I can
access
the data but when I look into the tables the times are in rows (Just one
column for employee and one for the time/date) instead of "IN","OUT TO
LUNCH","IN FROM LUNCH",OUT. How can I change this so I can see the data
in a
more clear way?? somebuddy give me a formula in a query but I have to do
one
for every time and when I get to the "OUT" it crash. How can I make this
simple so I can use it with my payroll program? Thanks to anyone that
can
help me with this problem.

Hi Victor,

In addition to Allen's sage help...

If you are saying you always have 4 rows
for an employee on a certain date, I might
think about bringing the time-span data you
want into a local table that has a third number
field "Rank."

Then run an update query to determine rank.

UPDATE tblLocal
SET Rank =
DCount("*","tblLocal", "[Emp] = '" & [Emp] & "' AND
DateValue([datefield]) = #" & DateValue([datefield]) & "#
AND [datefield] <= #" & [datefield] & "#" )

result of query on simple example data:

Emp datefield Rank
A 3/15/2008 8:00:00 AM 1
A 3/15/2008 12:00:00 PM 2
A 3/15/2008 1:00:00 PM 3
A 3/15/2008 5:00:00 PM 4
A 3/16/2008 8:00:00 AM 1
A 3/16/2008 12:00:00 PM 2
A 3/16/2008 1:00:00 PM 3
A 3/16/2008 5:00:00 PM 4

that might be one way...

Depending on granularity of your business rules,
here might be end results query assuming your
app wishes to keep track of time in minutes:

SELECT
tblLocal.Emp,
DateValue([datefield]) AS DateOnly,
Max(Switch([Rank]=1,[datefield],True,Null)) AS AM_IN,
Max(Switch([Rank]=2,[datefield],True,Null)) AS AM_OUT,
Max(Switch([Rank]=3,[datefield],True,Null)) AS PM_IN,
Max(Switch([Rank]=4,[datefield],True,Null)) AS PM_OUT,
DateDiff("n",[AM_IN],[AM_OUT]) AS AM_Minutes,
DateDiff("n",[PM_IN],[PM_OUT]) AS PM_Minutes,
[AM_Minutes]+[PM_Minutes] AS TotMinutes
FROM tblLocal
GROUP BY
tblLocal.Emp,
DateValue([datefield]);

I usually use DateDiff unit "one smaller" than what I
care about, so maybe you might want to use "s."
Either way you can then always "compute up."

SELECT
tblLocal.Emp,
DateValue([datefield]) AS DateOnly,
Max(Switch([Rank]=1,[datefield],True,Null)) AS AM_IN,
Max(Switch([Rank]=2,[datefield],True,Null)) AS AM_OUT,
Max(Switch([Rank]=3,[datefield],True,Null)) AS PM_IN,
Max(Switch([Rank]=4,[datefield],True,Null)) AS PM_OUT,
DateDiff("s",[AM_IN],[AM_OUT]) AS AM_Secs,
DateDiff("s",[PM_IN],[PM_OUT]) AS PM_Secs,
[AM_Secs]+[PM_Secs] AS TotSecs
FROM tblLocal
GROUP BY
tblLocal.Emp,
DateValue([datefield]);