Concatenating a single column

  • Thread starter Thread starter Rossz
  • Start date Start date
R

Rossz

I've been asked to make a seemingly simple change to a report. Currently
it looks something like this (fake data). This is used in the warehouse to
prepare the magazines for pickup by the drivers:

-------------------------------------------------
Drive Name: John Smith

Route # Publication Name Bundles Singles
123 Better Living 20 3
124 Better Living 10 0
124 Stupid Things 5 2
-------------------------------------------------

The magazines are packages in bundles (e.g. 20 magazines in a bundle).
They want the report changed to this:

-------------------------------------------------
Drive Name: John Smith

Routes: 123 124

Publication Name Bundles Singles
Better Living 30 3
Stupid Things 5 2
-------------------------------------------------

But I can't figure out how to print the route numbers horizontally. It
would be best if the query combined all the route numbers into a single
string for each driver, but I'd settle for handling this in the report.

Is this one of those things that requires a bit of scripting to handle?
 
In the report design under view>sort and grouping add the
route field and change display header to "Yes" add the
route field in the header area of route and the route will
print only once.
 
In the report design under view>sort and grouping add the
route field and change display header to "Yes" add the
route field in the header area of route and the route will
print only once.
Click to expand...

That prints the routes vertically, e.g.:

123
124
....

Once for each unique route. I need this:

123 124 ...


The maximum number of routes per driver is about 5 (all are three digit
numbers), so I have no worries about line overflow.
 
The answer is going to come from VBA code. Whether you do it in the report,
or write the data to a temp table prior to processing the report, either way
you'll need to loop through the values to be printed, and store the unique
Route#.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Rossz said:
I've been asked to make a seemingly simple change to a report. Currently
it looks something like this (fake data). This is used in the warehouse to
prepare the magazines for pickup by the drivers:

-------------------------------------------------
Drive Name: John Smith

Route # Publication Name Bundles Singles
123 Better Living 20 3
124 Better Living 10 0
124 Stupid Things 5 2
-------------------------------------------------

The magazines are packages in bundles (e.g. 20 magazines in a bundle).
They want the report changed to this:

-------------------------------------------------
Drive Name: John Smith

Routes: 123 124

Publication Name Bundles Singles
Better Living 30 3
Stupid Things 5 2
-------------------------------------------------

But I can't figure out how to print the route numbers horizontally. It
would be best if the query combined all the route numbers into a single
string for each driver, but I'd settle for handling this in the report.

Is this one of those things that requires a bit of scripting to handle?
Click to expand...


Yes it does require some VBA code. Here's a function the
does it for you.
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'
 
Yes it does require some VBA code. Here's a function the
does it for you.
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generi
c%20Function%20To%20Concatenate%20Child%20Records'
Click to expand...

That did that job! A big thank you.

For anyone using this function, if you are doing a WHERE on a string, you
need to quote it. In my case I had to do this:

=concatenate("SELECT DISTINCT route FROM RouteTable
WHERE DriverName='" & [DriverName] & "'")

Note: If I had designed the database, I would have put the driver names in
another table with a unique driver ID, but that's a seperate issue.
 
Marshall Barton wrote
Rossz said:
That did that job! A big thank you.

For anyone using this function, if you are doing a WHERE on a string, you
need to quote it. In my case I had to do this:

=concatenate("SELECT DISTINCT route FROM RouteTable
WHERE DriverName='" & [DriverName] & "'")

Note: If I had designed the database, I would have put the driver names in
another table with a unique driver ID, but that's a seperate issue.
Click to expand...

Right.

But note that you need to quote the value in the Where
clause because the field (in this case DriverName) is a text
field. You would not use the quotes if the field is
numeric.
 
Back
Top