Sort prices in categories
CRoche2464
Access 97, Win95 :-(
I have a report that sorts items sold.
The qryItemDetails is the source
Typically, it groups on "Category" as a header
Then in the detail section the items purchased are listed for each category.
I have a need to sort by Item price referred to as "Sell", instead of
"Category"
And list the items that fit into each "Sell" designation
The structure is as follows:
50 & Under: ([sell]>0 And [sell]<=0.5
100 & Under: ([sell]>0.5 And [sell]<=1
150 & Under: ([sell]>1 And [sell]<=1.5
200 & Under: ([sell]>1.5 And [sell]<=2
250 & Under: ([sell]>2 And [sell]<=2.5
300 & Under: ([sell]>2.5 And [sell]<=3
400 & Under: ([sell]>3 And [sell]<=4
500 & Under: ([sell]>4 And [sell]<=5
Over 500: ([sell]>5
None of these are fields in the table or query
Thanks for any help
Charlie
John Vinson
>I have a need to sort by Item price referred to as "Sell", instead of
>"Category"
>And list the items that fit into each "Sell" designation
>The structure is as follows:
>
>50 & Under: ([sell]>0 And [sell]<=0.5
>100 & Under: ([sell]>0.5 And [sell]<=1
>150 & Under: ([sell]>1 And [sell]<=1.5
>200 & Under: ([sell]>1.5 And [sell]<=2
>250 & Under: ([sell]>2 And [sell]<=2.5
>300 & Under: ([sell]>2.5 And [sell]<=3
>400 & Under: ([sell]>3 And [sell]<=4
>500 & Under: ([sell]>4 And [sell]<=5
>Over 500: ([sell]>5
>
>None of these are fields in the table or query
One way you can do this is to *create* a table, Ranges, with three
fields: [Category], [Low] and [High], with just these values. Join
this table to the query containing [Sell] using a "Non Equi Join" -
first in the grid, join [Sell] to [Low]; then switch to SQL view and
change
ON [yourtable].[Sell] = [Ranges].[Low]
to
ON [yourtable].[Sell] > [Ranges].[Low] AND [yourtable].[Sell] <=
[Ranges].[High]
You'll need to have the last record of Ranges
"Over 500:", 5, 65535
or some other extremely large number that will never occur.
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
Brett Collings
>Hi Folks,
>Access 97, Win95 :-(
>I have a report that sorts items sold.
>The qryItemDetails is the source
>Typically, it groups on "Category" as a header
>Then in the detail section the items purchased are listed for each category.
>I have a need to sort by Item price referred to as "Sell", instead of
>"Category"
>And list the items that fit into each "Sell" designation
Charlie, it's in the "Grouping and Sorting" window that you set your
Category Group in. Just add another line
Cheers,
Brett
Office XP Beta 429338
CRoche2464
Thanks for the help
I think I did a poor job trying to explain my table and query
None of the structure to sell is in a table or query. It was the left over of
my attempt to write and IF THEN ELSE statement that would not work.
If you could explain how I should do this I will give it a go
Your quote:
You'll need to have the last record of Ranges
"Over 500:", 5, 65535
or some other extremely large number that will never occur.
End of your quote
I am sorry for the confusion. I understand all except what should be in the
sell query
Thank You,
Charlie
John Vinson
>John,
>Thanks for the help
>I think I did a poor job trying to explain my table and query
Well... create such a table. It's easy.
>None of the structure to sell is in a table or query. It was the left over of
>my attempt to write and IF THEN ELSE statement that would not work.
>If you could explain how I should do this I will give it a go
>Your quote:
>
>You'll need to have the last record of Ranges
>
>"Over 500:", 5, 65535
>
>or some other extremely large number that will never occur.
>
>End of your quote
>I am sorry for the confusion. I understand all except what should be in the
>sell query
>Thank You,
>Charlie
Just what I told you (after you create the table).
CRoche2464
Sorry I just can't get my brain around it
Could you tell me if this works in a module to call from a control?
Am I missing anything
I know it is probably more time consuming and
probably not good on the resources either
Thanks for assisting a novice
Charlie
Option Compare Database
Option Explicit
Public Function fncPriceCategory() As Variant
If [Sell] > 0 And [Sell] <= 0.5 Then
fncPriceCategory = ".50 & Under"
ElseIf [Sell] > 0.5 And [Sell] <= 1 Then
fncPriceCategory = "1.00 & Under"
ElseIf [Sell] > 1 And [Sell] <= 1.5 Then
fncPriceCategory = "1.50 & Under"
ElseIf [Sell] > 1.5 And [Sell] <= 2 Then
fncPriceCategory = "2.00 & Under"
ElseIf [Sell] > 2 And [Sell] <= 2.5 Then
fncPriceCategory = "2.50 & Under"
ElseIf [Sell] > 2.5 And [Sell] <= 3 Then
fncPriceCategory = "3.00 & Under"
ElseIf [Sell] > 3 And [Sell] <= 3.5 Then
fncPriceCategory = "3.50 & Under"
ElseIf [Sell] > 3.5 And [Sell] <= 4 Then
fncPriceCategory = "4.00 & Under"
ElseIf [Sell] > 4 And [Sell] <= 4.5 Then
fncPriceCategory = "4.50 & Under"
ElseIf [Sell] > 4.5 And [Sell] <= 5 Then
fncPriceCategory = "5.00 & Under"
Else
fncPriceCategory = "Over 5.00"
End If
End Function
John Vinson
>John,
>Sorry I just can't get my brain around it
>Could you tell me if this works in a module to call from a control?
>Am I missing anything
>I know it is probably more time consuming and
>probably not good on the resources either
>
>Thanks for assisting a novice
Ok. I gave you my best advice. You're either ignoring it or choosing
to reject it without saying why. Sigh...
The function that you post should work, IF you change [Sell] to
Me!Sell and put the function in the Form's module; the term [Sell]
means nothing to VBA. You can set the Control Source of a textbox on
the Form to
=fncPriceCategory()
to call the function.
You might want to revise the function a bit to pass the Sell value as
an argument:
Public Function fncPriceCategory(Sell as Currency) As String
and change all the [Sell] to just Sell, without the brackets. You
would then call it passing the value that you want to categorize:
=fncPriceCategory([Sell])
CRoche2464
Thanks a million or two!!!!!!
Please don't feel like I quit on the first
recommendation you gave me.
I was unable to get it to work as you wanted
I was receiving #Name error at the end of 3 hours
of messing with it.
I didn't want to allow frustration to set in for either of us
Thank again
Charlotte