Formatting Help - Query Result for "Range"

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.
 
P

Pendragon

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
 
L

LisaInAz

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...
 
P

Pendragon

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...
 

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

how to solve the following in excel 1
Running Sum 9
function range 1
data series color - conditional formatting 2
Help with query, possible crosstab 8
function range 2
Function Range Lookup 2
Formula help 1

Top