Just a general bit of advice really. What was to be originally an invoicing system, I'm now being asked to make an accounts system and I'm wondering the best way to go about it with access.
Customers are invoiced per job which is fine but they pay off an account as and when, and not the sum of each invoice. So it would be similar to a bank with debits and credits...
The best way I see to do it is a column for credits and a column for debits, total and subtract the difference for the balance.
So what I'm asking is if I do it this way will I run into problems as the accounts grow because of all the adding and subtracting? As in, 3 years from now it will still be using credits and debits from the first months and all following months to get the year 3 balance... There will be about 30 or so customers with 6 or 7 credit/debits per month.
Or is this no problem to access and I'm just worrying?
Any RDBMS that can't handle that is not worthy of the name! Properly indexed, with customer number the leading column, then the number of rows "touched" by the engine to answer a query about a given customer is a touch over 1/30th of the total.