CODEWITHSUNDEEP
sqlms-access
csg
csg

Reputation: 121

MS Access SQL to replicate Excel vlookup on row data and column name

I have this working in Excel now, but due to the number of records, I need to move it out into Access.

Table 1:

| LookupValue        | Object Name | Attribute  | Data  |
|--------------------|-------------|------------|-------|
| Object1_Attribute1 | Object1     | Attribute1 | Data1 |
| Object1_Attribute2 | Object1     | Attribute2 | Data2 |
| Object2_Attribute1 | Object2     | Attribute1 | Data3 |
| Object2_Attribute2 | Object2     | Attribute2 | Data4 |

(in reality over 100 attributes)

Table 2 (pre-vlookup):

| Object Name | Attribute1 | Attribute2 |
|-------------|------------|------------|
| Object1     |            |            |
| Object2     |            |            |

Table 2 (post-vlookup):

| Object Name | Attribute1 | Attribute2 |
|-------------|------------|------------|
| Object1     | Data1      | Data2      |
| Object2     | Data3      | Data4      |

How do I replicate this in Access?

Upvotes: 0

Views: 201

Answers (2)

Parfait
Parfait

Reputation: 107587

Consider MS Access' crosstab query which can take up to 255 distinct values in Attribute column:

TRANSFORM MAX(Data) As MaxData
SELECT [Object Name]
FROM mytable
GROUP BY [Object Name]
PIVOT [Attribute]

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

I think you can do this with conditional aggregation:

select object_name,
       max(iif(Attribute = "Attribute1", Data, null)) as Attribute1,
       max(iif(Attribute = "Attribute2", Data, null)) as Attribute2,
       . . .
from t
group by object_name;

Upvotes: 0

Related Questions