|
| 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
January 15th, 2010, 02:23 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Formatting Help - Query Result for "Range"
Team - I really need a suggestion.
I am importing the following query result into an excel sheet (that is the media the customer wants). Below is sample of what is occurring - I can not find how to format PaidAmtRangeInDollars so I don't have to move several ranges into the correct place. In the below Current Result I would have to cut and past 500 - 1000 below the 0-500. I have several of these requests and just can not accomplish this by the deadline. Current Result SV Session MTH PaidAmtRangeInDollars Widgets CH 1 092009 0 - 500 223 CH 1 092009 1000 - 1500 156 CH 1 092009 1500 - 2000 62 CH 1 092009 2000 - 2500 21 CH 1 092009 2500 - 3000 18 CH 1 092009 3000 - 3500 8 CH 1 092009 3500 - 4000 1 CH 1 092009 4500 - 5000 1 CH 1 092009 500 - 1000 279 What I am using to get the "range": PaidAmtRangeInDollars: Int(([TotalReimbursed])/500)*500 & " - " & Int((([TotalReimbursed])/500)+1)*500 Complete SQL: SELECT tblStep001A_AmtPdChCgStep2.SV, tblStep001A_AmtPdChCgStep2.Session, tblStep001A_AmtPdChCgStep2.MTH, Int(([TotalReimbursed])/500)*500 & " - " & Int((([TotalReimbursed])/500)+1)*500 AS PaidAmtRangeInDollars, Count(tblStep001A_AmtPdChCgStep2.PROV_ID) AS Widgets INTO tblStep001BAmtPdCHCnt FROM tblStep001A_AmtPdChCgStep2 GROUP BY tblStep001A_AmtPdChCgStep2.SV, tblStep001A_AmtPdChCgStep2.Session, tblStep001A_AmtPdChCgStep2.MTH, Int(([TotalReimbursed])/500)*500 & " - " & Int((([TotalReimbursed])/500)+1)*500 HAVING (((tblStep001A_AmtPdChCgStep2.SV)="CH")); Needed Result SV Session MTH PaidAmtRangeInDollars Widgets CH 1 092009 0 - 500 223 CH 1 092009 500 - 1000 279 CH 1 092009 1000 - 1500 156 CH 1 092009 1500 - 2000 62 CH 1 092009 2000 - 2500 21 CH 1 092009 2500 - 3000 18 CH 1 092009 3000 - 3500 8 CH 1 092009 3500 - 4000 1 CH 1 092009 4500 - 5000 1 Thanks so much in advance I really need an idea. 
|
|
#2
January 15th, 2010, 03:38 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Formatting Help - Query Result for "Range"
Because you are concatenating the values the export is read as text. Thus,
the '5' of 500 is greater than the '4' of 4500. While keeping your concatenation field, also set a numeric field for the lower bound of the range and sort ascending - that should export the data as you want it. HTH "LisaInAz" wrote: Team - I really need a suggestion. I am importing the following query result into an excel sheet (that is the media the customer wants). Below is sample of what is occurring - I can not find how to format PaidAmtRangeInDollars so I don't have to move several ranges into the correct place. In the below Current Result I would have to cut and past 500 - 1000 below the 0-500. I have several of these requests and just can not accomplish this by the deadline. Current Result SV Session MTH PaidAmtRangeInDollars Widgets CH 1 092009 0 - 500 223 CH 1 092009 1000 - 1500 156 CH 1 092009 1500 - 2000 62 CH 1 092009 2000 - 2500 21 CH 1 092009 2500 - 3000 18 CH 1 092009 3000 - 3500 8 CH 1 092009 3500 - 4000 1 CH 1 092009 4500 - 5000 1 CH 1 092009 500 - 1000 279 What I am using to get the "range": PaidAmtRangeInDollars: Int(([TotalReimbursed])/500)*500 & " - " & Int((([TotalReimbursed])/500)+1)*500 Complete SQL: SELECT tblStep001A_AmtPdChCgStep2.SV, tblStep001A_AmtPdChCgStep2.Session, tblStep001A_AmtPdChCgStep2.MTH, Int(([TotalReimbursed])/500)*500 & " - " & Int((([TotalReimbursed])/500)+1)*500 AS PaidAmtRangeInDollars, Count(tblStep001A_AmtPdChCgStep2.PROV_ID) AS Widgets INTO tblStep001BAmtPdCHCnt FROM tblStep001A_AmtPdChCgStep2 GROUP BY tblStep001A_AmtPdChCgStep2.SV, tblStep001A_AmtPdChCgStep2.Session, tblStep001A_AmtPdChCgStep2.MTH, Int(([TotalReimbursed])/500)*500 & " - " & Int((([TotalReimbursed])/500)+1)*500 HAVING (((tblStep001A_AmtPdChCgStep2.SV)="CH")); Needed Result SV Session MTH PaidAmtRangeInDollars Widgets CH 1 092009 0 - 500 223 CH 1 092009 500 - 1000 279 CH 1 092009 1000 - 1500 156 CH 1 092009 1500 - 2000 62 CH 1 092009 2000 - 2500 21 CH 1 092009 2500 - 3000 18 CH 1 092009 3000 - 3500 8 CH 1 092009 3500 - 4000 1 CH 1 092009 4500 - 5000 1 Thanks so much in advance I really need an idea.
|
|
#3
January 15th, 2010, 03:55 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Formatting Help - Query Result for "Range"
Thank You
But I amnot quite understanding your suggestion - I am not sure where to set as numeric field. "Pendragon" wrote: Because you are concatenating the values the export is read as text. Thus, the '5' of 500 is greater than the '4' of 4500. While keeping your concatenation field, also set a numeric field for the lower bound of the range and sort ascending - that should export the data as you want it. HTH "LisaInAz" wrote: Team - I really need a suggestion. I am importing the following query result into an excel sheet (that is the media the customer wants). Below is sample of what is occurring - I can not find how to format PaidAmtRangeInDollars so I don't have to move several ranges into the correct place. In the below Current Result I would have to cut and past 500 - 1000 below the 0-500. I have several of these requests and just can not accomplish this by the deadline. Current Result SV Session MTH PaidAmtRangeInDollars Widgets CH 1 092009 0 - 500 223 CH 1 092009 1000 - 1500 156 CH 1 092009 1500 - 2000 62 CH 1 092009 2000 - 2500 21 CH 1 092009 2500 - 3000 18 CH 1 092009 3000 - 3500 8 CH 1 092009 3500 - 4000 1 CH 1 092009 4500 - 5000 1 CH 1 092009 500 - 1000 279 What I am using to get the "range": PaidAmtRangeInDollars: Int(([TotalReimbursed])/500)*500 & " - " & Int((([TotalReimbursed])/500)+1)*500 Complete SQL: SELECT tblStep001A_AmtPdChCgStep2.SV, tblStep001A_AmtPdChCgStep2.Session, tblStep001A_AmtPdChCgStep2.MTH, Int(([TotalReimbursed])/500)*500 & " - " & Int((([TotalReimbursed])/500)+1)*500 AS PaidAmtRangeInDollars, Count(tblStep001A_AmtPdChCgStep2.PROV_ID) AS Widgets INTO tblStep001BAmtPdCHCnt FROM tblStep001A_AmtPdChCgStep2 GROUP BY tblStep001A_AmtPdChCgStep2.SV, tblStep001A_AmtPdChCgStep2.Session, tblStep001A_AmtPdChCgStep2.MTH, Int(([TotalReimbursed])/500)*500 & " - " & Int((([TotalReimbursed])/500)+1)*500 HAVING (((tblStep001A_AmtPdChCgStep2.SV)="CH")); Needed Result SV Session MTH PaidAmtRangeInDollars Widgets CH 1 092009 0 - 500 223 CH 1 092009 500 - 1000 279 CH 1 092009 1000 - 1500 156 CH 1 092009 1500 - 2000 62 CH 1 092009 2000 - 2500 21 CH 1 092009 2500 - 3000 18 CH 1 092009 3000 - 3500 8 CH 1 092009 3500 - 4000 1 CH 1 092009 4500 - 5000 1 Thanks so much in advance I really need an idea.
|
|
#4
January 15th, 2010, 10:08 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Formatting Help - Query Result for "Range"
Add another field and populate the field with only the lower range of your
PaidAmtRange field. When you add the field, make sure the field type is numeric. You can do this directly in your query as: LowerRange: Val(Left$([PaidAmtRangeInDollars], InStr([PaidAmtRangeInDollars], "-") - 1)) You can also add the field in your underlying table, selecting Number as the field type. You would then have to manually enter in the data. Add the field to the query and sort Ascending. Either way you add the field, your query result should look something like this for the field: LowerRange 500 1000 1500 etc And so your export so sort properly. If it doesn't sort, you would at least have the field available in Excel by which you could sort. "LisaInAz" wrote: Thank You But I amnot quite understanding your suggestion - I am not sure where to set as numeric field. "Pendragon" wrote: Because you are concatenating the values the export is read as text. Thus, the '5' of 500 is greater than the '4' of 4500. While keeping your concatenation field, also set a numeric field for the lower bound of the range and sort ascending - that should export the data as you want it. HTH "LisaInAz" wrote: Team - I really need a suggestion. I am importing the following query result into an excel sheet (that is the media the customer wants). Below is sample of what is occurring - I can not find how to format PaidAmtRangeInDollars so I don't have to move several ranges into the correct place. In the below Current Result I would have to cut and past 500 - 1000 below the 0-500. I have several of these requests and just can not accomplish this by the deadline. Current Result SV Session MTH PaidAmtRangeInDollars Widgets CH 1 092009 0 - 500 223 CH 1 092009 1000 - 1500 156 CH 1 092009 1500 - 2000 62 CH 1 092009 2000 - 2500 21 CH 1 092009 2500 - 3000 18 CH 1 092009 3000 - 3500 8 CH 1 092009 3500 - 4000 1 CH 1 092009 4500 - 5000 1 CH 1 092009 500 - 1000 279 What I am using to get the "range": PaidAmtRangeInDollars: Int(([TotalReimbursed])/500)*500 & " - " & Int((([TotalReimbursed])/500)+1)*500 Complete SQL: SELECT tblStep001A_AmtPdChCgStep2.SV, tblStep001A_AmtPdChCgStep2.Session, tblStep001A_AmtPdChCgStep2.MTH, Int(([TotalReimbursed])/500)*500 & " - " & Int((([TotalReimbursed])/500)+1)*500 AS PaidAmtRangeInDollars, Count(tblStep001A_AmtPdChCgStep2.PROV_ID) AS Widgets INTO tblStep001BAmtPdCHCnt FROM tblStep001A_AmtPdChCgStep2 GROUP BY tblStep001A_AmtPdChCgStep2.SV, tblStep001A_AmtPdChCgStep2.Session, tblStep001A_AmtPdChCgStep2.MTH, Int(([TotalReimbursed])/500)*500 & " - " & Int((([TotalReimbursed])/500)+1)*500 HAVING (((tblStep001A_AmtPdChCgStep2.SV)="CH")); Needed Result SV Session MTH PaidAmtRangeInDollars Widgets CH 1 092009 0 - 500 223 CH 1 092009 500 - 1000 279 CH 1 092009 1000 - 1500 156 CH 1 092009 1500 - 2000 62 CH 1 092009 2000 - 2500 21 CH 1 092009 2500 - 3000 18 CH 1 092009 3000 - 3500 8 CH 1 092009 3500 - 4000 1 CH 1 092009 4500 - 5000 1 Thanks so much in advance I really need an idea.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
