Concatenate Woes!!

[KongMin77]

I have three Tables with its structure listed below:

Nurses (NurseID, etc, etc, etc)
Nurse Skills (ID, NurseID, SkillsID)
Skills (SkillsID, Code, Description)

Basically, my output should display a Distinct NurseID with multiple
Descriptions concatenated.
The form has a Descriptions textbox which I'd like to output the concatenated
Descriptions into.
eg
txtNurseID = 1001
txtDescription = Skill1, Skill2, Skill3

Is this possible?

Any help appreciated. Cheers!

 

[Dirk Goldgar]

I have three Tables with its structure listed below:

Nurses (NurseID, etc, etc, etc)
Nurse Skills (ID, NurseID, SkillsID)
Skills (SkillsID, Code, Description)

Basically, my output should display a Distinct NurseID with multiple
Descriptions concatenated.
The form has a Descriptions textbox which I'd like to output the
concatenated Descriptions into.
eg
txtNurseID = 1001
txtDescription = Skill1, Skill2, Skill3

Is this possible?

Any help appreciated. Cheers!

First you need a query that joins the three tables and returns one row
for each Nurse/Skill combination. Quick "air SQL":

SELECT Nurses.NurseID, Skills.Description
FROM
(
Nurses
INNER JOIN
[Nurse Skills]
ON Nurses.NurseID = [Nurse Skills].NurseID
)
INNER JOIN
Skills
ON [Nurse Skills].SkillsID = Skills.SkillsID;

Suppose you store this query as "qryNurseSkills".

Now you need to use a function to look up the records for a particular
nurse in this query's results and concatenate the Description values
from all the records. A handy, general-purpose function to do this is
posted at:

http://www.mvps.org/access/modules/mdl0004.htm
Modules: Return a concatenated list of sub-record values

In calling the fConcatChild function you'll find there, you'd pass
arguments as follows:

strChildTable : "qryNurseSkills" (or whatever you called the
query)
strIDName : "NurseID"
strFldConcat : "Description"
strIDType : "Long" (probably)
varIDvalue : [NurseID]

So your final query might look something like this:

SELECT
Nurses.*,
fConcatChild("qryNurseSkills", "NurseID", "Description", "Long",
[NurseID])
FROM Nurses;

 

[fredg]

I have three Tables with its structure listed below:

Nurses (NurseID, etc, etc, etc)
Nurse Skills (ID, NurseID, SkillsID)
Skills (SkillsID, Code, Description)

Basically, my output should display a Distinct NurseID with multiple
Descriptions concatenated.
The form has a Descriptions textbox which I'd like to output the concatenated
Descriptions into.
eg
txtNurseID = 1001
txtDescription = Skill1, Skill2, Skill3

Is this possible?

Any help appreciated. Cheers!

This is not a good method.
Your txtDescription field is not needed.

Use your Skills table as a sub-table of the Nurses table.
Each SkillID the nurse has is entered as a separate record in this
table, bound to the NurseID field.

This way new skills can easily be added and obsolete skills removed
without having to update the Description field.
Do not store duplicate data in Access.

 

[Gman]

Um - the three table system he's using avoids duplicate data - your two
table method would mean reentering the same skill description and skill
code should two nurses have the same skill (which is rather likely I
would hope).

I think his problem is more how to cram all of this into one textbox for

 

[fredg]

Um - the three table system he's using avoids duplicate data - your two
table method would mean reentering the same skill description and skill
code should two nurses have the same skill (which is rather likely I
would hope).

I think his problem is more how to cram all of this into one textbox for

It wasn't my intention for him to change from 3 to 2 tables, just to
show that he shouldn't store duplicate data, which is how I
interpreted his question as he has a Description field in the table
that he wanted to concatenate data into.

 

[Gman]

Ahhhhh..... understood.

It was actually a Descriptions textbox on a form he wanted to
concatenate to, rather than a field in a table.

 

[KongMin77 via AccessMonster.com]

G'Day Dirk,

Thanks absolutely for your help. The module works and the queries you
provided returned the right parameters the module required. In short, It
worked like a charm.

A little confused at first with what 'exact' paramater to pass to the
function. With a bit of trial and error though, and voila, a form that
displays nurses details along with the concatenated Descriptions

Thanks so much!!!