CODEWITHSUNDEEP
sqlms-access
tksy
tksy

Reputation: 3529

How to merge rows in MS access?

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?

Upvotes: 1

Views: 6299

Answers (4)

David-W-Fenton
David-W-Fenton

Reputation: 23067

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.

Upvotes: 0

Dave Barker
Dave Barker

Reputation: 6437

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;

Upvotes: 0

Andomar
Andomar

Reputation: 238058

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.

Upvotes: 1

Peter
Peter

Reputation: 999

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? ;-)

Upvotes: 0

Related Questions