Merging data from 2 rows into one

[harrywow]

New to access, don't know how to read or write in VBA, can use query design view to create queries..
I have data on 2 rows and would like a certain field on the row above.
Date Time
Row 1 01/10/2008 05:00
Row 2 01/10/2008 06:00

I need the 06:00am field to be on row 1

Any help would be great
Cheers
Harry

[NeoPa]

I assume you have more than two records in your data, otherwise you could do it manually.

However, you don't explain under what circumstances the code should recognise which values to add to which records. Without that, the question really has no defined meaning.

[harrywow]

Thanks for the reply.
There are hundreds of records and the query results look like this.(App 1)
The records are sorted by USRID and Time (Ascending), so for every Event_Type there should be an I and O (In and Out Time), where the Event_Type is = to "O", the Group_ID is not populated.
For every USERID there should be an "I" and"O" in a timestamped order.

App1.

Date   UserID  Time      Event_Type  Group_ID  DEVICE
01/10  0804    04:08:31    I           22       10.11
01/10  0804    05:32:47    O                    10.11
01/10  3035    04:37:48    I           22       10.12
01/10  3035    05:37:48    O                    10.12

I would like the data to look like this:

Date  UserID    Time      Time2   Group_ID  DEVICE 
01/10  0804   04:08:31  05:32:47     22      10.11
01/10  3035   04:37:48  05:37:48     22      10.12

Hope this helps
Cheers
Harry

[Stewart Ross]

Hi Harry. You can use an Access crosstab query to accomplish this. Example SQL for this (which you can paste into the SQL view of the Access Query Editor) is as follows:

TRANSFORM Max(App1.Time) AS T
  SELECT      App1.Date, App1.UserID, App1.Device
  FROM        App1
  GROUP BY    App1.Date, App1.UserID, App1.Device
  ORDER BY    App1.Date, App1.UserID
PIVOT    IIf(Nz([Event_Type],"O")="I","Time In","Time Out");

Test Data

Date       UserID  Time    Event_Type Device
01/10/2008  804    04:08:31     I      10.11
01/10/2008  804    05:32:47     O      10.11
02/10/2008  804    11:40:00     I      10.11
02/10/2008  804    12:10:00     O      10.11
03/10/2008  804    09:45:00     I      10.11
01/10/2008 3035    04:37:48     I      10.12
01/10/2008 3035    05:37:48     O      10.12
02/10/2008 3035    14:00:00     I      10.12
02/10/2008 3035    14:20:00     O      10.12

Results

Date       UserID  Device  Time In   Time Out
01/10/2008  804    10.11   04:08:31  05:32:47
01/10/2008 3035    10.12   04:37:48  05:37:48
02/10/2008  804    10.11   11:40:00  12:10:00
02/10/2008 3035    10.12   14:00:00  14:20:00
03/10/2008  804    10.11   09:45:00

The Max function is just used to provide an aggregate value for the pivoted element; Min or Sum would have worked just as well.

Crosstab queries always place the pivoted values on the right of the result columns. If you need custom ordering of columns, or of rows for that matter, you can always take the results into a separate query and reorder them there.

-Stewart

ps the Nz function is used to make sure that you don't end up with three pivoted columns - an in column, an out column, and a null column (by default always headed "<>" by Access), which would arise if there is an in time but not yet an out time for that userID and device. Example of this shown in row 6 of test data and results.

[harrywow]

Thank you very much, that helped heaps.
One more question, in my original data it shows an "I" and "O" entry for users under the EVENT_TYPE column.

