Multi-valued field

  • Thread starter Thread starter Jimmy G.
  • Start date Start date
J

Jimmy G.

I am trying in create a query in which an record (i.e., an individual) which
has multiple values in a joined table (i.e., activities in which s/he are
actively interested (e.g., travel, sports, reading, gardening, cooking)
appears as one row but all the associated values appear in separate columns.
I can't do a cross-tab query since the number of unique values are very large
yet the maximum numbers of values that any record had is five. Any
suggestions would be helpful. Thank you.
 
Here's a data sample :

ENTITYID COMMCODE
0000005480 FALC
0000005480 FDHC
0000006251 FALC
0000006251 FDHC
0000013620 FCC
0000013620 FSBRC
0000014107 FSBRC
0000014507 FALC
0000014507 FCC
0000014507 FSBAF
0000021411 FSUC
0000023399 FSBRC
0000023848 FDHC
0000023849 FSBRC
0000024478 FEITF
0000024478 FSBRC
0000027878 FBOV
0000027878 FSUC
 
I am trying in create a query in which an record (i.e., an individual) which
has multiple values in a joined table (i.e., activities in which s/he are
actively interested (e.g., travel, sports, reading, gardening, cooking)
appears as one row but all the associated values appear in separate columns.
I can't do a cross-tab query since the number of unique values are very large
yet the maximum numbers of values that any record had is five. Any
suggestions would be helpful. Thank you.
Click to expand...

If it's just for reporting purposes, you can use a bit of code to concatenate
the child record values into a text string:

http://www.mvps.org/access/modules/mdl0004.htm
 
Unfortunately, I'm not very familiar with writing in SQL; I usually work in
the design mode.

I like the idea of concatenating the values as a string, but since the there
are dozens of these, I don't want to list them all. Currently, I've written
the field as "ACTIVIITES: fConcatChild ([COMMCODE])", but that returns an
error message of "Undefined function fConcatChild in expression". Is there a
way around writing out the code?
 
Unfortunately, I'm not very familiar with writing in SQL; I usually work in
the design mode.
Click to expand...

No SQL is being discussed here. This is VBA (Visual Basic for Applications)
code.
I like the idea of concatenating the values as a string, but since the there
are dozens of these, I don't want to list them all. Currently, I've written
the field as "ACTIVIITES: fConcatChild ([COMMCODE])", but that returns an
error message of "Undefined function fConcatChild in expression". Is there a
way around writing out the code?
Click to expand...

Sure... follow the instructions on thew webpage, and copy and paste the code
from the webpage into your module. After you do so, select Debug... Compile
<my database> from the menu; correct any errors; and save the module.

Also note that you're trying to pass fConcatChild a single COMMCODE. The
function expects multiple arguments; you'll need to pass more than that one!
From the webpage:

SELECT Orders.*, fConcatChild("Order
Details","OrderID","Quantity","Long",[OrderID]) AS SubFormValuesFROM Orders;

This example is based on Orders and Orders Details tables in Northwind
database which are related in a 1:M relationship. The fConcatChild simply
states Concatenate all values in field Quantity in table Order Details where
linking field is OrderID of datatype Long, for each value of [OrderID]
returned by the table Orders.


In your case you'll need to pass the name of the table containing the
activities, the name of the field which identifies the person, the name of the
field that you want to concatenate, the datatype of that field, and again the
name of the field which identifies the person. I can't see your database so I
don't know what these values will be but you will need all five.
 
Thank you for your reply and I'm sorry I haven't gotten back to you sooner.

I don't understand VBA , but given your instructions, I copied the function
fConcatChild into my modules, debugged it, and saved it. I then wrote the
following expression in a column in a design view:

Committees:
fConcatChild("COMMITTEES","[COMMITTEES]![ENTITYID]","COMMCODE","LONG",[COMMITTEES]![ENTITYID])

Where I'm linking from the 'COMMITTEE' table (which has all my multi-valued
records) to the 'ROSTER_ALL_PURPOSE' table via the 'ENTITYID' field (in the
'COMMITTEE' table) to the 'ENTITYID' field (in the 'ROSTER_ALL_PURPOSE'
table) and I want to concatenate the COMMCODE field (in the 'COMMITTEES'
table)which is the code for the committees in which an individual is involved.

I'll still getting the error message of "Undefined function 'fconcatChild'
in expression.

Any idea what I'm doing wrong? I appreciate all your help.
John W. Vinson said:
Unfortunately, I'm not very familiar with writing in SQL; I usually work in
the design mode.
Click to expand...

No SQL is being discussed here. This is VBA (Visual Basic for Applications)
code.
I like the idea of concatenating the values as a string, but since the there
are dozens of these, I don't want to list them all. Currently, I've written
the field as "ACTIVIITES: fConcatChild ([COMMCODE])", but that returns an
error message of "Undefined function fConcatChild in expression". Is there a
way around writing out the code?
Click to expand...

Sure... follow the instructions on thew webpage, and copy and paste the code
from the webpage into your module. After you do so, select Debug... Compile
<my database> from the menu; correct any errors; and save the module.

Also note that you're trying to pass fConcatChild a single COMMCODE. The
function expects multiple arguments; you'll need to pass more than that one!
From the webpage:

SELECT Orders.*, fConcatChild("Order
Details","OrderID","Quantity","Long",[OrderID]) AS SubFormValuesFROM Orders;

This example is based on Orders and Orders Details tables in Northwind
database which are related in a 1:M relationship. The fConcatChild simply
states Concatenate all values in field Quantity in table Order Details where
linking field is OrderID of datatype Long, for each value of [OrderID]
returned by the table Orders.


In your case you'll need to pass the name of the table containing the
activities, the name of the field which identifies the person, the name of the
field that you want to concatenate, the datatype of that field, and again the
name of the field which identifies the person. I can't see your database so I
don't know what these values will be but you will need all five.
Click to expand...
 
Thank you for your reply and I'm sorry I haven't gotten back to you sooner.

I don't understand VBA , but given your instructions, I copied the function
fConcatChild into my modules, debugged it, and saved it. I then wrote the
following expression in a column in a design view:

Committees:
fConcatChild("COMMITTEES","[COMMITTEES]![ENTITYID]","COMMCODE","LONG",[COMMITTEES]![ENTITYID])

Where I'm linking from the 'COMMITTEE' table (which has all my multi-valued
records) to the 'ROSTER_ALL_PURPOSE' table via the 'ENTITYID' field (in the
'COMMITTEE' table) to the 'ENTITYID' field (in the 'ROSTER_ALL_PURPOSE'
table) and I want to concatenate the COMMCODE field (in the 'COMMITTEES'
table)which is the code for the committees in which an individual is involved.

I'll still getting the error message of "Undefined function 'fconcatChild'
in expression.
Click to expand...

One way this could happen is if you put the function fconcatChild into a
module also named fconcatChild. Annoyingly, Modules and Procedures share the
same namespace, so this isn't allowed! If you did so, just change the name of
the Module to something else (basConcat maybe), compile again, and try once
more.

Also change your ! to . in the call. The ! delimiter is used for Form and
other object references, not table and fieldnames.
 
Thank you for your reply. And patience.

When I first copied the function over from the webpage you referenced, I
pasted it into a new module, debugged it, and saved it as 'fConcateChild'.
There was no module by that name earlier. Was there a different location
where I was to paste the function? I'm still getting that error message so
obviously I've put it somewhere the query isn't looking at.

John W. Vinson said:
Thank you for your reply and I'm sorry I haven't gotten back to you sooner.

I don't understand VBA , but given your instructions, I copied the function
fConcatChild into my modules, debugged it, and saved it. I then wrote the
following expression in a column in a design view:

Committees:
fConcatChild("COMMITTEES","[COMMITTEES]![ENTITYID]","COMMCODE","LONG",[COMMITTEES]![ENTITYID])

Where I'm linking from the 'COMMITTEE' table (which has all my multi-valued
records) to the 'ROSTER_ALL_PURPOSE' table via the 'ENTITYID' field (in the
'COMMITTEE' table) to the 'ENTITYID' field (in the 'ROSTER_ALL_PURPOSE'
table) and I want to concatenate the COMMCODE field (in the 'COMMITTEES'
table)which is the code for the committees in which an individual is involved.

I'll still getting the error message of "Undefined function 'fconcatChild'
in expression.
Click to expand...

One way this could happen is if you put the function fconcatChild into a
module also named fconcatChild. Annoyingly, Modules and Procedures share the
same namespace, so this isn't allowed! If you did so, just change the name of
the Module to something else (basConcat maybe), compile again, and try once
more.

Also change your ! to . in the call. The ! delimiter is used for Form and
other object references, not table and fieldnames.
Click to expand...
 
Thank you for your reply. And patience.

When I first copied the function over from the webpage you referenced, I
pasted it into a new module, debugged it, and saved it as 'fConcateChild'.
There was no module by that name earlier. Was there a different location
where I was to paste the function? I'm still getting that error message so
obviously I've put it somewhere the query isn't looking at.
Click to expand...

Reread my suggestion.

If you have a Module named 'fConcatChild' and that Module contains a function
named 'fConcatChild' Access will get confused.

Change the name of the Module from 'fConcatChild' to 'basNames'. Leave the
line

Public Function fConcatChild(....

alone.
 
O.K. I'm not getting the error message any longer, and the query executes but
does not return any valus. I'm wondering two things: since the field I'm
trying to concatenate is a text field, should I be using 'STRING' instead of
'LONG'; and what should be the fifth string value (right now, my value this
the linked field in my multi-valued table)?

I'm sorry I'm so dense about this.
 
O.K. I'm not getting the error message any longer, and the query executes but
does not return any valus. I'm wondering two things: since the field I'm
trying to concatenate is a text field, should I be using 'STRING' instead of
'LONG'; and what should be the fifth string value (right now, my value this
the linked field in my multi-valued table)?
Click to expand...

You should indeed use String, since... umm... it's a string. The fifth value
should be the ID of the current record of the parent table, which I'm guessing
is ENTITYID.
 
Back
Top