Merge Duplicate Values with a Unique ID

[afullerton]

Hi,




I have a query with over 85,000 lines of data where each row has a unique id but the unique id appears multiple times within the table. For example, there is a unique numeric id tide to various types of pets across four columns. So the id number 1 could appear in four different rows each with different pets. Please see the first attachment "Original Table"


I want to be able to create a table that has one for for each unique id with all of the corresponding pet data. Please see the attached "Desired Table".Attachment 300829


I am not very access savvy so if you have a SQL suggestion, if you could be very specific with your instructions it would be very much appreciated!!

Thanks!

[pbaldy]

If the data supports it, start a new query and add all 5 fields to the grid. Click on the Totals icon on the ribbon (looks like an E). Change group by to max on the 4 pet fields.

[afullerton]

If the data supports it, start a new query and add all 5 fields to the grid. Click on the Totals icon on the ribbon (looks like an E). Change group by to max on the 4 pet fields.

Hi,


Thanks for your reply but it didn't quite work. It doesn't combine the rows so that there is only one row for a unique id, it just seems to sort the "pet" rows in a different way.

Any other suggestions??

Thanks!!!

[pbaldy]

What is the SQL of this query?

[June7]

Should be like:

SELECT ID_Number, Max(Pet1) AS P1, Max(Pet2) AS P2, Max(Pet3) AS P3, Max(Pet4) As P4 FROM tablename GROUP BY ID_Number;

This is not a normalized structure. Are you attempting to normalize? A normalized structure would be:

tblPets

ID	OwnerID	Pet
1	1	dog
2	1	fish
3	1	bird
4	1	cat
5	2	dog
6	2	fish
7	2	cat
8	3	dog
9	3	fish
10	4	dog
11	4	cat

A UNION query could accomplish that structure.

[afullerton]

What is the SQL of this query?

Hi,

Thanks for your response! The SQL of the query is

SELECT [Raw Pet Data].ID_Number, IIf([Raw Pet Data]![Pet] Like "Pet1_dog","dog",IIf([Raw Pet Data]![Pet] Like "Pet1_cat","cat",IIf([Raw Pet Data]![Pet] Like "Pet1_fish","fish",IIf([Raw Pet Data]![Pet] Like "Pet1_bird","bird","")))) AS Pet1, IIf([Raw Pet Data]![Pet] Like "Pet2_dog","dog",IIf([Raw Pet Data]![Pet] Like "Pet2_cat","cat",IIf([Raw Pet Data]![Pet] Like "Pet2_fish","fish",IIf([Raw Pet Data]![Pet] Like "Pet2_bird","bird","")))) AS Pet2, IIf([Raw Pet Data]![Pet] Like "Pet3_dog","dog",IIf([Raw Pet Data]![Pet] Like "Pet3_cat","cat",IIf([Raw Pet Data]![Pet] Like "Pet3_fish","fish",IIf([Raw Pet Data]![Pet] Like "Pet3_bird","bird","")))) AS Pet3, IIf([Raw Pet Data]![Pet] Like "Pet4_dog","dog",IIf([Raw Pet Data]![Pet] Like "Pet4_cat","cat",IIf([Raw Pet Data]![Pet] Like "Pet4_fish","fish",IIf([Raw Pet Data]![Pet] Like "Pet4_bird","bird","")))) AS Pet4
FROM [Raw Pet Data];


It is pulling from the Raw Pet Data table that looks like the attached picture called Raw Pet Data. When I run the query it looks like the picture Pet Query and I want it to look like the Desired Table Picture.

Any suggestions?


Really appreciate the help! I am very desperate at this point!

[pbaldy]

Well, you didn't try what I suggested. I'll stay out of June7's way.

[June7]

Right, since the raw data is a normalized structure and you have a query that de-normalizes, build another query with that first query as source. Use the query syntax that Paul suggested.

