Creating new table in VBA

[pascal]

Hi,

I have a table with the following fields :

ID, Name, WorkDate, CheckTime

ID is a unique number for each employee.
CheckTime contains the hour someone comes in or goes out the office.
So, each employee has at least two records (mostly four) a day in the table.

Example of the table :

1 Frank Devon 01/01/2005 08:00
1 Frank Devon 01/01/2005 12:00
1 Frank Devon 01/01/2005 12:30
1 Frank Devon 01/01/2005 16:00
2 Paul Hogan 01/01/2005 08:15
2 Paul Hogan 01/01/2005 12:00
3 Kim Daltrey 02/01/2005 08:10
3 Kim Daltrey 02/01/2005 12:00
3 Kim Daltrey 02/01/2005 12:30
3 Kim Daltrey 02/01/2005 16:00

Now, I need to make a table or query from these records which contains all the checktimes in one row per person of the query or table, for example again :

1 Frank Devon 01/01/2005 08:00 12:00 12:30 16:00
2 Paul Hogan 01/01/2005 08:15 12:00
3 Kim Daltrey 02/01/2005 08:10 12:00 12:30 16:00

Anyone who can help me out with this one?

The first (original) table was imported from an external database program.

Thanks already.

Pascal

 

[Jon K]

If the table is small, you can do it with a series of two queries.

qryOne:-
SELECT [ID], [Name], [WorkDate], [CheckTime],
"CheckTime " & DCount("*","TableName","[ID]=" & [ID] & " and [WorkDate]=DateValue('" & [WorkDate] & "') and [CheckTime]<=#" & [CheckTime] & "#") AS ColumnHeading
FROM [TableName];

qryTwo:-
TRANSFORM First([CheckTime])
SELECT [ID], [Name], [WorkDate]
FROM qryOne
GROUP BY [ID], [Name], [WorkDate]
PIVOT [ColumnHeading];

Run the second query.


Using DCount() in this way is inefficient. If the table is large, you may consider using VBA code to update a temporary table.


Note
If ID is a text field, its value in the DCount() needs to be surrounded with single-quotes i.e.

"CheckTime " & DCount("*","TableName","[ID]='" & [ID] & "' and [WorkDate]=DateValue('" & [WorkDate] & "') and [CheckTime]<=#" & [CheckTime] & "#") AS ColumnHeading


The DateValue() function in the DCount() makes the expression work correctly on systems using either dd/mm/yyyy or mm/dd/yyyy date format.

Name is a reserved word in Access. Better avoid using it as a field name.
.

 

[pascal]

Unbelievable, worked from the first time.

I can't thank you enough Jon K.

Thanks a lot.