help with Access query

[genxiii]

hey guys. i'm stumped on a problem and i need some help. i have two tables with a 1 to many relationship. i'm need to run a query that will bring the records of the two tables together. my problem is that some records on the 1 table has many records in the many table so when i run the query i get multiple listings of that record each with a differnt part of the the many table. is there ne way that i can have just 1 instance of that record on the 1 side table and have all the records on the many side for that particular record joined together in one field? sorry if this is confusing explanation, hopefully the diagram i put below will help explain.

Table1 Table2
P_ID ProjectName P_ID L_ID
---- ----------- ----------
P1 Project1 P1 L1
P2 Project2 P1 L2
P2 L2

those are my tables. when i run a query with the following SQL

SELECT ActionItem.ActionID, ActionItem.ActionItem, ActionItemLead.LeadID
FROM ActionItem INNER JOIN ActionItemLead ON ActionItem.ActionID = ActionItemLead.ActionID;

i get the results:

Query1
P_ID ProjectName L_ID
----- ------------ -----
P1 Project1 L1
P1 Project1 L2
P2 Project2 L2

notice how the the record P! appears twice, jus with a different L_ID for both of the entries. how can i combine those to make it just one entry?

i want it to be something like this

P_ID ProjectName L_ID
---- ----------- -----
P1 Project1 L1, L2
P2 Project2 L2

is that possible?

sorry for explaining it the way i did. i'm not very good with this stuff or the terms.

 

[gol4]

You want a crosstab query

something like this would be close

TRANSFORM Max(mytable.Lleadid) AS [The Value]
SELECT mytable.projectname
FROM mytable
GROUP BY mytable.projectname
PIVOT [leadid];

 

[Norris68]

Probably best to create a public function that lists the child fields as a string.
e.g.

Public Function ActionLeads(ActionID As Long) As String
'Open up the ActionItemLead table with all the records that match the ActionID and cocatenate them into a string that is the return parameter

Then call the function within your query:

SELECT ActionItem.ActionID, ActionItem.ActionItem, ActionLeads(ActionID)
FROM ActionItem;

 

[RickSpr]

In Norris68's answer, you need to add the DISTINCT keyword after SELECT to eliminate the duplicates.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein