If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Group by using IF then??
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 |
#2
|
|||
|
|||
Group by using IF then??
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" wrote: babs wrote: 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. 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. -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Group by using IF then??
babs wrote:
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. 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. -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Group by using IF then??
babs wrote:
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 #. 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) = "*" -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|