Category report

[Guest]

Hi

I am looking for the following

My Table name is: Orders
Fields
SL # (Auto number)
Date (Date/Time)
Client Name (Text)
Item-1-Value(numbers)
Item-2-Value(Numbers)
Item-3-Value(numbers)
Item-4-Value(Numbers)

I need a report to show the number of orders received in a given date range
in the following category
Category for each item. These categories are fixed
<=500,"USD 0 To 500"
<=1000,"USD 501 To 1,000"
<=2000,"USD 1,001 To 2,000"
<=4000,"USD 2,001 To 4,000"
<=10000,"USD 4,001 To 10,000"

=10001,"USD Over 10,001"

I’ve used a query to show this and I have used the following expression
Category: IIf([Item-1-Value]<=500,"USD 0 To
500",IIf([Item-1-Value]<=1000,"USD 501 To
1,000",IIf([Item-1-Value]<=2000,"USD 1,001 To
2,000",IIf([Item-1-Value]<=4000,"USD 2,001 To
4,000",IIf([Item-1-Value]<=10000,"USD 4,001 To
10,000",IIf([Item-1-Value]>=10001,"USD Over 10,001"))))))

Another problem in my query there zero values also available, because my
data is as follows
SL# Item-1 Item-2 Item-3
1 0 10 0
2 5 0 0
3 0 0 20

I need the number of orders > zero for all the items, I tried to give a
criteria in query grid, but it returned onl the records that are matching
each other. How can we do this report, please help

 

[Allen Browne]

Rather than mess with embedded IIf() statements, create a Category table
with fields:
CategoryID Number
MinValue Currency

Enter records:
CategoryID MinValue
1 0
2 501
3 1001
etc

You can now create a query that uses DLookup() to get the CategoryID:
CategoryLookup("CategoryID", "Category", [Amount] >= & "[MinValue])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am looking for the following

My Table name is: Orders
Fields
SL # (Auto number)
Date (Date/Time)
Client Name (Text)
Item-1-Value(numbers)
Item-2-Value(Numbers)
Item-3-Value(numbers)
Item-4-Value(Numbers)

I need a report to show the number of orders received in a given date
range
in the following category
Category for each item. These categories are fixed
<=500,"USD 0 To 500"
<=1000,"USD 501 To 1,000"
<=2000,"USD 1,001 To 2,000"
<=4000,"USD 2,001 To 4,000"
<=10000,"USD 4,001 To 10,000"

=10001,"USD Over 10,001"

I've used a query to show this and I have used the following expression
Category: IIf([Item-1-Value]<=500,"USD 0 To
500",IIf([Item-1-Value]<=1000,"USD 501 To
1,000",IIf([Item-1-Value]<=2000,"USD 1,001 To
2,000",IIf([Item-1-Value]<=4000,"USD 2,001 To
4,000",IIf([Item-1-Value]<=10000,"USD 4,001 To
10,000",IIf([Item-1-Value]>=10001,"USD Over 10,001"))))))

Another problem in my query there zero values also available, because my
data is as follows
SL# Item-1 Item-2 Item-3
1 0 10 0
2 5 0 0
3 0 0 20

I need the number of orders > zero for all the items, I tried to give a
criteria in query grid, but it returned onl the records that are matching
each other. How can we do this report, please help