Group by using IF then??

B

babs

Iif[ticket]=[ticket] & *, ticket, comment

I am trying to put code into the groupby of a report I am creating. right
now it is grouping by comment. However when the ticket it number is similiar
meaning if for example 1234 and 1234*. The * is the only situation I am
working with- I would like it to group by ticket # only if they are similar
and otherwise group it by comment.

Any ideas,
Barb
 
B

babs

I am grouping by clientid, sortin by date, grouping by Comment(ex. clear
linestone etc.), then sorting by ticket #(ex. 1019879). It works fine for
first example form report. See other example though with * after ticket#


JWPETW 3" CLEAR LINESTONE TO RT 83 & ROLLINS RD ROUND LAKE
2/27/2007 1019879 288 0 22.79 $13.28 $302.65
2/27/2007 1019880 292 0 18.71 $13.28 $248.47
2/27/2007 1019900 288 0 22.96 $13.28 $304.91
2/27/2007 1019901 292 0 20.36 $13.28 $270.38
2/27/2007 1019921 288 0 20.75 $13.28 $275.56
2/27/2007 1019923 292 0 20.92 $13.28 $277.82
JWPETW 1" CLEAR (CA-7) LIMESTONE TO RT 83 & ROLLINS RD ROUND LAKE
2/28/2007 1020066 393 0 20.08 $14.66 $294.37



FB HL FROM KENOSHA TO CRESTWOOD (120 MI) W/ STOPOVER
2/28/2007 15839841/15839751 TIMCON 0 1.00 $437.00 $437.00
FB HL HAMMOND TO PEOTONE (56 MI)
2/28/2007 1584092 283F 0 1.00 $250.00 $250.00
FB HL KENOSHA TO BROOKFIELD (47 MI)
2/28/2007 15844761 286 0 1.00 $250.00 $250.00
FB HL KENOSHA TO GENEVA (73 MI)
2/28/2007 15840671 286 0 1.00 $250.00 $250.00
FB HL KENOSHA TO SHEBOYGAN (91 MI)
2/28/2007 15844791 DIRECT 0 1.00 $342.00 $342.00
FB HL WEST CHICAGO TO CHICAGO (32 MI)
2/28/2007 1585000 TIMCON 0 1.00 $250.00 $250.00
FUEL SURCHARGE @ $ 0.27
2/28/2007 15839841/15839751* TIMCON FS 0 120.00 $0.27 $32.40
2/28/2007 15840671* 286FS 0 73.00 $0.27 $19.71
2/28/2007 1584092* 283FS 0 56.00 $0.27 $15.12
2/28/2007 1584277* 355FS 0 122.00 $0.27 $32.94
2/28/2007 15844761* 286FS 0 47.00 $0.27 $12.69
2/28/2007 15844791* DIRECT FS 0 91.00 $0.27 $24.57
2/28/2007 1585000* TIMCON FS 0 32.00 $0.27 $8.64

Would like to keep original grouping and sorting EXCEPT for when ticket# has
an * at end then want it to group those records by ticket # and not by
comment since comment is FuelSurcharge and different from other similar
ticket #.

Any ideas,
Thanks,
Barb


Marshall Barton said:
babs said:
Iif[ticket]=[ticket] & *, ticket, comment

I am trying to put code into the groupby of a report I am creating. right
now it is grouping by comment. However when the ticket it number is similiar
meaning if for example 1234 and 1234*. The * is the only situation I am
working with- I would like it to group by ticket # only if they are similar
and otherwise group it by comment.
Click to expand...


Sorry, Barb, but that doesn't really make much sense. If a
report is sorted by comment, where would a ticket number fit
into the list of comments???

You could goup all the similar tickets under a single
comment by using two groups.

Comment
=IIf(Right(Ticket,1)="*", Left(Ticket,Len(Ticket)-1),Ticket)

If that isn't what you are trying to do, maybe an example
that demonstrates your problem would help clarify the
problem.
Click to expand...
 
M

Marshall Barton

babs said:
Iif[ticket]=[ticket] & *, ticket, comment

I am trying to put code into the groupby of a report I am creating. right
now it is grouping by comment. However when the ticket it number is similiar
meaning if for example 1234 and 1234*. The * is the only situation I am
working with- I would like it to group by ticket # only if they are similar
and otherwise group it by comment.
Click to expand...


