Making a query that merges the unique data with the same persons record. (1 Viewer)

[Nano]

As mentioned before on a different topic I am building a database to sort through publication authors. I currently have the table sorted in a way that only the main author is listed for the 3000 records. Now I need a way to remove duplicate entries on a new table. Each entry is a different publication. Each record has a DOI code that is unique and an author which might be a duplicate. I want a new table listed by author once and a new field that list all of the DOI numbers for that author.

I know how to do a query to show only the unique names, but how to I also get the database to combine the records that share the same author.?

 

[plog]

Your phrasing is scaring me. It sounds like you have an unnormalized table structure, or are moving in that direction.

Can you post some sample data of what you have, and then what you want it to ultimately look like?

 

[Nano]

Your phrasing is scaring me. It sounds like you have an unnormalized table structure, or are moving in that direction.

Can you post some sample data of what you have, and then what you want it to ultimately look like?

Sorry, I am sure that I am breaking some of the rules for database design. The table I am using was made when I imported the data from a excel document someone else created. I have attached a picture of some of the Authors and DOI (I hid the other fields for now as I don't need them.)

Ultimately I want a record for each author with a list of DOI from the author. Along with contact information for the author, which I will add at a later time.

 

[plog]

Halfway there:

...and then what you want it to ultimately look like?

 

[Nano]

Halfway there:

I guess something like this. Unless you have a better idea on how to store the DOI codes.

(This is just a sample, I am not using this table for the finished product.)

 

[plog]

Horrible examples. First, John Doe isn't in your first example so having the 2nd example only include him gives me no frame of reference between the two. All I can tell you is that you need to store discrete pieces of data in its their fields. First name in its own field, last name in its own field, every DOI in its own field, not jammed together.

Read up on normalization.

 

[Nano]

Horrible examples. First, John Doe isn't in your first example so having the 2nd example only include him gives me no frame of reference between the two. All I can tell you is that you need to store discrete pieces of data in its their fields. First name in its own field, last name in its own field, every DOI in its own field, not jammed together.

Read up on normalization.

Thanks I think, I appreciate your advice but you don't have to say it so rudely. I know I am a novice when it comes to Access which is why I ask for help here.

 

[JHB]

If you still want it done, for what reason you may have, look at the thread
http://www.access-programmers.co.uk/forums/showthread.php?t=248376 post #6, it is exactly the same you are asking for. The Names = yours DOI and the Address = yours MainAuthor.