Concatenating fields from multiple records

[symonedge]

Hi

I have 3 tables - Jobs, JobEmployees and Employees. Jobs has a
one-to-many relationship with JobEmployees, and JobEmployees has a
one-to-many relationship with Employees. The table JobEmployees is used
to assign Employees to various Jobs. What I want to do is filter
JobEmployees by JobID and find all Employees that have been assigned to
that job. Rather than using a form/subform to display these I am
wanting a query to concatenate all of the Employee names into a field
per Job record. This will allow me to sort all Jobs by date and have
all associated Employee names displayed in one field.

Cheers

 

[Duane Hookom]

If I understand your question correctly, there is a generic concatenate
function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[BruceM]

I can't answer your particular question, but I can suggest a way of
producing the desired results. If each job can have many employees, and
each employee can be associated with many jobs, that is a many-to-many
relationship. Your tables may would look something like this:

tblJob
JobID (primary key, or PK)
Job description, etc.

tblEmployee
EmployeeID (PK)
FirstName
LastName, etc.

tblJobEmployee
JobEmployeeID (PK)
JobID (foreign key, or FK)
EmployeeID (FK)

Create one-to-many relationships (enforce referential integrity) between the
PKs of tblJob and tblEmployee and their namesake FK fields in
tblJobEmployee.
Make a form (frmJob) based on tblJob (or on a query based on tblJob).
Make a subform (fsubJobEmployee) based on tblJobEmployee (or on a query
based on tblJobEmployee). Set its default view to Continuous.
On fsubJobEmployee make a combo box (cboEmployee) bound to EmployeeID.
Click the three dots in Row Source (combo box properties). Add
tblEmployees. Put EmployeeID in the first column. In the second column you
may want FullName: [LastName] & ", " & [FirstName]. Set the combo box
column count to 2, the bound column to 1, and the column widths to 0";1".
With frmJob open in design view, drag the icon for fsubJobEmployee onto it.
Now you can enter job information onto the main form, and select names in
the subform. Those subform records (stored in tblJobEmployee) will be
related to the main record.
You can use a report to view job information, including the assigned
employees. Just build a report and subreport as you did the form and
subform (with a text box instead of a combo box on the subreport). With the
report open in design view, click View > Sorting and Grouping. Select
JobID, Group Header Yes. You can set Force New Page in the group header to
Before Section (or you can add a group footer, and Force New Page After
Section; there are several options here). This will give you a job record
complete with a listing of employees.

 

[Guest]

Hi Duane,

I have used the database to pull together the query and am getting an error;

'Run-Time error '-2147217900 (8004e14)'
Syntax error (Missing operator) in query expression 'Client ID = 1141120'

Do you have any ideas where i may be going wrong?

My column data: Client Groups: Concatenate("SELECT CG FROM
TBL_cg3_MaxMainClientGroup WHERE Client ID = " & [Client ID])

Many thanks,


Mark.

 

[Guest]

I think i found the error!

The fields i was using had spaces in them (eeek! Spaces in field names!!)

Hi Duane,

I have used the database to pull together the query and am getting an error;

'Run-Time error '-2147217900 (8004e14)'
Syntax error (Missing operator) in query expression 'Client ID = 1141120'

Do you have any ideas where i may be going wrong?

My column data: Client Groups: Concatenate("SELECT CG FROM
TBL_cg3_MaxMainClientGroup WHERE Client ID = " & [Client ID])

Many thanks,


Mark.

If I understand your question correctly, there is a generic concatenate
function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[hootyeah]

Many thanks Duane - this looks like what I need.

I will try this when I get home!

 

[hootyeah]

How do I implement this function? Do I just copy the module then recall
this everytime I call the Concatenate(), whether this be a query or
form?

Cheers

 

[Duane Hookom]

You need to import the module into your mdb file. You should then compile
your code to make sure you have the appropriate references set.

Then, use the function in almost any place a standard function would work
ie: query, control source, code,....

 

[hootyeah]

Looking at the code - I couldnt see anything that related to
tables/queries/forms - what would I need to alter?

Cheers again

 

[Duane Hookom]

You don't need to add any tables/queries/forms to use the function. You
decide where you want to see the concatenated information and place the
function there.

 

[hootyeah]

Sorry Duane - I meant this comment:

"You should then compile your code to make sure you have the
appropriate references set."

What do I need to compile to make this function work with another db?
In the basConcatenate module I couldnt see code relating to the
database. I want to copy this module into my own db and try it.

Sorry for the dumb questions but Im a bit of a coding moron.

Thanks again

 

[Duane Hookom]

After you have imported or copied the code into your mdb file, select
Debug->Compile... to make sure you don't have any errors. If you have an
error, you select Tools->References and check for missing references.

 

[hootyeah]

Cheers Duane, thanks for your patience.


Symon