Geek asks and answers

Collapse MS Access Table

I have a table similar to the Example source table below, which I would like to collapse depending on the ID field (see Example collapsed table). I can do this with code, but it inflates my Access database with a maximum size of 2 GB, so I hope there is a way to do this with a query. I should probably point out that for any given ID value, I don’t need to worry about more than one entry that has a value in the One, Two, Three, or Four field.

Example source table:

ID  One Two  Three  Four
1   My       Is
1                   Matt
1       Name
2   My       Is     Matt
2       Name
3   My  Name Is     Matt

An example of a collapsible table:

ID  One Two   Three  Four
1   My  Name    Is   Matt
2   My  Name    Is   Matt
3   My  Name    Is   Matt

Thanks in advance for any help or support.

Matt

+3
source share
1 answer

, Max() .

SELECT
    ID,
    Max(One),
    Max(Two),
    Max(Three),
    Max(Four)
FROM tblSource
GROUP BY ID;

, "make table". , "append query".

2 , Compact Repair, . , db () . .

+3

More articles:


All Articles