An alternative is a CROSSTAB query. But to do that the raw data Pet field needs to be split. It really should be two fields.

[afullerton]

Well, you didn't try what I suggested. I'll stay out of June7's way.

Hi pbaldy,

I realized that I made an error when I tried your first suggestion "If the data supports it, start a new query and add all 5 fields to the grid. Click on the Totals icon on the ribbon (looks like an E). Change group by to max on the 4 pet fields." I tried it again and it works - thanks so much for the help!!! However now I would like to do it for a similar table but instead of the contents being Pet1_dog they are date values, like 2/14/14. Do you have a suggestion of what to do with date values? I tried to use the Max suggestion but it didn't work. Let me know!



Thanks for your suggestion June7, I appreciate the help but this solution ended up working.

[afullerton]

I spoke to soon, I just realized that if an ID doesn't have a Pet name associated with it, the GROUP BY function still puts in some value and I want it to just be blank. I am sorry for all of the questions, I just started working with Access this week and have a very limited knowledge. Just as a reminder, I have a table that looks like "Pet Raw Data", I can write a query to get it into the form "Pet Query" and I want to get it to look like "Desired Table". If you can help me do this I would be so appreciative, I have a deliverable for work and I can't seem to find an answer. THANK YOU!

[June7]

If an ID doesn't have a Pet name associated then the record should be blank or no record at all for the ID. Where would 'some value' come from?

Your options are still what has been suggested.

CROSSTAB the raw data

or aggregate the denormalized query

[afullerton]

Hi,

Thanks for your response and sorry I wasn't clearer about the issues I am having in my previous reply.

When I run it on my small Pet Data database, I don't have any issues and using the GROUP BY Max works well. However, when I try to use the same method on my query that has a large amount of raw data it doesn't seem to work.

In an attempt to make this more clear and easier for you all to help me (thank you so much!) I changed around the data I want to manipulate for confidentiality reasons and have uploaded it here. So essentially, I have a table, Pet_Data, with 236,676 lines of data with duplicate ID_Numbers that appear in different rows with different corresponding Pet data. I want to manipulate this table so that I have one row per ID_Number with four columns that catch all of the data stored in the Pet column corresponding to this ID_Number. So for example, with the ID_Number 10000003 the columns the align with this number would have the values, Pet_Dog = Four, Pet_Cat = Four, Pet_Fish = Four and Pet_Rank = Four. For ID_Number 40616689 the columns that align with this number would have the values, Pet_Dog = BLANK, Pet_Cat = Four, Pet_Fish = BLANK and Pet_Rank = Four.

To sum up what my goal is, I want to merge all of the information I have for a unique id number and store them in columns as opposed to having several rows with the same id numbers.

I am very very new to Access so if you have a suggestion on how to accomplish this, I would very much appreciate detailed instructions. Like I don't know what to do with these instructions "CROSSTAB the raw data or aggregate the denormalized query"

Thanks so much for you help! I really really appreciate it! Pet_Database_2.zip

[June7]

Options are not changed.

I tested the GROUP BY Max with your data and it worked. Is this a small sample of your data? This approach is 'aggregating the denormalized data' and simulates a CROSSTAB query and would be very slow with several times the number of records.

SELECT Pet_Data.ID_Number, Max(IIf([Pet] Like "Pet_One*",[Pet],Null)) AS Pet1, Max(IIf([Pet] Like "Pet_Two*",[Pet],Null)) AS Pet2, Max(IIf([Pet] Like "Pet_Three*",[Pet],Null)) AS Pet3, Max(IIf([Pet] Like "Pet_Four*",[Pet],Null)) AS Pet4
FROM Pet_Data
GROUP BY Pet_Data.ID_Number;

Again, Pet field should be two fields then a true CROSSTAB could be generated. This would let Access do the heavy lifting to pivot the data.

Third option is Allen Browne's ConcatRelated function.

Only other option I can think of involves writing records to a temp table.