creating buckets / tiers in query 1

[was1]

I'm trying to bill our client based on widgets. They are charged x amt based on the 1st 25,000 widgets, y amt based on 25,001- 50,000, z amt based on anything over 50,000

I have around 75,000 records and would like to group them into 25,000 increments.

I'm a basic access user and have no vb skills.

thanks for your time.

 

[dhookom]

Create a table of buckets with min and max amounts and the amount. Then you can grab the amount in a query or code.

SELECT tblSales.*, Amt, Qty * Amt as ChargeAmt
FROM tblSales, tblBuckets
WHERE Qty Between MinAmt and MaxAmt;

Duane
Hook'D on Access
MS Access MVP

 

[was1]

Sorry, I'm a bit confused its probably my fault for not explaining correctly. I want to group my 100,000 records into 4 groups of 25,000. Would it be easier to add a counter and then i can choose based on the counter? I dont know how to create a counter. I tried using the method in FAQ but it would continue to change each time i ran the query. i tried the one that would reset every 4 seconds. it worked except for 1 record which would not reset.

 

[PHV]

Do you have a PrimaryKey defined for your table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

Somthing like this?

=iif(qty < 25001, AmountDue = qty * Price!, 
iif(qty < 50001, AmountDue = (25000 * PriceA) + ((Qty - 25000) * PriceB, 
AmountDue = (25000 * PriceA) + (25000 * PriceB) + ((Qty - 50000) * PriceC)))))

Randy

 

[lespaul]

as duane said:

Create a table of buckets with min and max amounts and the amount

[tt]
tblBuckets
MIN MAX AMOUNT
0 25000 x
25001 50000 y
50001 75000 z
75000 999999 a[/tt]

Then you can grab the amount in a query or code.

Leslie

Have you met Hardy Heron?

 

[cmmrfrds]

Access has a Partition function that works well for increments that are the same size like your's are.

Here is an example query against the Northwind database to see how it works. This partitons 0-50, 51-100, etc.., starting at 0 and going up to 500.

SELECT DISTINCTROW Partition([freight],0,500,50) AS Range, Count([dbrders].[Freight]) AS [Count]
FROM dbrders
GROUP BY Partition([freight],0,500,50);

 

[Remou]

Partition is new for me. Thanks.

 

[was1]

ohh, okay, i got it thanks everybody for your help!!!

 

[dhookom]

While the Partition function is cool and works quite well under the current requirements, I always anticipate changing specifications. I would hate to create an expression that will break [red]when[/red] the specs change. Consider reviewing Data belongs in your tables -- not in your code


Duane
Hook'D on Access
MS Access MVP

 

[MajP]

To further echo what Dhookum is saying. Using vb functions in SQL statements is cool, and also really slow. Avoid them when you can do it in pure SQL.

Although 100K records is not that many I imagine Dhookum's suggestion will run much faster using his pure SQL solution vice a function call such as partition and especially a notoriously slow iif. I have not tested, but I bet we are talking orders of magnitude slower.