asked on

Trying to get all related data for a record to show on 1 line

Haven't used access in a while, and cannot remember how to do something which I thought I remembered as simple.

I'm trying to get a query or form or report to display all data for a record on 1 row. Right now data displays like this...

fFName	fLName	fEmployeeNum	fBookTitle
Jack	Jones	jjs123456	Book1
Jack	Jones	jjs123456	Book2
Jack	Jones	jjs123456	Book3
Jack	Jones	jjs123456	Book4
Cathy 	Carson	ccn987654	Book1
Pete 	Parsons	pp678123	Book1
Pete 	Parsons	pp678123	Book3
Pete 	Parsons	pp678123	Book4

Open in new window

And I want it to show like...

Jack	Jones	jjs123456	Book1	Book2	Book3	Book4
Cathy 	Carson	ccn987654	Book1
Pete 	Parsons	pp678123	Book1	Book3	Book4

Open in new window

I'm using the following 3 tables

tblPeople
-IDp
-fFName
-fLName
-fEmployeeNum

tblBooks
-IDb
-fPeopleID (FK to tblPeople IDp)
-fBookTitle

tblPeopleBooks
-fIDpb
-fPeopleID (FK tp tblPeople IDp)
-fBooksID (FK tp tblBooks IDb)

As I said, I haven't used Access in a while, and I'm a bit rusty.

John Tsioumpris

Allen Browne has developed a utility function that concatenates results of a query in one row....take a look here

aikimark

Here's a useful article and code you can use:
https://www.experts-exchange.com/articles/2380/Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

ASKER CERTIFIED SOLUTION

Fabrice Lambert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MRH-ITS

ASKER

fPeopleID was to be FK to the PK 'IDp' in tblPeople table, if needed for a relationship.

Fabrice Lambert

From what I understood, you have a many to many Relationship between the tblBooks and tblPeople tables.
This relationship is implemented via the tblPeopleBooks table, so no need to have an additional key in th tblBooks table.

That will effectively make a many to many and many to one relationship between the tables, wich make no sens.

MRH-ITS

ASKER

Perfect, Fabrice! Exactly what I was looking for; The output was a little messy for some reason, but I'm exporting to a csv text, and running a couple RegEx's against it for cleanup.

I learned and re-learned a lot by picking apart the query you provided, particularly the nesting of the inner joins of the SELECT; I expanded it to include other fields, and it really fleshed out the data. I hope I did that correctly; I'll bounce it against other data to see.

Thanks again, all!