Lookup consolidation

  • Thread starter Thread starter PhotoFinish
  • Start date Start date
P

PhotoFinish

This is probably the acme of simplicity, but I can't get it:
I have a lookup table that has job titles for employees so it might say:
12345 Butcher
12345 Baker
12221 Candlestick maker
12221 Baker
12123 Butcher

What I want is to be able in a query (or subsequent report) to string them
together thusly:
12345 Butcher, Baker
12221 Baker, Candlestick maker
12123 Butcher
Without having to resort to a subreport, which takes up too much room and is
ugly to boot.

What would I need to do to consolidate these?

Thank you.
 
PhotoFinish said:
This is probably the acme of simplicity, but I can't get it:
I have a lookup table that has job titles for employees so it might say:
12345 Butcher
12345 Baker
12221 Candlestick maker
12221 Baker
12123 Butcher

What I want is to be able in a query (or subsequent report) to string them
together thusly:
12345 Butcher, Baker
12221 Baker, Candlestick maker
12123 Butcher
Without having to resort to a subreport, which takes up too much room and is
ugly to boot.
Click to expand...


Not simple.

You need to create a function to do that. There's a good
one at:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16
 
This is probably the acme of simplicity, but I can't get it:
I have a lookup table that has job titles for employees so it might say:
12345 Butcher
12345 Baker
12221 Candlestick maker
12221 Baker
12123 Butcher

What I want is to be able in a query (or subsequent report) to string them
together thusly:
12345 Butcher, Baker
12221 Baker, Candlestick maker
12123 Butcher
Without having to resort to a subreport, which takes up too much room and is
ugly to boot.

What would I need to do to consolidate these?

Thank you.
Click to expand...

A bit of VBA code: see
http://www.mvps.org/access/modules/mdl0004.htm
 
Um, not to be a bother or anything, but I created a module with John's
fConcatChild as a public function. But if I try to include it in a query, it
says "undefined function fConcatChild" and if I try to use it in the
Immediate window, as his Usage example implies, I get Compile error: Expected
variable or procedure, not module.
Can it only be a local function in a form?
Thanks.
 
Um, not to be a bother or anything, but I created a module with John's
fConcatChild as a public function. But if I try to include it in a query, it
says "undefined function fConcatChild" and if I try to use it in the
Immediate window, as his Usage example implies, I get Compile error: Expected
variable or procedure, not module.
Can it only be a local function in a form?
Thanks.
Click to expand...

It sounds like you named the function fConcatChild and *ALSO* named the module
fConcatChild. The modules and the subs/functions within them share the same
namespace so you must use a DIFFERENT name for the module.

Try changing the name of the module to basConcat say and see if that helps.
 
Spiffy! Thanks so much!

John W. Vinson said:
It sounds like you named the function fConcatChild and *ALSO* named the module
fConcatChild. The modules and the subs/functions within them share the same
namespace so you must use a DIFFERENT name for the module.

Try changing the name of the module to basConcat say and see if that helps.
Click to expand...
 
Back
Top