Help needed in designing a database (1 Viewer)

[uttamsaxena]

I want to design a database with following requirement--

1) I am managing my township association which have around 1000 houses.I made a database of each house as its owners name, house no, address, phone, etc.(Single Table)

2) Now I have to maintain the electricity consumption data, and its payment for each house every month and so on forever. I mean every month three data for each house has to be stored i.e. meter_reading, power_consumption and paid_amount.

Can you help me how to build a database for this objective? What table should be made now?

 

[Steve R.]

TBL_House
hidnum <=autnumber
htaxidnum <= In case you need to contact the County recorder's office.
haddress
--any other HOUSE data you may wish

TBL_Owner
oidnum <= liniked to TBL_House hidnum
omailaddress <= You can have some logic so that if the owners mail address is the same as the house, the info is just copied.
ophonenumber
-- Any other OWNER data you may wish.

PS: You may need a TBL_Rental in case any units are rented. Also addtional logic of if the owner or renter pays the power bill.


TBL_Meter
midnum <=Linked to TBL_House hidum
mbillnum <= the unique number of each bill
mstart <=meter reading at the start
mend <=meter reading at end
-- any other METER data such as model number, date installed etc.


TBL_Bill
bbillnum <= from TBL_Meter
bcost <= $cost per killowatt
bduedate <= When is bill due
pbaid <= Paid? yes/no - Some would say is not necessary as bpaiddate provides this info.
bpaiddate <=date paid
-- In theory, any balance (deficiet/overage) can be computed.
-- bcost may to too difficult to compute. May just want to take final cost.

 

[uttamsaxena]

Thanks a lot for your help. As I am learning Access, this is of very much help to me.

one question ---

I don't see the months, for which reading has been taken, in your proposed table . Suppose we need to know the meter reading of Mr A OR of Meter No. 123 OR house no 345 for the month Jan 2004 then how it will be done.

Or if we want to look the consumption pattern or readings for past 12 months from current month.

Thanks again.

 

[Steve R.]

You are correct. Missed suggesting a meter read date in TBL_Meter.

 

[uttamsaxena]

Hi, everybody,

Thanks for nice tips.

I am not making a billing database as the billing mechanism itself is very complicated. I have just to track the consumption pattern of each household.

Following thing are also to be checked but I am not that advance in Access , so your help would be needed on this---

1. Suppose there are 1000 houses so every month the powertbl will have to be added with 1000 records. What could be the method for this. Is it possible that every month starting 1000 new records are added automatically and only feeding of reading is required.

2. How it could be checked that no meter have duplicate record with different readings for any month.

3. How it could be checked that reading of any meter for each month is never less than the reading of same meter in immediatly preceding month.

Any suggestion is welcome.

 

[gemma-the-husky]

you wouldn't want to preload every house with a blank record

i would use an unbound form, to select the house, a reading date, and a meter reading, then use code to load the record (i would use a rst update code, but you could use sql directly or use an append query, matter of taste)

before processing the update, you could test that the current date is after the last date, is a sensible date, is in a different month to the last, and that the reading satisfies similar parameters.

you would then be able to interrogate the dbs to find any houses with no readings for a given month, high or low usage etc etc

using any code is not really trivial, and if you are new to writing programs you will probably need some guidance

 

[dsigner]

I have read all the comments with interest. The only thing I can see which is overlooked is estimated readings. You need a field (binary) for this. Otherwise you will get caught sooner or later.

 

[Steve R.]

I would advocate that you do NOT need a field (in a table) for "estimated readings". However, it depends on your needs and approach. Estimated readings can be derived by computation (of historical data) and inserted into an unbound field on a form.

 

[dsigner]

estimate versus actual

I would advocate that you do NOT need a field (in a table) for "estimated readings". However, it depends on your needs and approach. Estimated readings can be derived by computation (of historical data) and inserted into an unbound field on a form.

Sorry I was not precise enough. There should be only one reading per period.
The estimated field is not to hold a reading or to permit being calculated. It is a binary field which distinguishes between an actual reading and an estimate. Most of the time it does not matter but sometimes (a change of tenancy for example it does matter).

 

[uttamsaxena]

The data provided by the utility is in the following format-

Serial No/ Consumer No/ Meter No/ Name and Address/ Meter reading/Meter status i.e. OK, Defective or No reading.

This info is provided for all the consumers for a month in one booklet. Like wise I have booklets for all the months for this financial year i.e. April to Sept.

As for as the meters are concerned following situations are possible-

1. All the consumers have same meter for all the period and in this case it must be checked that reading recorded should not be less than previous month's reading and if this happen reading should be physically verified and rectified in database.

2. Any consumers meter may be defective and a new meter with some initial reading will be installed. In this case the consumption, in the month when meter is changed, will be the :
(New meter reading - New meter initial reading) + (Old meter reading on removal - old meter previous month reading)

3. The defective meter may be scraped if unserviceable or may be installed at same or any other house where its meter becomes defective.

I have not been provided with the kilowatt Hours (i.e. kWh or Units consumed) but the meter reading for each month and kWh is to be calculated by substracting the current reading and previous reading.