By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

Merging data from 2 rows into one

P: 4
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
Sep 30 '08 #1
Share this Question
Share on Google+
15 Replies


NeoPa
Expert Mod 15k+
P: 31,656
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.
Sep 30 '08 #2

P: 4
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.
Expand|Select|Wrap|Line Numbers
  1. Date   UserID  Time      Event_Type  Group_ID  DEVICE
  2. 01/10  0804    04:08:31    I           22       10.11
  3. 01/10  0804    05:32:47    O                    10.11
  4. 01/10  3035    04:37:48    I           22       10.12
  5. 01/10  3035    05:37:48    O                    10.12
I would like the data to look like this:
Expand|Select|Wrap|Line Numbers
  1. Date  UserID    Time      Time2   Group_ID  DEVICE 
  2. 01/10  0804   04:08:31  05:32:47     22      10.11
  3. 01/10  3035   04:37:48  05:37:48     22      10.12
Hope this helps
Cheers
Harry
Oct 1 '08 #3

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Max(App1.Time) AS T
  2.   SELECT      App1.Date, App1.UserID, App1.Device
  3.   FROM        App1
  4.   GROUP BY    App1.Date, App1.UserID, App1.Device
  5.   ORDER BY    App1.Date, App1.UserID
  6. PIVOT    IIf(Nz([Event_Type],"O")="I","Time In","Time Out");
Test Data
Expand|Select|Wrap|Line Numbers
  1. Date       UserID  Time    Event_Type Device
  2. 01/10/2008  804    04:08:31     I      10.11
  3. 01/10/2008  804    05:32:47     O      10.11
  4. 02/10/2008  804    11:40:00     I      10.11
  5. 02/10/2008  804    12:10:00     O      10.11
  6. 03/10/2008  804    09:45:00     I      10.11
  7. 01/10/2008 3035    04:37:48     I      10.12
  8. 01/10/2008 3035    05:37:48     O      10.12
  9. 02/10/2008 3035    14:00:00     I      10.12
  10. 02/10/2008 3035    14:20:00     O      10.12
Results
Expand|Select|Wrap|Line Numbers
  1. Date       UserID  Device  Time In   Time Out
  2. 01/10/2008  804    10.11   04:08:31  05:32:47
  3. 01/10/2008 3035    10.12   04:37:48  05:37:48
  4. 02/10/2008  804    10.11   11:40:00  12:10:00
  5. 02/10/2008 3035    10.12   14:00:00  14:20:00
  6. 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.
Oct 1 '08 #4

P: 4
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
Oct 2 '08 #5

Expert Mod 2.5K+
P: 2,545
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
Oct 2 '08 #6

NeoPa
Expert Mod 15k+
P: 31,656
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.
Oct 2 '08 #7

P: 4
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
Oct 6 '08 #8

NeoPa
Expert Mod 15k+
P: 31,656
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.
Oct 6 '08 #9

Expert Mod 2.5K+
P: 2,545
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
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Max(App1.Time) AS T
  2.   SELECT   App1.Date, 
  3.            App1.UserID, 
  4.            Max(App1.GroupID) AS [Group], 
  5.            App1.Device
  6.   FROM     App1
  7.   GROUP BY App1.Date,
  8.            App1.UserID, 
  9.            App1.Device
  10. PIVOT IIf(Nz([Event_Type],"O")="I","Time In","Time Out");
Test Data:
Expand|Select|Wrap|Line Numbers
  1. Date       UserID   Time   Event_Type GroupID Device
  2. 01/10/2008   804  04:08:31      I     22      10.11
  3. 01/10/2008   804  05:32:47      O             10.11
  4. 01/10/2008   804  07:10:00      I     25      10.11
  5. 01/10/2008   804  08:10:00      O             10.11
  6. 01/10/2008  3035  04:37:48      I     23      10.12
  7. 01/10/2008  3035  05:37:48      O             10.12
  8. 02/10/2008   804  11:40:00      I     22      10.11
  9. 02/10/2008   804  12:10:00      O             10.11
  10. 02/10/2008  3035  14:00:00      I     24      10.12
  11. 02/10/2008  3035  14:20:00      O             10.12
  12. 03/10/2008   804  09:45:00      I     23      10.11
