Prev: Grouping reports and subreports
Next: With Function
From: Anja on 17 Apr 2010 10:34
Hi,
I have a small database that keeps track of my mileage.
Every trip is recorded in one table with date, start odometer, end odometer
and a short description.

When I run the query that consolidate it into daily figures, the total miles
is calculated, NP - However I can not seem to combine the short into one
longer description. Most days there are more than one trip.
The descriptions to combine would be: "home to gas station and back home"
with home to aunt polly and back home" It would be fine if the combined
description is:" home to gas station and back home, home to aunt polly and
back home"

The problem is that it is the same field(column) but on two different records

Any assistance would be greatly appreciated
From: Ken Snell on 17 Apr 2010 12:00
You can use a function that will combine the values from the different
fields, using the date field to be the "key" that links the records
together; you then incorporate the function in your query that consolidates
the daily figures.

ConcatenateFieldValues
http://www.accessmvp.com/KDSnell/VBA_Functions.htm#ConcatFlds

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"Anja" <Anja(a)discussions.microsoft.com> wrote in message
news:053EF05E-DCBA-484F-8B15-B8DDB9232314(a)microsoft.com...
> Hi,
> I have a small database that keeps track of my mileage.
> Every trip is recorded in one table with date, start odometer, end
> odometer
> and a short description.
>
> When I run the query that consolidate it into daily figures, the total
> miles
> is calculated, NP - However I can not seem to combine the short into one
> longer description. Most days there are more than one trip.
> The descriptions to combine would be: "home to gas station and back home"
> with home to aunt polly and back home" It would be fine if the combined
> description is:" home to gas station and back home, home to aunt polly and
> back home"
>
> The problem is that it is the same field(column) but on two different
> records
>
> Any assistance would be greatly appreciated


From: John W. Vinson on 17 Apr 2010 12:15
On Sat, 17 Apr 2010 07:34:01 -0700, Anja <Anja(a)discussions.microsoft.com>
wrote:

>When I run the query that consolidate it into daily figures, the total miles
>is calculated, NP - However I can not seem to combine the short into one
>longer description. Most days there are more than one trip.
>The descriptions to combine would be: "home to gas station and back home"
>with home to aunt polly and back home" It would be fine if the combined
>description is:" home to gas station and back home, home to aunt polly and
>back home"

You'll need a little easy VBA code to do this. There's a sample at

http://www.mvps.org/access/modules/mdl0004.htm
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Grouping reports and subreports
Next: With Function