general design question

[desertdirk]

I am setting up a database that will record and calculate electric usage
for an RV campground. I have tables for the sites, customers and meter
readings. What I am unable to do is figure out how to get the meter
reading from the previous month so that I can subtract it from the
reading of the current month.

any help is appreciated
Thanks

 

[Rolls]

General Design Suggestion:

Assuming that you have one table which contains site, date, and meter
fields. For two consecutive readings there would be two records in the
table. Query this table using a "Where" clause with "Between" criteria that
would include both dates such as "Between #10/01/2003# and #11/30/2003#"
which limits the query result to two records within this range. You'll need
three fields in the query to do the calculation. The first two use an IIF
statement to find the reading from the prior and current months. The third
field will subtract [prior] from [current] to calculate 1 month's usage.
The format for the IIF (Immediate If) statement is described in help. It
had three parameters: 1) criteria, 2) true result, 3) false result. There
are several ways to select current and prior months using date functions.
Try to make these formulae generic so you don't have to edit them.

This suggestion assumes that there is one meter per campsite and that it can
be read only once per day. You can have many sites and many campers. To
resolve this you need a separate camper-site table which can then be used to
associate a name with a useage calculation via a JOIN between the
camper-site table and the query which calculates useage for each site.

The camper-site table is maintained via a form that is maintained at
check-in and check-out. One table has one record per campsite. One table
has personal information for each unique camper. The camper-site table is
on a subform with two combo boxes with a rowsource for camper and site
respectively.

 

[desertdirk]

Thank you for your suggestions.
I was not being emailed that there was a response to my questions so
sorry for the delay

What I am ending up with is very similar to what you suggest only it is
on a monthly basis. One table for sites, one tabel for guests, and a
table for the readings.
I appreciate your help
DD