how to handle meter readings
in [Tables]
|
Prev: shipping database
Next: Help with pivot table
From: Jeff Boyce on 12 Nov 2009 08:01 If you do that "simple calculation" in a query, great! Don't bother trying to add that value into a field in a table... And if, by "input the peak and offpeak", you mean manually do the data-entry, are you sure you can't just load it from the spreadsheet (no re-typing required)? -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "deb" <deb(a)discussions.microsoft.com> wrote in message news:8E2402DB-DA85-4A07-A7B0-77257E830A28(a)microsoft.com... > 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: BruceM via AccessMonster.com on 12 Nov 2009 08:07 If you have a meter table and a related readings table, with a form and subform for data entry, you can use an unbound combo box on the main form to select a meter. The wizard can get you started on that. Once the meter is selected Access will go to that record, where you can enter or view Readings records. You can use a spreadsheet to import many Readings records all at once. If the meter number is unchanging you probably could use that rather than autonumber as the linking field between the two tables. If you put together a simple database with a few sample records you can see how the data will look in the tables, and plan accordingly. The main point is that a linking field is needed to associate a reading with a meter. Your import from Excel will need to include that field. If using the meter number is not practical there are other ways, but I won't get into that just now. My question about the meter number changing was with the idea that meters will break down or become damaged, and will need to be replaced. Will the new meter have the same meter number as the old? If so, no problem. If so, you may need to make provisions for continuity. deb wrote: >yep, but i think i'm too damn cheap for this much stress! >> >every time i hand them one >> >feature they get all excited and add something else (3 week job is now >[quoted text clipped - 3 lines] >> >> Just so it's billable hours... -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1
From: deb on 12 Nov 2009 18:32 "And if, by "input the peak and offpeak", you mean manually do the data-entry, are you sure you can't just load it from the spreadsheet (no re-typing required)? " i did suggest that but he wants an input form as well, so the figures can be input manually from the electricity bills if necessary. me, i'd just import from a spreadsheet each month and save the time, but i think he's worried the person doing it will be an office junior and he wants a nice simple interface -- deb "Jeff Boyce" wrote: > If you do that "simple calculation" in a query, great! Don't bother trying > to add that value into a field in a table... > > And if, by "input the peak and offpeak", you mean manually do the > data-entry, are you sure you can't just load it from the spreadsheet (no > re-typing required)? > > -- > > Regards > > Jeff Boyce > Microsoft Access MVP > > Disclaimer: This author may have received products and services mentioned in > this post. Mention and/or description of a product or service herein does > not constitute endorsement thereof. > > Any code or pseudocode included in this post is offered "as is", with no > guarantee as to suitability. > > You can thank the FTC of the USA for making this disclaimer > possible/necessary. > > "deb" <deb(a)discussions.microsoft.com> wrote in message > news:8E2402DB-DA85-4A07-A7B0-77257E830A28(a)microsoft.com... > > 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 12 Nov 2009 18:57 i did that, on the combo box on the main form the row source is SELECT [ElectricityMeter].[ElectricityMeterID], [ElectricityMeter].[MeterNumber] FROM ElectricityMeter ORDER BY [MeterNumber]; the record source for the main form is ElectricityMeter on the sub form the source object is MeterReading link child and master are ElectricityMeterID the ElectricityMeter table has ElectricityMeterID (link field) MeterNumber BuildingID LocationID the MeterReading table has MeterReadingID ElectricityMeterID (link field) ReadingDate PeakReading OffPeakReading linked with enforced referential integrity so you select the meter number from the combo box, fill in the date and readings - all good except its not picking up the ElectricityMeterID in the MeterReading table what am i missing? -- deb "BruceM via AccessMonster.com" wrote: > If you have a meter table and a related readings table, with a form and > subform for data entry, you can use an unbound combo box on the main form to > select a meter. The wizard can get you started on that. Once the meter is > selected Access will go to that record, where you can enter or view Readings > records. > > You can use a spreadsheet to import many Readings records all at once. If > the meter number is unchanging you probably could use that rather than > autonumber as the linking field between the two tables. If you put together > a simple database with a few sample records you can see how the data will > look in the tables, and plan accordingly. The main point is that a linking > field is needed to associate a reading with a meter. Your import from Excel > will need to include that field. If using the meter number is not practical > there are other ways, but I won't get into that just now. > > My question about the meter number changing was with the idea that meters > will break down or become damaged, and will need to be replaced. Will the > new meter have the same meter number as the old? If so, no problem. If so, > you may need to make provisions for continuity. > > deb wrote: > >yep, but i think i'm too damn cheap for this much stress! > >> >every time i hand them one > >> >feature they get all excited and add something else (3 week job is now > >[quoted text clipped - 3 lines] > >> > >> Just so it's billable hours... > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1 > > . >
From: John W. Vinson on 12 Nov 2009 19:08
On Thu, 12 Nov 2009 15:32:02 -0800, deb <deb(a)discussions.microsoft.com> wrote: >i did suggest that but he wants an input form as well, so the figures can be >input manually from the electricity bills if necessary. > >me, i'd just import from a spreadsheet each month and save the time, but i >think he's worried the person doing it will be an office junior and he wants >a nice simple interface well... Having two incompatible ways to do the same thing (importing spreadsheet AND a form) is a *complicated* interface, not a simple one...! -- John W. Vinson [MVP] |