how to handle meter readings
in [Tables]
|
Prev: shipping database
Next: Help with pivot table
From: deb on 11 Nov 2009 01:09 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
From: deb on 11 Nov 2009 02:14 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
From: BruceM via AccessMonster.com on 11 Nov 2009 08:29 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/accessjunkie/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
From: John W. Vinson on 11 Nov 2009 16:57 On Tue, 10 Nov 2009 23:14:05 -0800, deb <deb(a)discussions.microsoft.com> 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]
From: deb on 11 Nov 2009 18:20
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 <deb(a)discussions.microsoft.com> 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] > . > |