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.
MRH-ITSAsked:
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
0
Fabrice LambertFabrice LambertCommented:
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
0

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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

MRH-ITSAuthor Commented:
fPeopleID was to be FK to the PK 'IDp' in tblPeople table, if needed for a relationship.
0
Fabrice LambertFabrice LambertCommented:
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.
0
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!
0
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.