Results:
Expand|Select|Wrap|Line Numbers
  1. Date       UserID Group Device Time In  Time Out
  2. 01/10/2008  804    25   10.11  07:10:00 08:10:00
  3. 01/10/2008 3035    23   10.12  04:37:48 05:37:48
  4. 02/10/2008  804    22   10.11  11:40:00 12:10:00
  5. 02/10/2008 3035    24   10.12  14:00:00 14:20:00
  6. 03/10/2008  804    23   10.11  09:45:00 
  7.  
-Stewart
Oct 6 '08 #10

ADezii
Expert 5K+
P: 8,669
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.
Expand|Select|Wrap|Line Numbers
  1. Date    UserID    Time    Event_Type   Group_ID    DEVICE
  2. 01/10    0804    04:08:31    I             22      10.11
  3. 01/10    0804    05:32:47    O                     10.11
  4. 02/14    2345    10:27:13    I             22      10.54
  5. 02/14    2345    12:40:21    O                     10.54
  6. 01/10    3035    04:37:48    I             22      10.12
  7. 01/10    3035    05:37:48    O                     10.12
  8. 07/23    9999    19:37:20    I             22      23.12
  9. 07/23    9999    19:54:02    O                     23.12
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Date   UserID  Time       Time2   Group_ID DEVICE
  2. 01/10 | 0804 | 04:08:31 | 05:32:47 | 22 |  10.11
  3. 01/10 | 3035 | 04:37:48 | 05:37:48 | 22 |  10.12
  4. 02/14 | 2345 | 10:27:13 | 12:40:21 | 22 |  10.54
  5. 07/23 | 9999 | 19:37:20 | 19:54:02 | 22 |  23.12
  6.  
Oct 6 '08 #11

NeoPa
Expert Mod 15k+
P: 31,656
...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).
Oct 6 '08 #12

Expert Mod 2.5K+
P: 2,545
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.
Oct 6 '08 #13

Expert Mod 2.5K+
P: 2,545
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),

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.        A.Date,   
  3.        A.UserID, 
  4.        A.Device, 
  5.        A.GroupID, 
  6.        A.Time AS [Time In], 
  7.        Min(B.Time) AS [Time Out]
  8. FROM 
  9.        App1 AS A 
  10. LEFT JOIN 
  11.        App1 AS B ON 
  12.             (A.Device = B.Device) 
  13.        AND  (A.UserID = B.UserID) 
  14.        AND  (A.Date = B.Date)
  15. WHERE     (((A.Event_Type)="I") 
  16.        AND ((A.Time)<[b].[Time]))
  17. GROUP BY 
  18.        A.Date, 
  19.        A.UserID, 
  20.        A.Device, 
  21.        A.GroupID, 
  22.        A.Time
  23. ORDER BY 
  24.        A.Date, 
  25.        A.UserID, 
  26.        A.Device, 
  27.        A.Time;
Results
Expand|Select|Wrap|Line Numbers
  1. Date      UserID Device GroupID Time In  Time Out
  2. 01/10/2008  804  10.11    22   04:08:31  05:32:47
  3. 01/10/2008  804  10.11    25   07:10:00  08:10:00
  4. 01/10/2008 3035  10.12    23   04:37:48  05:37:48
  5. 02/10/2008  804  10.11    22   11:40:00  12:10:00
  6. 02/10/2008 3035  10.12    24   14:00:00  14:20:00
  7.  
-Stewart
Oct 6 '08 #14

ADezii
Expert 5K+
P: 8,669
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).
Oct 6 '08 #15

NeoPa
Expert Mod 15k+
P: 31,656
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).
Expand|Select|Wrap|Line Numbers
  1. SELECT A.Date,
  2.        A.UserID,
  3.        A.Device,
  4.        A.GroupID,
  5.        A.Time AS [Time In],
  6.        B.Time AS [Time Out]
  7.  
  8. FROM   App1 AS A LEFT JOIN
  9.        App1 AS B
  10.   ON  (A.Device=B.Device)
  11.  AND  (A.UserID=B.UserID)
  12.  AND  (A.Date=B.Date)
  13.  
  14. WHERE (((A.Event_Type='I')
  15.   AND   (B.Event_Type='O'))
  16.    OR  ((A.Event_Type='I')
  17.   AND   (B.Event_Type Is Null)))
  18.  
  19. GROUP BY A.Date,
  20.          A.UserID,
  21.          A.Device,
  22.          A.GroupID,
  23.          A.Time
  24.  
  25. ORDER BY A.Date,
  26.          A.UserID,
  27.          A.Device,
  28.          A.Time
I've changed the WHERE clause somewhat to resolve outstanding issues. Let us know if this does all you want.
Oct 7 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.