Combine multiple records into one

[LxQ]

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

 

[DanteAmor]

How do you have this line: Jane, Doe, teacher, math?
A line in a single cell?
In this way:

	A
1	Data
2	Jane, Doe, teacher, math
3	Jane, Doe, teacher, English

<tbody>
</tbody>

 

[LxQ]

How do you have this line: Jane, Doe, teacher, math?
A line in a single cell?

No, separate cells

1	First	Last	position	subject
2	Jane	Doe	teacher	math
3	Jane	Doe	teacher	English

<tbody>
</tbody>

So I'm looking for either combined "math English" in some form, or adding cells, such as subject1, subject2

 

[Joe4]

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!)

 

[Joe4]

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.

 

[xenou]

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.

 

[LxQ]

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?

 

[Joe4]

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.

 

[LxQ]

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.

 

[LxQ]

If Excel is a better solution for this, I can deal with that too.