merge/flatten records

[Minder]

Hello,
I have a report with meta tags about several objects. Each instance appears as a separate record. I tried to cross tab this (see screen cap below). I'd like to merge (flatten) these records so each object has one record with meta tags in columns.

Many thanks



M. (novice)

Attachment 14035

[oemar00]

Would you show the SQL that produce this.
It looks like the table contains one column value for each ID, if this becoming one record which ID will be displayed?
Should you fix the table first so that each column will be on the same row?

[Minder]

Hi Oemar,
the first column was added by Access when I imported the table. The ID itself is not important, and data loss here is acceptable to combine records. However, the order of the items is important.
Thanks

Here's the code:
TRANSFORM First(Master.[Values]) AS FirstOfValues
SELECT Master.[ID], Master.[ObjectID], Master.[Description], Master.[Values]
FROM Master
GROUP BY Master.[ID], Master.[ObjectID], Master.[Description], Master.[Values]
PIVOT Master.[Metatag];

[oemar00]

Hi Oemar,
the first column was added by Access when I imported the table. The ID itself is not important, and data loss here is acceptable to combine records. However, the order of the items is important.
Thanks

Here's the code:
TRANSFORM First(Master.[Values]) AS FirstOfValues
SELECT Master.[ID], Master.[ObjectID], Master.[Description], Master.[Values]
FROM Master
GROUP BY Master.[ID], Master.[ObjectID], Master.[Description], Master.[Values]
PIVOT Master.[Metatag];

To see how the table looks like could you use:

SELECT Master.[ID],
            Master.[ObjectID],
            Master.[Description],
            Master.[Values]
FROM Master
ORDER BY 1;

I think the table is the one that need fixing, the ID data type is Auto number that is why Access keep adding one to it, I see the grouping should be by ObjectID, if that is the case then you need to change the ORDER BY 2 where the sequence will be by ObjectID not ID.

I think your table results from importing one column at a time, try by importing for all columns at one time which will fix the table as well.