Formatting Help - Query Result for "Range"

  • Thread starter Thread starter LisaInAz
  • Start date Start date
L

LisaInAz

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.
 
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
 
Thank You
But I amnot quite understanding your suggestion - I am not sure where to set
as numeric field.

Pendragon said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
Thank You
But I amnot quite understanding your suggestion - I am not sure where to set
as numeric field.

Pendragon said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top