Combining 2 or more records become 1 record

[kft10 via AccessMonster.com]

Hi All,

Can I combine 2 or more records become 1 records using Access query?

Here is my data:
Client ID Info-1 Info-2 Info-3
AAAA active - -
AAAA - 2000 -
AAAA - - NY
BBBB inactive 3200 NJ

I'd like to combine the blank information to all clients who has the same
client-ID. So the result will be:

Client ID info-1 info-2 info-3
AAAA active 2000 NY
BBBB inactive 3200 NJ

Thank you in advance.
KF

 

[Michel Walsh]

SELECT ClientID, MAX(info1), MAX(info2), MAX(info3)
FROM tableName
GROUP BY ClientID


may do the job assuming the Maximum value can be taken in cases there is
more than one not null value under infoX for a given ClientID



Vanderghast, Access MVP

 

[John W. Vinson]

Hi All,

Can I combine 2 or more records become 1 records using Access query?

Here is my data:
Client ID Info-1 Info-2 Info-3
AAAA active - -
AAAA - 2000 -
AAAA - - NY
BBBB inactive 3200 NJ

I'd like to combine the blank information to all clients who has the same
client-ID. So the result will be:

Client ID info-1 info-2 info-3
AAAA active 2000 NY
BBBB inactive 3200 NJ

Thank you in advance.
KF

Ummm... and what would you want to do with data like

CCCC active 2500 -
CCCC indolent 2000 CA
CCCC inactive 2250 NY
DDDD active - -
DDDD - 3000 -
DDDD - 3200 -
DDDD - - PA


??

Some sort of Self Join query will be needed, but a lot depends on the nature
of the data and what you want joined with what!

 

[kft10 via AccessMonster.com]

If the data like that, just leave as it is. Only the blank data will be
combined from the other data. Is it possible Access query can do that? Thanks.

Hi All,

[quoted text clipped - 16 lines]

Thank you in advance.
KF

Ummm... and what would you want to do with data like

CCCC active 2500 -
CCCC indolent 2000 CA
CCCC inactive 2250 NY
DDDD active - -
DDDD - 3000 -
DDDD - 3200 -
DDDD - - PA

??

Some sort of Self Join query will be needed, but a lot depends on the nature
of the data and what you want joined with what!

 

[John W. Vinson]

If the data like that, just leave as it is. Only the blank data will be
combined from the other data. Is it possible Access query can do that? Thanks.

It's still ambiguous. If there are two DIFFERENT values in two different
records, which one should be chosen? Or will that - reliably - never happen?

I guess I'd be inclined to use a two-table solution. You have:

Client ID Info-1 Info-2 Info-3
AAAA active - -
AAAA - 2000 -
AAAA - - NY
BBBB inactive 3200 NJ


Create a new table with the same structure (copy and paste the table, design
mode only); make Client ID the Primary Key. Run an Append query appending the
existing table into it - you'll get a whole lot of records "Not appended due
to key violations", but it will put one (doesn't matter which) of the records
for each ClientID into the new table.

Then run three update queries:

UPDATE newtable
SET [Info-1] =
DLookUp("[Info-1]", "oldtable", "[Client ID] = '" & [Client ID] & "' AND
[Info-1] IS NOT NULL") WHERE [Info-1] IS NULL;

and similarly for Info-2 and Info-3. This will pick an arbitrary one of the
non-null values for the field, so if there are duplicates data will be lost.

I'm using DLookUp because a join query will (I suspect) not be updateable,
since Access won't be able to find a unique value.

 

[Michel Walsh]

Using Max should work if you have one and only one not null value, per
field, per ClientID. It does not?


Vanderghast, Access MVP

If the data like that, just leave as it is. Only the blank data will be
combined from the other data. Is it possible Access query can do that?
Thanks.

Hi All,

[quoted text clipped - 16 lines]

Thank you in advance.
KF

Ummm... and what would you want to do with data like

CCCC active 2500 -
CCCC indolent 2000 CA
CCCC inactive 2250 NY
DDDD active - -
DDDD - 3000 -
DDDD - 3200 -
DDDD - - PA

??

Some sort of Self Join query will be needed, but a lot depends on the
nature
of the data and what you want joined with what!

 

[kft10 via AccessMonster.com]

It works, what I want. Thank you very much....

Rgds,
KF10

Using Max should work if you have one and only one not null value, per
field, per ClientID. It does not?

Vanderghast, Access MVP

If the data like that, just leave as it is. Only the blank data will be
combined from the other data. Is it possible Access query can do that?

[quoted text clipped - 21 lines]