A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

how to handle meter readings



 
 
Thread Tools Display Modes
  #1  
Old November 11th, 2009, 07:09 AM posted to microsoft.public.access.tablesdbdesign
deb
external usenet poster
 
Posts: 898
Default how to handle meter readings

i have to create a table that will import meter readings

my first instinct is to just have a simple table that has meter number,
location and then readings by date (june, july etc) and then create a new
table when the year kicks over - keeping in mind location is an ID that links
to another table

am i being too simple?
--
deb
  #2  
Old November 11th, 2009, 08:14 AM posted to microsoft.public.access.tablesdbdesign
deb
external usenet poster
 
Posts: 898
Default how to handle meter readings

dammit now i've been told i have to have peak, offpeak and total for the
meter readings for each meter

how do i do this without creating a separate tbl for each meter?
--
deb


"deb" wrote:

i have to create a table that will import meter readings

my first instinct is to just have a simple table that has meter number,
location and then readings by date (june, july etc) and then create a new
table when the year kicks over - keeping in mind location is an ID that links
to another table

am i being too simple?
--
deb

  #3  
Old November 11th, 2009, 02:29 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default how to handle meter readings

You absolutely should not have a field for each month. Starting with the
simple example of one reading per month, you need a Meter table and a related
Readings table:

tblMeter
MeterID (primary key, or PK)
MeterLocation
SerialNumber or whatever other fields are needed to describe the meter

tblReading
ReadingID (PK)
MeterID
ReadingDate
Reading

Using autonumber for the PK fields should work. I would guess that anything
else would be subject to change if the meter is replaced, and things of that
sort. If MeterID in tblMeter is autonumber (as determined in table design
view), MeterID in tblReading must by Number (Long Integer). Even if a meter
is identified by a code number or some such thing, I still suspect that
number could change, so I would stay with Autonumber or other unchanging PK,
and add a field for the ID number that is exposed to the user.

Click Tools Relationships. Add both tables. Drag MeterID from one table
to another. Click Enforce Referential Integrity when prompted.

Make a form (frmMeter) based on tblMeter (that is, add the fields in which
users record data about the meter initially. If MeterID is autonumber you
probably should not use it on the form). Make another form (frmReading)
based on tblReading. Set the Default View of frmReading to Continuous.

With frmMeter open in desgn view, add a subform control from the toolbox.
Set its Source Object to frmReading, and its Link Child and Link Master
fields to MeterID.

Switch to Form view for frmMeter. Add meter information to the main form,
and reading information to the subform, one line (record) per reading.

As for off-peak, etc., I don't know how you go about recording that data. If
the meter reader obtains that information from the meter itself it should be
simply a matter of adding fields to tblReading, and text boxes to frmReading
for those fields.

I don't know if or how account information enters into this, or if you can
have more than one meter per location. In any case, if Location is an
address you will need extra fields in tblMeter for each component of the
address (number, street, city, etc.).

If a location is an address, and an address can have several meters, you will
need a Location table at the top of the hierarchy. In that case tblMeter
will be related to tblLocation as tblReading is related to tblMeter in the
scenario described here.

If as I suspect you are unfamiliar with relational database design principles,
you would do well to become familiar with the concepts. Here are some links
John Vinson often provides. IMHO Crystal's tutorial is a good place to start.


Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


deb wrote:
dammit now i've been told i have to have peak, offpeak and total for the
meter readings for each meter

how do i do this without creating a separate tbl for each meter?
i have to create a table that will import meter readings

[quoted text clipped - 4 lines]

am i being too simple?


--
Message posted via http://www.accessmonster.com

  #4  
Old November 11th, 2009, 10:57 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default how to handle meter readings

On Tue, 10 Nov 2009 23:14:05 -0800, deb wrote:

dammit now i've been told i have to have peak, offpeak and total for the
meter readings for each meter

how do i do this without creating a separate tbl for each meter?