Where there is an "O" entry in the EVENT_TYPE column, the "GROUP_ID" cloumn is always null.
Is there a way to populate this field with the value above (if USRID's match).

Date Time USRID EVENT_TYPE GROUP_ID DEVICE
1/10/2008 18:13:52 5064 I 12 10.11
1/10/2008 18:13:56 5064 O 10.11
1/10/2008 18:14:03 5064 I 22 10.11
1/10/2008 18:59:35 5064 O 10.11

In the above example I would populate row 2 and 4 with 12 and 22 respectively.
Thanks for all your help
Harry

[Stewart Ross]

Hi. SQL has no concept of record position as such. Individual rows cannot 'see' or access previous rows.

You would either need to join the table to itself using the date, user ID and device ID fields, or (much simpler) use DLOOKUP to retrieve the most recent in-time row - but DLOOKUP is likely to be very slow in operation. Self-joining the table can also dramatically affect performance, particularly where crosstabs are also involved.

Whether by joining or by DLOOKUP there will also be a need to have the time element included in the criteria to retrieve the most recent in-time row for that user and device ID, and this is likely to prove quite challenging.

It would be far, far simpler for you if instead of the Group ID being null it was provided as part of the out-time row itself. Is this possible?

-Stewart

[NeoPa]

One more question, in my original data it shows an "I" and "O" entry for users under the EVENT_TYPE column.

Where there is an "O" entry in the EVENT_TYPE column, the "GROUP_ID" cloumn is always null.
Is there a way to populate this field with the value above (if USRID's match).

Try using Max([GROUP_ID]) in your SQL.

As Null values are excluded from this function, the only value to Max() from should be the one you're after.

[harrywow]

Thanks Gents.
Stewart it is not possible to get the data on the same line. If I could that would be great (but unfortunately its not possible).
Tried the DLookup, took ages.
Anyway thanks for you help
H

[NeoPa]

What data can't be got on the same line Harry?

I can't see why there should be a barrier :S

Why don't you explain what you've got now and we can look at it for you and see if we can't find an appropriate resolution.

[Stewart Ross]

Actually, NeoPa pointed the right direction in his earlier post. It turns out that as Max ignores nulls it can be used in the crosstab query itself, without DLookups and self-joins. The GroupID can be found very easily as a result.

The modified crosstab is just

TRANSFORM Max(App1.Time) AS T
  SELECT   App1.Date, 
           App1.UserID, 
           Max(App1.GroupID) AS [Group], 
           App1.Device
  FROM     App1
  GROUP BY App1.Date,
           App1.UserID, 
           App1.Device
PIVOT IIf(Nz([Event_Type],"O")="I","Time In","Time Out");

Test Data:

Date       UserID   Time   Event_Type GroupID Device
01/10/2008   804  04:08:31      I     22      10.11
01/10/2008   804  05:32:47      O             10.11
01/10/2008   804  07:10:00      I     25      10.11
01/10/2008   804  08:10:00      O             10.11
01/10/2008  3035  04:37:48      I     23      10.12
01/10/2008  3035  05:37:48      O             10.12
02/10/2008   804  11:40:00      I     22      10.11
02/10/2008   804  12:10:00      O             10.11
02/10/2008  3035  14:00:00      I     24      10.12
02/10/2008  3035  14:20:00      O             10.12
03/10/2008   804  09:45:00      I     23      10.11

Results:

Date       UserID Group Device Time In  Time Out
01/10/2008  804    25   10.11  07:10:00 08:10:00
01/10/2008 3035    23   10.12  04:37:48 05:37:48
02/10/2008  804    22   10.11  11:40:00 12:10:00
02/10/2008 3035    24   10.12  14:00:00 14:20:00
03/10/2008  804    23   10.11  09:45:00 
 

-Stewart

[ADezii]

harrywow, both NeoPa and Stewart are more proficient in SQL than I am, but I honestly do not believe that there is an SQL based solution that will produce the single-line results that you so desire. I have a code based solution in the form of a single Function but it relies on strict data conformance. I'll post the sample data along with the results, and if you are still interested let me know. You would not have to know VBA code, just a simple call to the Function will do the trick.

Date    UserID    Time    Event_Type   Group_ID    DEVICE
01/10    0804    04:08:31    I             22      10.11
01/10    0804    05:32:47    O                     10.11
02/14    2345    10:27:13    I             22      10.54
02/14    2345    12:40:21    O                     10.54
01/10    3035    04:37:48    I             22      10.12
01/10    3035    05:37:48    O                     10.12
07/23    9999    19:37:20    I             22      23.12
07/23    9999    19:54:02    O                     23.12

OUTPUT:

Date   UserID  Time       Time2   Group_ID DEVICE
01/10 | 0804 | 04:08:31 | 05:32:47 | 22 |  10.11
01/10 | 3035 | 04:37:48 | 05:37:48 | 22 |  10.12
02/14 | 2345 | 10:27:13 | 12:40:21 | 22 |  10.54
07/23 | 9999 | 19:37:20 | 19:54:02 | 22 |  23.12
 

[NeoPa]

...but I honestly do not believe that there is an SQL based solution that will produce the single-line results that you so desire.

Did you catch Stewart's post #10 ADezii?

I'm not positive a Cross-tab is specifically required, but it is an example of a GROUP BY query that does exactly what's required (as far as I can tell).

[Stewart Ross]

Upon further review of the test data the crosstab query approach has a flaw, which is that the use of the dummy function for the value is resulting in loss of rows (for example, the first two lines of my test data are not there in the result set). This is a result of the aggregation of the Max function (on the time field), but there would be data loss whichever aggregate function was deployed.

I will rethink the SQL to see if there is indeed a solution which does not lose data, but ADezii's function based approach is a good one too.

-Stewart

ps this just points up how careful one has to be in designing and interpreting test cases. A realistic set of data is essential if a robust solution is to be found - which in turn means being very clear in posting what is and is not an allowed combination of data. The crosstab as listed will work successfully on the data as posted in post # 2. However, it does not fully work where there is more than one row per day for that device and userid.

[Stewart Ross]

OK, here's an SQL solution which uses a self-join approach instead of the crosstab. Please note that it does not return IN-only rows (ones where there is no OUT as yet),

SELECT
       A.Date,   
       A.UserID, 
       A.Device, 
       A.GroupID, 
       A.Time AS [Time In], 
       Min(B.Time) AS [Time Out]
FROM 
       App1 AS A 
LEFT JOIN 
       App1 AS B ON 
            (A.Device = B.Device) 
       AND  (A.UserID = B.UserID) 
       AND  (A.Date = B.Date)
WHERE     (((A.Event_Type)="I") 
       AND ((A.Time)<[b].[Time]))
GROUP BY 
       A.Date, 
       A.UserID, 
       A.Device, 
       A.GroupID, 
       A.Time
ORDER BY 
       A.Date, 
       A.UserID, 
       A.Device, 
       A.Time;

Results

Date      UserID Device GroupID Time In  Time Out
01/10/2008  804  10.11    22   04:08:31  05:32:47
01/10/2008  804  10.11    25   07:10:00  08:10:00
01/10/2008 3035  10.12    23   04:37:48  05:37:48
02/10/2008  804  10.11    22   11:40:00  12:10:00
02/10/2008 3035  10.12    24   14:00:00  14:20:00
 

-Stewart

[ADezii]

Did you catch Stewart's post #10 ADezii?

I'm not positive a Cross-tab is specifically required, but it is an example of a GROUP BY query that does exactly what's required (as far as I can tell).

Sorry Stewart, NeoPa, the old eyes aren't what they used to be! (LOL).

[NeoPa]

No worries ADezii. Happens all the time to me :D

A slightly different version of Stewart's SQL (horribly plagiarised I'm afraid to save me some work).

SELECT A.Date,
       A.UserID,
       A.Device,
       A.GroupID,
       A.Time AS [Time In],
       B.Time AS [Time Out]
 
FROM   App1 AS A LEFT JOIN
       App1 AS B
  ON  (A.Device=B.Device)
 AND  (A.UserID=B.UserID)
 AND  (A.Date=B.Date)
 
WHERE (((A.Event_Type='I')
  AND   (B.Event_Type='O'))
   OR  ((A.Event_Type='I')
  AND   (B.Event_Type Is Null)))
 
GROUP BY A.Date,
         A.UserID,
         A.Device,
         A.GroupID,
         A.Time
 
ORDER BY A.Date,
         A.UserID,
         A.Device,
         A.Time

I've changed the WHERE clause somewhat to resolve outstanding issues. Let us know if this does all you want.