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


-fPeopleID (FK to tblPeople IDp)

-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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
Allen Browne has developed a utility function that concatenates results of a query in one row....take a look here
Fabrice LambertConsultingCommented:
Hmm, cross tab query ?

TRANSFORM First(tblBooks.fBookTitle) AS FirstOfBookTitle
SELECT tblPeople.fFName, tblPeople.fLName
         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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

MRH-ITSAuthor Commented:
fPeopleID was to be FK to the PK 'IDp' in tblPeople table, if needed for a relationship.
Fabrice LambertConsultingCommented:
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-ITSAuthor 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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.