Setting up the table for Month to Month reporting

  • Thread starter Thread starter Marti
  • Start date Start date
M

Marti

I need to set up table(s) for entering monthly readings on
water meters and then calculate the previous month from
the current month to figure credit due. Do I have to set
up 12 monthly fields for both readings and credit? How do
I then set up query to figure the credit calculations?

Thanks for any help at all
 
What you suggest is not a "normalized" data structure, and as such will only
cause problems. A better approach is to have just one table for all meter
readings:
tblMeterReadings
ReadingID - autonumber , primary key
CustomerID -integer, foreign key linked to tblCustomer
ReadDate - date/time
MeterRead - long integer
etc.

This will allow you to filter/sort for any single customer and find all
related records, or filter/sort for any date period and find all readings by
customer, etc.

A tblPayments (similar to above) would record the $ amount and gal/cubic ft
paid for, along with date and CustomerID. A calculation of water used
(period A - period B) could be reported vs. water paid.

Your application will be similar to an inventory/order system - of which
there are many examples and starting templates. Check'em out.
-Ed
 
Back
Top