combining the content from multiple rows into 1

[NOL]

Hi,
I can't see the light anymore !


I have 2 tables :

DEAL Table
Deal_ID
User_ID
eg. Deal 1
User 1


USER Table
User_ID
Name
eg User 1
David

A deal can have more than 1 users under it .
I want to be able to show a concatenated list of all users for every deal.

eg.
Deal 1 John
Deal 1 Robert
Deal 1 David
Deal 2 Katie
Deal 2 David
Deal 3 John

I must show:

Deal 1 John/Robert/David
Deal 2 Katie/David
Deal 3 John

Would be grateful for any help .
Thanks,
Gina.

 

[Jon K]

Take a look at this page about returning a concatenated list of all the values of a field. It contains a function that you can use.
http://www.mvps.org/access/modules/mdl0004.htm


You can copy the fConcatChild function to a Module in your database. And before saving the module, change the line:-
varConcat = varConcat & rs(strFldConcat) & ";"
to:-
varConcat = varConcat & rs(strFldConcat) & "/"


Then create two queries:

qryDealID_UserName:-
SELECT Deal.Deal_ID, User.Name
FROM Deal INNER JOIN User ON Deal.User_ID = User.User_ID
ORDER BY Deal_ID;

qryDealID_ConcatNames:-
SELECT DISTINCT Deal_ID,
fConcatChild("qryDealID_UserName","Deal_ID","Name","String",[Deal_ID]) AS ConcatNames
FROM qryDealID_UserName;


Run the second query.

 

[NOL]

Thanks a lot Jon! It worked !
I'd almost given it up as impossible !

I had to make the following change though:
Dim rs As DAO.Recordset
instead of
Dim rs As Recordset

It wouldn't work without that.
Kept giving me error 13, "Type Mismatch"
Do you know why ?

Thanks a million !
Gina.

 

[Jon K]

My guess is that you are using Access 2000 or later, so the default object library used is ADO. But the code in the function was written in Access 97, so DAO object library must be used. Unfortunately, both ADO and DAO have a Recordset object that is basically different.

When a reference to DAO is made and the recordset is qualified with DAO, Access knows which object library to use.

In fact, if DAO is moved above ADO in the references list, DAO will have a higher priority and the original code will run.

 

[NOL]

Thanks Jon !
Yes I'm using Access 2K.
I guess I'll try to use the ADO recordset object in my code ,later when I have more time.
But for now , this works like a charm!

I'm eternally grateful

 

[marjon18us]

Hello,

I just registered today...

I have a similar problem. this is how my table looks like:

Main Tbl
Org ID CompanyName DateofInc
1234 ABC 1/24
1233 A Com 2/5
1444 TSY 3/53

ValidityTbl
OrgID FieldIssue ValidityConsistency
1444 CompanyName Validity
1444 DateofInc Validity

I want to create a query or table that will lookup all the orgIds in the maintbl in the validity table. it will display a "fail" for a given field if it exist in the validity table and it will blank if it does not exist in validity tbl. Desired output is as follows:

Validityquery
Org ID CompanyName DateofInc
1234 [blank] [blank]
1233 [blank] [blank]
1444 Fail Fail

I have created a query but the output showing is duplicated rows for OAId. I want to display it in a single row for each org id. here is what my query displays:
Validityquery
Org ID CompanyName DateofInc
1234 [blank] [blank]
1233 [blank] [blank]
1444 Fail [blank]
1444 [blank] Fail

I would really appreciate your help. I did this in Excel but the macro was too slow (i will be getting 200 thousand data or more). I believe it is faster in Access.

This is not concatenate. The Fail must be displayed in separate fields or columns.