Two tables are all that are needed: Meters (one row per meter, with a unique
ID, its location, maybe information about the owner or what it's metering) and
Readings (ReadingID autonumber primary key, MeterID, ReadingDate (Date/Time),
Reading).

Peak, offpeak and total would be calculated dynamically in Queries based on
the Readings table; just how I don't know since I have no idea how often there
are readings, or how you distinguish peak from offpeak.
--

John W. Vinson [MVP]
  #5  
Old November 12th, 2009, 12:20 AM posted to microsoft.public.access.tablesdbdesign
deb
external usenet poster
 
Posts: 898
Default how to handle meter readings

thanks guys, thats just what i needed

i do understand how relationships work but i'm still in the learning curve
so far i've only created simple address and product databases but this one
is turning out to be a little more tricky - every time i hand them one
feature they get all excited and add something else (3 week job is now
turning into over 9 weeks!)

Bruce, thanks for the links, i need to get up to speed really fast and that
will be a big help
--
deb


"John W. Vinson" wrote:

On Tue, 10 Nov 2009 23:14:05 -0800, deb wrote:

dammit now i've been told i have to have peak, offpeak and total for the
meter readings for each meter

how do i do this without creating a separate tbl for each meter?


Two tables are all that are needed: Meters (one row per meter, with a unique
ID, its location, maybe information about the owner or what it's metering) and
Readings (ReadingID autonumber primary key, MeterID, ReadingDate (Date/Time),
Reading).

Peak, offpeak and total would be calculated dynamically in Queries based on
the Readings table; just how I don't know since I have no idea how often there
are readings, or how you distinguish peak from offpeak.
--

John W. Vinson [MVP]
.

  #6  
Old November 12th, 2009, 12:47 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default how to handle meter readings

On Wed, 11 Nov 2009 15:20:01 -0800, deb wrote:

every time i hand them one
feature they get all excited and add something else (3 week job is now
turning into over 9 weeks!)


LOL!!!

Just so it's billable hours...
--

John W. Vinson [MVP]
  #7  
Old November 12th, 2009, 02:04 AM posted to microsoft.public.access.tablesdbdesign
deb
external usenet poster
 
Posts: 898
Default how to handle meter readings

yep, but i think i'm too damn cheap for this much stress!
--
deb


"John W. Vinson" wrote:

On Wed, 11 Nov 2009 15:20:01 -0800, deb wrote:

every time i hand them one
feature they get all excited and add something else (3 week job is now
turning into over 9 weeks!)


LOL!!!

Just so it's billable hours...
--

John W. Vinson [MVP]
.

  #8  
Old November 12th, 2009, 02:24 AM posted to microsoft.public.access.tablesdbdesign
deb
external usenet poster
 
Posts: 898
Default how to handle meter readings

peak, offpeak and total are simply going to be supplied to us as figures,
monthly in a spreadsheet - so i think i'll just input the peak and offpeak
and then do the total as a simple calculation
--
deb


"John W. Vinson" wrote:

On Tue, 10 Nov 2009 23:14:05 -0800, deb wrote:

dammit now i've been told i have to have peak, offpeak and total for the
meter readings for each meter

how do i do this without creating a separate tbl for each meter?


Two tables are all that are needed: Meters (one row per meter, with a unique
ID, its location, maybe information about the owner or what it's metering) and
Readings (ReadingID autonumber primary key, MeterID, ReadingDate (Date/Time),
Reading).

Peak, offpeak and total would be calculated dynamically in Queries based on
the Readings table; just how I don't know since I have no idea how often there
are readings, or how you distinguish peak from offpeak.
--

John W. Vinson [MVP]
.

  #9  
Old November 12th, 2009, 02:27 AM posted to microsoft.public.access.tablesdbdesign
deb
external usenet poster
 
Posts: 898
Default how to handle meter readings

i think i just need the one form for meter readings - the actual meter
numbers will never change and the only time there will be new ones is if we
buy a new building - doesnt happen very often (once a year or two maybe)

so i'll create the table for meter numbers and adding to it will be a data
import from a spreadsheet
--
deb


"BruceM via AccessMonster.com" wrote:

You absolutely should not have a field for each month. Starting with the
simple example of one reading per month, you need a Meter table and a related
Readings table:

tblMeter
MeterID (primary key, or PK)
MeterLocation
SerialNumber or whatever other fields are needed to describe the meter

tblReading
ReadingID (PK)
MeterID
ReadingDate
Reading

Using autonumber for the PK fields should work. I would guess that anything
else would be subject to change if the meter is replaced, and things of that
sort. If MeterID in tblMeter is autonumber (as determined in table design
view), MeterID in tblReading must by Number (Long Integer). Even if a meter
is identified by a code number or some such thing, I still suspect that
number could change, so I would stay with Autonumber or other unchanging PK,
and add a field for the ID number that is exposed to the user.

Click Tools Relationships. Add both tables. Drag MeterID from one table
to another. Click Enforce Referential Integrity when prompted.

Make a form (frmMeter) based on tblMeter (that is, add the fields in which
users record data about the meter initially. If MeterID is autonumber you
probably should not use it on the form). Make another form (frmReading)
based on tblReading. Set the Default View of frmReading to Continuous.

With frmMeter open in desgn view, add a subform control from the toolbox.
Set its Source Object to frmReading, and its Link Child and Link Master
fields to MeterID.

Switch to Form view for frmMeter. Add meter information to the main form,
and reading information to the subform, one line (record) per reading.

As for off-peak, etc., I don't know how you go about recording that data. If
the meter reader obtains that information from the meter itself it should be
simply a matter of adding fields to tblReading, and text boxes to frmReading
for those fields.

I don't know if or how account information enters into this, or if you can
have more than one meter per location. In any case, if Location is an
address you will need extra fields in tblMeter for each component of the
address (number, street, city, etc.).

If a location is an address, and an address can have several meters, you will
need a Location table at the top of the hierarchy. In that case tblMeter
will be related to tblLocation as tblReading is related to tblMeter in the
scenario described here.

If as I suspect you are unfamiliar with relational database design principles,
you would do well to become familiar with the concepts. Here are some links
John Vinson often provides. IMHO Crystal's tutorial is a good place to start.


Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


deb wrote:
dammit now i've been told i have to have peak, offpeak and total for the
meter readings for each meter

how do i do this without creating a separate tbl for each meter?
i have to create a table that will import meter readings

[quoted text clipped - 4 lines]

am i being too simple?


--
Message posted via http://www.accessmonster.com

.

  #10  
Old November 12th, 2009, 03:11 AM posted to microsoft.public.access.tablesdbdesign
deb
external usenet poster
 
Posts: 898
Default how to handle meter readings

ah crap, well that didnt work did it - i should have listened to you
--
deb


"BruceM via AccessMonster.com" wrote:

You absolutely should not have a field for each month. Starting with the
simple example of one reading per month, you need a Meter table and a related
Readings table:

tblMeter
MeterID (primary key, or PK)
MeterLocation
SerialNumber or whatever other fields are needed to describe the meter

tblReading
ReadingID (PK)
MeterID
ReadingDate
Reading

Using autonumber for the PK fields should work. I would guess that anything
else would be subject to change if the meter is replaced, and things of that
sort. If MeterID in tblMeter is autonumber (as determined in table design
view), MeterID in tblReading must by Number (Long Integer). Even if a meter
is identified by a code number or some such thing, I still suspect that
number could change, so I would stay with Autonumber or other unchanging PK,
and add a field for the ID number that is exposed to the user.

Click Tools Relationships. Add both tables. Drag MeterID from one table
to another. Click Enforce Referential Integrity when prompted.

Make a form (frmMeter) based on tblMeter (that is, add the fields in which
users record data about the meter initially. If MeterID is autonumber you
probably should not use it on the form). Make another form (frmReading)
based on tblReading. Set the Default View of frmReading to Continuous.

With frmMeter open in desgn view, add a subform control from the toolbox.
Set its Source Object to frmReading, and its Link Child and Link Master
fields to MeterID.

Switch to Form view for frmMeter. Add meter information to the main form,
and reading information to the subform, one line (record) per reading.

As for off-peak, etc., I don't know how you go about recording that data. If
the meter reader obtains that information from the meter itself it should be
simply a matter of adding fields to tblReading, and text boxes to frmReading
for those fields.

I don't know if or how account information enters into this, or if you can
have more than one meter per location. In any case, if Location is an
address you will need extra fields in tblMeter for each component of the
address (number, street, city, etc.).

If a location is an address, and an address can have several meters, you will
need a Location table at the top of the hierarchy. In that case tblMeter
will be related to tblLocation as tblReading is related to tblMeter in the
scenario described here.

If as I suspect you are unfamiliar with relational database design principles,
you would do well to become familiar with the concepts. Here are some links
John Vinson often provides. IMHO Crystal's tutorial is a good place to start.


Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


deb wrote:
dammit now i've been told i have to have peak, offpeak and total for the
meter readings for each meter

how do i do this without creating a separate tbl for each meter?
i have to create a table that will import meter readings

[quoted text clipped - 4 lines]

am i being too simple?


--
Message posted via http://www.accessmonster.com

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:00 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.