CODEWITHSUNDEEP
ms-access
Matt Hall
Matt Hall

Reputation: 2412

Merging rows in to a single record

A database authored externally has some data I want to match up over 2 tables:

Table A:

Table B:

The only common field to join on is ID1, however, I really want all data matched on ID2 records.

Joining the tables on ID1 gives me the following result:

| ID1 | ID2 | Type |   Fld1    |   Fld2    |   Fld3    |
--------------------------------------------------------
|  1  |  1  |  1   |   Data1   |   Data1   |           |
|  2  |  1  |  2   |           |           |   Data1   |
|  3  |  2  |  1   |   Data1   |   Data1   |           |
|  4  |  2  |  2   |           |           |   Data1   |

What I need is a query that results in:

| ID1 | ID2 | Type |   Fld1    |   Fld2    |   Fld3    |
--------------------------------------------------------
|  ?  |  1  |  ?   |   Data1   |   Data1   |   Data1   |
|  ?  |  2  |  ?   |   Data1   |   Data1   |   Data1   |

I've put "?" for ID1 and Type fields as I'm not really sure what result these would merge down to (ultimately I'm not fussed about these 2 fields, I really just want ID2 and the 3 "Fld~" fields).

What method is there for achieving this?

Additional notes after accepted answer:

As Remou recommended, using the aggregate function MAX on the text fields and grouping by the ID field I wanted to use worked. However, I didn't mention in my original illustration of the issue that I was also filtering on a couple of fields as well.

Even if these fields are not selected for the query, they need to have their "total" function set to "Where" in the query's design view to ensure they become part of the SQL's WHERE clause. By default design view will set them to "Group By", which put them in to the HAVING clause (not what I wanted).

Totals in Design View

This is the full SQL I arrived at that gave me what I needed:

SELECT A.Learner_Id, 
       Max(B.AddLine1) AS Address1, 
       Max(B.AddLine2) AS Address2, 
       Max(B.AddLine3) AS Address3, 
       Max(B.AddLine4) AS Address4, 
       Max(A.PostCode) AS AddPostCode
FROM LearnerContact As A LEFT JOIN PostAdd As B ON A.LearnerContact_Id = B.LearnerContact_Id
WHERE (((A.LocType)=1 Or (A.LocType)=2) AND ((A.ContType)=2))
GROUP BY A.Learner_Id;

Upvotes: 2

Views: 160

Answers (1)

Fionnuala
Fionnuala

Reputation: 91316

Max might suit:

SELECT t.ID2, 
       Max(t.Fld1) AS MaxOfFld1, 
       Max(t.Fld2) AS MaxOfFld2, 
       Max(x.Fld3) AS MaxOfFld3
FROM aTable t 
INNER JOIN bTable x
ON t.ID1 = x.ID1
GROUP BY t.ID2;

Upvotes: 1

Related Questions