Prev: Prevent Rounding of Double in Report
Next: Sizing problem with picture in BoundObject control
From: vfv on 16 Jun 2010 13:53
I have this table:
CustName TripID LocID
ABC 12 96
CBS 18 91
CSI 19 97
ABC
From: Salad on 16 Jun 2010 14:36
vfv wrote:

> I have this table:
> CustName TripID LocID
> ABC 12 96
> CBS 18 91
> CSI 19 97
> ABC

Cool!
From: Access Developer on 16 Jun 2010 17:06
So, do you also have a question? It's not apparent from your post that you
d.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"vfv" <vfvacct(a)gmail.com> wrote in message
news:5b6b0b4c-8c0c-4048-ad01-950d2b1a23b3(a)y4g2000yqy.googlegroups.com...
>I have this table:
> CustName TripID LocID
> ABC 12 96
> CBS 18 91
> CSI 19 97
> ABC


From: James A. Fortune on 16 Jun 2010 17:22
On Jun 16, 1:53 pm, vfv <vfva...(a)gmail.com> wrote:
> I have this table:
> CustName TripID LocID
> ABC             12     96
> CBS             18     91
> CSI               19     97
> ABC

This is a shot-in-the-dark :-).

A Trip can often involve multiple locations. Perhaps you have:

tblCustomerTrips
CTID CustName TripID LocID
1 ABC 12 96
2 CBS 18 91
3 CSI 19 97
4 ABC 12 98
....

You'd like the report to show:

CBS Calgary
CSI Miami :-)
ABC Washington D.C., Dogpatch

You also have:

tblTrips
TripID TripDescription
12 Characters and Characters
18 Horses and Hockey
19 Fun in the Sun

tblLocations
LocID City StateProvince
91 Calgary Alberta
96 Washington D.C. Null
97 Miami Florida
98 Dogpatch Null

qryCustomerTripCities:
SELECT CustName, TripDescription, City FROM (tblCustomerTrips INNER
JOIN tblTrips ON tblCustomerTrips.TripID = tblTrips.TripID) INNER JOIN
tblLocations ON tblCustomerTrips.LocID = tblLocations.LocID;

!qryCustomerTripCities:
CustName TripDescription City
ABC Characters and Characters Washington D.C.
CBS Horses and Hockey Calgary
CSI Fun in the Sun Miami
ABC Characters and Characters Dogpatch

Now you have the data in a format that might support appending a list
of cities to a string using a public function such as fConcatChild().
That function was made, I think, by an Access MVP and should be
discoverable by using Google (maybe Bing also) within this newsgroup.
Note that both the CustName and TripID have to match in order to
append the City so some modification to the function might be required
(I don't remember ever using fConcatChild(), so I can't say for
sure). Subqueries can also be used to cobble together the cities, but
having a Public function append the field values to a string
(accounting for the commas correctly) is much tidier. I usually write
a function to do field concatenation as needed. The Public function
can be used in the Details section where you want the concatenated
list of cities to show up. The CustName and TripID used as parameters
in the function should be obtained from the controls in the group
headers of the report.

James A. Fortune
CDMAPoster(a)FortuneJames.com
 | 
Pages: 1
Prev: Prevent Rounding of Double in Report
Next: Sizing problem with picture in BoundObject control