Group by using IF then??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

babs - 12 Mar 2007 18:13 GMT

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

Reply to this Message

Marshall Barton - 12 Mar 2007 21:46 GMT

>Iif[ticket]=[ticket] & *, ticket, comment
>
[quoted text clipped - 3 lines]
>working with- I would like it to group by ticket # only if they are similar
>and otherwise group it by comment.

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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

babs - 12 Mar 2007 22:20 GMT

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

> >Iif[ticket]=[ticket] & *, ticket, comment
> >
[quoted text clipped - 17 lines]
> that demonstrates your problem would help clarify the
> problem.

Reply to this Message

Marshall Barton - 13 Mar 2007 00:31 GMT

>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
[quoted text clipped - 35 lines]
>comment since comment is FuelSurcharge and different from other similar
>ticket #.

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) = "*"

Signature

Marsh
MVP [MS Access]

Reply to this Message