Help with a change in Access design - price 3 for $50

New Contributor

A2007.  I have a sport database I've used for 10 years.  Works great.  We skate weekly and I log who is present for the week, the cost for that week, and how much they paid.  I print out weekly to show balances, as people may pay ahead, or may get behind.  The price is normally $15 each week.  Easy enough. (There is much more to the db, but I want to limit the scope to this problem)

Prices have gone up slightly, so now I want to charge $50 for 3 skates.  I don't want to change the table design to accept like $16.6666 as the normal charge and deal with rounding in the existing reports.  For now I plan to charge $17 for two weeks and $16 for the third (=$50).  BUT, since not everyone is there each week, I will need to adjust people occasionally to have them equal the $50 I'm receiving/charging.  IDK how else to manage it.  Any ideas?

2 Replies
best response confirmed by maarkr (New Contributor)



How do you record payments now? It seems to me that the idea of charging skaters $17.00 a skate, with a $1.00 discount every third skate maintains things pretty well with a minimum of additional processing.


All you have to do is create an aggregate query that counts the number of times a skater has paid the $17.00 charge since the price increase went into effect and display the current status of their account accordingly. If it's the third weekly charge apply a discount (or maybe a refund of $1.00 into the table for that week) and collect the "balance" of $16.00. 


It occurs to me that this is a variation on the punch card for coffee or donuts, etc. every time you buy a coffee, the shop punches a hole in the card. When there are 10 holes in the card, the coffee is free and you get a new card. Here, on the third digital "punch", you refund them $1.00 and only collect the remaining $16.00 balance.


The other path leads to complexity, chaos and lost sleep, IMO. 


I'd like to take a whack at a possible design for that, in fact, if you can upload a copy of the accdb with just enough sample data (remove personally identifiable information) to get started.



Thanks, George, that sounds like a good solution. I have a credit field with the cost field, so it should be easy to do, thanks