Sorry, Barb, but that doesn't really make much sense. If a
report is sorted by comment, where would a ticket number fit
into the list of comments???

You could goup all the similar tickets under a single
comment by using two groups.

Comment
=IIf(Right(Ticket,1)="*", Left(Ticket,Len(Ticket)-1),Ticket)

If that isn't what you are trying to do, maybe an example
that demonstrates your problem would help clarify the
problem.
 
M

Marshall Barton

babs said:
I am grouping by clientid, sortin by date, grouping by Comment(ex. clear
linestone etc.), then sorting by ticket #(ex. 1019879). It works fine for
first example form report. See other example though with * after ticket#


JWPETW 3" CLEAR LINESTONE TO RT 83 & ROLLINS RD ROUND LAKE
2/27/2007 1019879 288 0 22.79 $13.28 $302.65
2/27/2007 1019880 292 0 18.71 $13.28 $248.47
2/27/2007 1019900 288 0 22.96 $13.28 $304.91
2/27/2007 1019901 292 0 20.36 $13.28 $270.38
2/27/2007 1019921 288 0 20.75 $13.28 $275.56
2/27/2007 1019923 292 0 20.92 $13.28 $277.82
JWPETW 1" CLEAR (CA-7) LIMESTONE TO RT 83 & ROLLINS RD ROUND LAKE
2/28/2007 1020066 393 0 20.08 $14.66 $294.37

FB HL FROM KENOSHA TO CRESTWOOD (120 MI) W/ STOPOVER
2/28/2007 15839841/15839751 TIMCON 0 1.00 $437.00 $437.00
FB HL HAMMOND TO PEOTONE (56 MI)
2/28/2007 1584092 283F 0 1.00 $250.00 $250.00
FB HL KENOSHA TO BROOKFIELD (47 MI)
2/28/2007 15844761 286 0 1.00 $250.00 $250.00
FB HL KENOSHA TO GENEVA (73 MI)
2/28/2007 15840671 286 0 1.00 $250.00 $250.00
FB HL KENOSHA TO SHEBOYGAN (91 MI)
2/28/2007 15844791 DIRECT 0 1.00 $342.00 $342.00
FB HL WEST CHICAGO TO CHICAGO (32 MI)
2/28/2007 1585000 TIMCON 0 1.00 $250.00 $250.00
FUEL SURCHARGE @ $ 0.27
2/28/2007 15839841/15839751* TIMCON FS 0 120.00 $0.27 $32.40
2/28/2007 15840671* 286FS 0 73.00 $0.27 $19.71
2/28/2007 1584092* 283FS 0 56.00 $0.27 $15.12
2/28/2007 1584277* 355FS 0 122.00 $0.27 $32.94
2/28/2007 15844761* 286FS 0 47.00 $0.27 $12.69
2/28/2007 15844791* DIRECT FS 0 91.00 $0.27 $24.57
2/28/2007 1585000* TIMCON FS 0 32.00 $0.27 $8.64

Would like to keep original grouping and sorting EXCEPT for when ticket# has
an * at end then want it to group those records by ticket # and not by
comment since comment is FuelSurcharge and different from other similar
ticket #.
Click to expand...


I think(?) I see what you want. You want to see the tickets
with a * placed under the comment that has the same ticket
without the *

If that's what you're looking for, you can not do that in
the report. Instead, you need to reorganize the records in
the report's record source query so the tickets with an *
has the same comment as the ones without the *

Let's try something like:

SELECT comment, ticket, fa, fb, . . .
FROM thetable
WHERE Right(ticket, 1) <> "*"
UNION ALL
SELECT X.comment, T.ticket, T.fa, T.fb, . . .
FROM thetable As T INNER JOIN thetable As X
ON Left(T.ticket, Len(T.ticket) - 1) = X.ticket
WHERE Right(T.ticket, 1) = "*"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to create a report that will list unmatched records by Ticket Primary Key 0
want to sort by ticket only if ticket# kind of matches 0
Using Microsoft Access as a Service Ticket Write-up program 0
Using an ActiveX control instead of multiple Access controls 0
Print Access report along with an outside checklist 4
Sorting by Ticket # 0
Look up one value and return multiple corresponding values 2
Populating main form after changing selection 0

Top