how to handle meter readings
in [Tables]
|
Prev: shipping database
Next: Help with pivot table
From: John W. Vinson on 11 Nov 2009 18:47 On Wed, 11 Nov 2009 15:20:01 -0800, deb <deb(a)discussions.microsoft.com> 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]
From: deb on 11 Nov 2009 20:04 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 <deb(a)discussions.microsoft.com> 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] > . >
From: deb on 11 Nov 2009 20:24 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 <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 20:27 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/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: deb on 11 Nov 2009 23:25
ok - this isnt working i dont want them to be able to input the meter number - i need them to be able to pick that from a combo box - although that might be a pain because there will be hundreds ...(hmmm not so good) - maybe i need them to be able to select by property first then have a combo for meter numbers to cut down the list why do i have to have a sub form? why cant i have a qry based on the two tables and have a single input form? if these are dumb questions feel free to virtually slap me ;) -- 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/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 > > . > |