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

MRH-ITS
MRH-ITS used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
Allen Browne has developed a utility function that concatenates results of a query in one row....take a look here
Top Expert 2014

Commented:
Consulting
Distinguished Expert 2017
Commented:
Hmm, cross tab query ?

TRANSFORM First(tblBooks.fBookTitle) AS FirstOfBookTitle
SELECT tblPeople.fFName, tblPeople.fLName
FROM tblBooks INNER JOIN (
         tblPeople INNER JOIN tblPeopleBooks
             ON tblPeople.IDp = tblPeopleBooks.fPeopleID)
         ON tblBooks.IDb = tblPeopleBooks.fBooksID
GROUP BY tblPeople.fFName, tblPeople.fLName
PIVOT tblBooks.fBookTitle;

Open in new window



PS: I Don't see the purpose of the fPeopleID column in tblBooks
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
fPeopleID was to be FK to the PK 'IDp' in tblPeople table, if needed for a relationship.
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
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.

Author

Commented:
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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial