Combine multiple records into one

LxQ

Well-known Member
Joined
Feb 9, 2006
Messages
619
I would like to combine records into a query. For example, the table would be

Jane, Doe, teacher, math
Jane, Doe, teacher, English

I would like the query to have:
Jane, Doe, teacher, math English
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
How do you have this line: Jane, Doe, teacher, math?
A line in a single cell?
In this way:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Data[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane, Doe, teacher, math[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jane, Doe, teacher, English[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
How do you have this line: Jane, Doe, teacher, math?
A line in a single cell?

No, separate cells

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD="align: center"]First[/TD]
[TD="align: center"]Last[/TD]
[TD="align: center"]position[/TD]
[TD="align: center"]subject[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]teacher[/TD]
[TD]math[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]teacher[/TD]
[TD]English[/TD]
[/TR]
</tbody>[/TABLE]

So I'm looking for either combined "math English" in some form, or adding cells, such as subject1, subject2
 
Last edited:
Upvote 0
Dante,

Not sure if you are aware, but this question is posted in the Access Forum.
Many people use the "Zero Reply Posts" listing to look for new question, and miss that over on the far left, it shows which forum the question is in (they aren't all Excel questions!)
 
Upvote 0
LxQ,
That could be difficult to do in an Access Query (I am not sure if it is even possible).
If it was me, I would probably use VBA and DAO Recordsets, and loop through my data and write the results to a new table.
 
Upvote 0
I suppose it could be asked what's the end goal/purpose.

As a matter of working with relational data that's not a useful pattern - so end goal is probably not data updates, data maintenance, or any kind of data processing. Your end goal might be a report or form ... in which case you could do more of your work in the form or report, not in the query as such.
 
Upvote 0
Thanks xenou, but as you know, when a request comes from above as "all in one row," I have little to argue. :) Yes, a report instead of a query would work, if I could get it all in one row.

So, if I were to make the the first three columns (in this example) a combination primary key, pasting the records would allow one subject in subject1 column but pasting the next record would error out and be pasted in Paste Errors table. Can that table be used to add the subjects to the existing records as a subject2? then if there are more errors, subject3?
 
Last edited:
Upvote 0
Are you agreeable to the option that I mentioned?
If it was me, I would probably use VBA and DAO Recordsets, and loop through my data and write the results to a new table.
If so, I may be able to play around with it some tonight.
Just provide more details, such as, is this how your data really looks, or is it a simplified version?
I would need to know your exact table and field names.
 
Upvote 0
Thank you, this is a simplified version, the table is called CERTCR2 and the fields are SSN, CREDNAME, SUBJECT, CREDENTIAL, TYPECODE, CREDTYPE, and CREDEXP. The SUBJECT is with multiple records.
 
Upvote 0

Forum statistics

Threads
1,223,565
Messages
6,173,071
Members
452,500
Latest member
FrankSit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top