MS Access or MS Excel many to one

I have a data sheet that looks like:

Name      Field1   Field2 Field3 Field4
John Doe   AAA      BBB    CCC    DDD
John Doe   AAA      BBB    CCC    EEE
John Doe   AAA      BBB    CCC    FFF

I want it to look like:

Name      Field1   Field2
John Doe   AAA      DDD
John Doe   BBB      EEE
John Doe   CCC      FFF

At the very least the Name and Field1 fields. Field2 is optional.

Is there a way to do this quickly and easily in MS Access or MS Excel? The transpose option in Excel doesn't quite cut it. I know there is a (probably) a query process in Access, I just cannot remember at the moment.

microsoft-access

Kelli

Posted 2011-06-06T23:07:57.313

Reputation: 31

1I see the difference between the two datasheets but I don't understand what the algorithm is for deciding how to shift things around. Please use more unique sample data on the initial dataset to make your objective clearer. – Tony Toews – 2011-06-08T06:42:13.427

Tony, I have data in three columns that I want to appear in one field on a form. The only way I know how to do this at the moment is if the data is in rows as a unique field. I want to be able to only see one item of data at a time, comment on it and click to the next data piece. If there is another way to do this (which I think there is and I cannot remember) then I'll be happy to hear it. – Kelli – 2011-06-08T14:14:26.153

Answers

If I understand your problem correctly you can create queries for each of Field1 through Field4. Then you can Union those together to create a datasheet with four times as many records.

However you can't edit the data in a dataset based in a Union query. So you will need to add an constant, 1-4, in each of the base queries, have that constant in the Union query and then open up one of four forms based on that constant from your main continuous form. Ugly and clumsy.

Tony Toews

Posted 2011-06-06T23:07:57.313

Reputation: 424

I was working on this and decided to go with a create table and multiple append queries. I'll create a macro. The Union query seemed to put them all together in one field in one column. I needed in one column, separate fields. Basically it is one parent and multiple child horizontally; not able to cut out the children to a separate table to eliminate the duplicated data as there was nothing to link the children. The file I have is not pretty, but that is what I was given. Now I'll have all the children lined up and can create the appropriate relationships, etc. Thanks for your help! – Kelli – 2011-06-08T19:21:59.150