Listing 25 transactions

[Guest]

HI heres a complicated one;
I have a transaction table with 4 types of transaction, Cash (ca), C/Card
(cc), Cheque(ch). Columns are InvNr, Amount Paid, Ca, CC,CH.
What I need is to get all cash transactions listed so that I can assign a
value to the first 25 transactions, the value needs to be the same for the
first 25, then assign a different number for the next 25 trans, and so-on,
for all types of payments. (these will end up in cash batches, cc batches ,
ch batches). I have tried using an autonumber (roundup(record nr/25)+
Datevalue(today), which will give me what I want but if i remove/change a
transaction i leave a hole in the batch ie: 24 transactions not 25. Can i
get the number of a record ie: this record is the 26th cash transaction? Is
there another way of doing it?
Please help
Thanks

Roger

 

[[MVP] S.Clark]

HI heres a complicated one;
I have a transaction table with 4 types of transaction, Cash (ca), C/Card
(cc), Cheque(ch). Columns are InvNr, Amount Paid, Ca, CC,CH.
What I need is to get all cash transactions listed so that I can assign a
value to the first 25 transactions, the value needs to be the same for the
first 25, then assign a different number for the next 25 trans, and so-on,
for all types of payments. (these will end up in cash batches, cc batches ,
ch batches). I have tried using an autonumber (roundup(record nr/25)+
Datevalue(today), which will give me what I want but if i remove/change a
transaction i leave a hole in the batch ie: 24 transactions not 25. Can i
get the number of a record ie: this record is the 26th cash transaction? Is
there another way of doing it?
Please help
Thanks

Roger

 

[[MVP] S.Clark]

I don't understand the table structure. Typical normalized data would not
have each one of the different payment types included as fields. Rather,
there would be a field for Payment Type, then a column for the amount.

Regardless of the structure, the process of assigning the batch numbers will
probably require some VBA coding.

 

[Guest]

Thanks,

The reason I have set it up this way is that1 invoice could be paid using
different payment , actually happens about 30% of the time. I have tried
setting it up as different tables which does work with alot of mucking about.
The batching works mostly but does not resolve all issues.

 

[John Vinson]

Thanks,

The reason I have set it up this way is that1 invoice could be paid using
different payment , actually happens about 30% of the time. I have tried
setting it up as different tables which does work with alot of mucking about.
The batching works mostly but does not resolve all issues.

That's usually the case with non-normalized design. It makes the *one
task* for which the table was denormalized easy; but it makes many
other tasks exponentially more difficult!

With a proper table design you can (almost always) massage the data
around to fit almost any reporting need; the simplest tasks might be a
bit more difficult, but the hard ones are still doable.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks John,

This leads me to the next quest (sorry about delay had troubles accessing
website),]
HOW do I set these tables up?
Bearing in mind the user may have multiple payment method for the SAME
invoice at the same time, eg they will pay by cash and credit card. The
batches created need to have 1 type of payment only ,eg: 25 transactions of
cash per batch, completely separate batch for credit card, and another batch
for combination payments (where payment has been made by multiple methods).

Any Help here would be great!