Query Results Next to Each Other :)

[Guest]

Hey Guyz,

I want to create a query that takes all results in one column
(FirstManager)and "pastes" (for lack of a better word) them into another
column (Group) but next to each other seperated by semi-colans.

Like this:

Employee Department FirstManager Group
Person 1 IT Person A Person A; Person B; Person C
Person 2 IT Person B Person A; Person B; Person C
Person 3 IT Person C Person A; Person B; Person C

How can i do this?

Many thanks in advance!

Wayne

 

[Steve]

Many times a question like this suggests there is a problem with the design
of the tables in the database. Why do you want to do this?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John Spencer]

Take a look at Duane Hookom's Concatenate solution.

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

You can download his example and extract the Concatenate function to use to
do that


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Hi John,

Ive done that and replicated exactly what they have done but i get thsi error:

Undefined function 'Concatenate' in expression.

Pls help...

Cheers!

 

[Rob Parker]

That suggests that you haven't copied the module containing the Concatenate
function into your database. In the example database which you were
referred to, it's in a module named basConcatenate. You must copy that
module and save it, with any name other than "Concatenate" (since that's the
name of the function within the module), in your database.

Have a look at the code at the code in that module, since it is designed to
run with either ADO or DAO recordsets - comment/uncomment the indicated
lines appropriately. Then compile the code (from the Debug menu in the
Visual Basic editor), to ensure there are no errors. If you do get any
errors, it will probably be due to missing references: Form the Tools menu,
select References ... and ensure that either Microsoft DAO 3.6 Object
Library or Microsoft ActiveX Data Objects 2.x Library are checked, depending
on the code which you have commented out.

HTH,

Rob

 

[Jamie Collins]

I want to create a query that takes all results in one column
(FirstManager)and "pastes" (for lack of a better word) them into another
column (Group) but next to each other seperated by semi-colans.

i get thsi error:

Undefined function 'Concatenate' in expression.

Access 2007 is the only version with multivalued data types to support
your desired resultset; earlier versions only had scalar data types.
See:

Access 2007 Help
Using multivalued fields in queries
http://office.microsoft.com/en-gb/access/HA101492971033.aspx

I also suggest you research First Normal Form (http://en.wikipedia.org/
wiki/First_normal_form ...) to find out why a Concatenate function in
a query could be problematic (... "This is arguably the worst design
of all, and again not in keeping with the spirit of 1NF. The Telephone
Number heading becomes semantically woolly, as it can now represent
either a telephone number, a list of telephone numbers, or indeed
anything at all...")

Jamie.

--

 

[Guest]

WOW!! That works! Thanks... Though theres a catch... I did a dummy
database and it works perfectly for it. My ACTUAL database wont be so easy
though. Reason is that the actual database was never really a database. It
was a huge Excel spreadsheet that is linked in the database hence there are
no links between tables but one HUGE table (er... well its not that huge)
(And i didnt design it) :$

Anywayz...

So heres the scenario:

I have only 1 table containing the following fields:

- ID (This is an autonumber)

- JobFamily (This field has values such as "Actuarial" "Human Capital" "IT"
"Medical" (etc)

- GenericJobTitle (This field is Text and could have anything in it - If its
Duplicate then i want it to show the JobIDs (as below) concatenated for each

- JobIDs (Also text value. Value's such as "ADM123" "ACT888" (etc))

To simplify the table it looks like this:

ID GenericJobTitle JobFamily JobIDs GenericLoWJobName
1 Operational Specialist Actuarial Actuarial ACT 164 Operational Specialist
2 Operational Specialist Actuarial MS Life Actuarial ACT164B Operational
Specialist
3 Skilled Leader Actuarial Actuarial ACT1 Skilled Leader
4 Skilled Leader Actuarial Actuarial ACT2 Skilled Leader
5 Specialist Leader Actuarial Actuarial ACT 178 Specialist Leader
6 Specialist Leader Group Risk Actuarial Actuarial ACT 200 Specialist Leader

As you can see ID 3 and 4 are duplicated in terms of "GenericJobTitle" i.e.
"Skilled Leader Actuarial" hence i would like their Job IDs to concatenate.
i.e. "ACT1 / ACT2"

Does that make sense?

 

[Rob Parker]

The answer, in theory, is simple: normalize your data. Then you can
concatenate as much as you want. But it won't (can't) work on denormalised
data, which is what you seem to have.

If you haven't done so already, read up about data normalisation. You can
find some useful references here:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

When you understand what you need, fix your existing data.

And when you get stuck with that, post a new question to the appropriate
Access group.

Rob

 

[Jamie Collins]

The answer, in theory, is simple: normalize your data. Then you can
concatenate as much as you want.

Oh the irony...

Jamie.

--