How to merge rows in MS access?

Question

I am having a table with records like this in MS Access:

ID  field2  field3  field4 field5
1   345       asr
2          ase   567  788
3   456       ghy
4          jki   568  899
5   235       yui
6          hju   456  456

I want to merge it to get a table like this:

ID      field2 field3 field4 field5
1           345 asrase    567   788
3           456 ghyjki    568   899
5           235 yuihju    456   456

Is this possible with queries?

Answer 1

I think you probably can. Judging from your example rows, you want each "even" and "odd" row to merge together in a well-defined way.

With this kind of problem, if you don't know the way, I'd start using the query editor to play around. For example, can you create a query that calculates a pseudo-id of 1/2 th actual ID? I think you can, and that will let you get a common ID to relate the 2 fields with.

But there are other ways to relate the two fields. You could merge the table with itself, choosing the first version as those with a non-NULL field 4 (or 5), and the second where the ID is the same as the ID of the first -1.

If you use the 1/2 ID ID method I proposed first, the easy thing to do is create 1 query to get the "even" and another to get the "odd" rows. Then you simply merge the two queries in a third one.

Once you have a solution, you can muck around with it, and try other things.

I'd start by playing in the query editor, and then look at the SQL when you're done.

PS: I'm sure I could code your answer for you, but what fun would you have then? ;-)

Answer 2

Assuming the rows continue like the example, this query would do:

select a.id, 
    a.field2, 
    a.field3 + Iif(IsNull(b.field3),'',b.field3), 
    b.field4, 
    b.field5
from table1 a
left join table1 b on b.id = a.id + 1
where a.field2 is not null

It joins the table on itself, looking for the next row. It combines both rows to produce the desired result.

Answer 3

I went with a slightly different approach to Andomar and used the mod function to return even rows.

SELECT a.id, a.field2, a.field3 + IIF(b.field3 IS NULL,'', b.field3), 
       b.field4, b.field5
  FROM Table1 AS a
       INNER JOIN Table1 AS b 
          ON a.ID = b.ID + 1
 WHERE Table1_1.ID MOD 2 = 0;

Answer 4

I don't think any of the answers here are robust, given that it depends on the ID fields being consecutive, and that even if it is consecutive, that it is the right value in the next consecutive record.

I would like to know more about the data in Field3, i.e., whether or not it is meaningful.