Batching records

[Guest]

HI all,
I have a table called transaction, with theses columns; Inv_Nr, Cash, CC,
Chq. Where the payment for the invoice is put under the appropriate heading.
I need to create a query(or maybe a report) to assign differnt numbers to
batches of 25 transactions. These batches can only contain like payments.
Eg: I need to create a batch number for only the first 25 transactions for
cash, then a diff number for the next 25 and so on.
Do you have any Ideas?
Thanks

 

[Dale Fye]

This would be easier if you changed your table structure to:

Inv_Nr
Pmt_Type: "Cash", "CC", "Chq"
Amount

Then you would just do:

SELECT Top 25 *
FROM yourTable
WHERE Pmt_Type= "Cash"
Order By Inv_Nr

This assumes that Inv_Nr are consecutive numbers. It would also have the
added benefit of being able to sum over a single column when you go to track
your receipts.

With your current table structure you might do something like:

SELECT Top 25 Inv_Nr, Cash
FROM yourTable
WHERE NOT isNull([Cash])
Order By Inv_Nr