By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,620 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,620 IT Pros & Developers. It's quick & easy.

Grouping Problem

P: n/a
I have been racking my brain on this one, maybe someone might be able
to help me out. What I want to do is be able to group the result of
this query into a report. Below is a condensed sample of the results
that I need to be grouped.

CallID Date Membership Car Service
------ ---- ---------- --- -------
100001 06/22/05 123456 Ford Boost
100001 06/22/05 123456 Ford Tow
100002 06/22/05 987654 Honda Flat Tire
100003 06/22/05 456789 Nissan Unlock
100003 06/22/05 456789 Nissan Boost
100003 06/22/05 456789 Nissan Tow

This is what I am trying to get the report to look like:

CallID Date Membership Car Service
------ ---- ---------- --- -------
100001 06/22/05 123456 Ford Boost, Tow
100002 06/22/05 987654 Honda Flat Tire
100003 06/22/05 456789 Nissan Unlock, Boost, Tow

I want the report to be grouped by the CallID, but I want it to display
the services that were provided to the vehicle. If anyone can help that
would make my day! Thanks in advanced!

Tony

Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Anthony Hendrata wrote:
I have been racking my brain on this one, maybe someone might be able
to help me out. What I want to do is be able to group the result of
this query into a report. Below is a condensed sample of the results
that I need to be grouped.

CallID Date Membership Car Service
------ ---- ---------- --- -------
100001 06/22/05 123456 Ford Boost
100001 06/22/05 123456 Ford Tow
100002 06/22/05 987654 Honda Flat Tire
100003 06/22/05 456789 Nissan Unlock
100003 06/22/05 456789 Nissan Boost
100003 06/22/05 456789 Nissan Tow

This is what I am trying to get the report to look like:

CallID Date Membership Car Service
------ ---- ---------- --- -------
100001 06/22/05 123456 Ford Boost, Tow
100002 06/22/05 987654 Honda Flat Tire
100003 06/22/05 456789 Nissan Unlock, Boost, Tow

I want the report to be grouped by the CallID, but I want it to display
the services that were provided to the vehicle. If anyone can help that
would make my day! Thanks in advanced!

Tony


This is tougher than it looks, especially if the list of Services can
be long. See if the following thread gives you any ideas:

http://groups-beta.google.com/group/...555f42cdf343f1

James A. Fortune

Nov 13 '05 #2

P: n/a
Thanks for the reply, the solution is similair in manner but I have a
list that is 36 long. The solution that was mentioned showed that there
was about 5 different IP's. Is there anyway to have it built so it is
dynamic?

Tony

Nov 13 '05 #3

P: n/a
Anthony Hendrata wrote:
Thanks for the reply, the solution is similair in manner but I have a
list that is 36 long. The solution that was mentioned showed that there
was about 5 different IP's. Is there anyway to have it built so it is
dynamic?

Tony


Not that I can think of using SQL. The IP problem handled 'up to' five
IP's, so maybe set up the SQL to handle up to 40 services. Then you'd
have to find an elegant way to get the commas in. I'm not sure that
many services would even fit on the report :-). If you go the VBA
route you can handle any number of services separated by commas but
fitting it on the report could be a problem. Maybe the Mid() function
could be used to split the services list into three or more wide boxes
in the Detail section of the report. Splitting the services list after
a space would be even better. Give something a try and post back if
you need any assistance.

James A. Fortune

Nov 13 '05 #4

P: n/a
<ji********@compumarc.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Anthony Hendrata wrote:
I have been racking my brain on this one, maybe someone might be able
to help me out. What I want to do is be able to group the result of
this query into a report. Below is a condensed sample of the results
that I need to be grouped.

CallID Date Membership Car Service
------ ---- ---------- --- -------
100001 06/22/05 123456 Ford Boost
100001 06/22/05 123456 Ford Tow
100002 06/22/05 987654 Honda Flat Tire
100003 06/22/05 456789 Nissan Unlock
100003 06/22/05 456789 Nissan Boost
100003 06/22/05 456789 Nissan Tow

This is what I am trying to get the report to look like:

CallID Date Membership Car Service
------ ---- ---------- --- -------
100001 06/22/05 123456 Ford Boost, Tow
100002 06/22/05 987654 Honda Flat Tire
100003 06/22/05 456789 Nissan Unlock, Boost, Tow

I want the report to be grouped by the CallID, but I want it to display
the services that were provided to the vehicle. If anyone can help that
would make my day! Thanks in advanced!

Tony
This is tougher than it looks, especially if the list of Services can
be long. See if the following thread gives you any ideas:

http://groups-beta.google.com/group/...555f42cdf343f1
James A. Fortune


Tony,
Here's what I've done in the past. I first create a temporary table where I
import all of the records and add another field. The new field is populated
with an integer representing how many "services" that member has received. I
perform this step with a VBA procedure. The procedure opens a recordset and
cycles through all of the records assigning the integers. The results would
look like this:

CallID Date Membership Car Service Newfield
------ ---- ---------- --- ------- ------
100001 06/22/05 123456 Ford Boost 1
100001 06/22/05 123456 Ford Tow 2
100002 06/22/05 987654 Honda Flat Tire 1
100003 06/22/05 456789 Nissan Unlock 1
100003 06/22/05 456789 Nissan Boost 2
100003 06/22/05 456789 Nissan Tow 3
Now create a crosstab query with [Newfield] as the columnheading.

Good Luck,
Fred Zuckerman
Nov 13 '05 #5

P: n/a
Hey thanks for the direction on this problem of mine...I am going to
plug away at this.

Nov 13 '05 #6

P: n/a
jv
What I've done in the past is to create a public function that
concatenate the services for a CallID and then call that function from
the grouped query that does not include the field service as a field.

Air Code for DAO:
Public Function ConCatServices (lngCallID as long) as string
dim strResult as string
dim db as database, rs as dao.recorset
set db=CurrentDB
set rs=db.OpenRecordset("SELECT Service FROM TableName WHERE CallID=" &
lngCallID)
do while not rs.eof
strResult =strResult & rs!Service
rs.movenext
if not rs.eof then strResult =strResult & ", "
loop
ConCatServices =strResult
end function

Nov 13 '05 #7

P: n/a
jv wrote:
What I've done in the past is to create a public function that
concatenate the services for a CallID and then call that function from
the grouped query that does not include the field service as a field.

Air Code for DAO:
Public Function ConCatServices (lngCallID as long) as string
dim strResult as string
dim db as database, rs as dao.recorset
set db=CurrentDB
set rs=db.OpenRecordset("SELECT Service FROM TableName WHERE CallID=" &
lngCallID)
do while not rs.eof
strResult =strResult & rs!Service
rs.movenext
if not rs.eof then strResult =strResult & ", "
loop
ConCatServices =strResult
end function


I love her mind. It's a great plan.

James A. Fortune

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.