how to handle meter readings
in [Tables]
|
Prev: shipping database
Next: Help with pivot table
From: Jeff Boyce on 12 Nov 2009 21:02 As you've no doubt found, "easy ... is HARD!" Making a user interface that users find simple and easy is not an insignificant task. It sounds a little like the person directing you isn't clear on what he wants! -- 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:756988FB-0EC5-43D4-848E-822E826C780B(a)microsoft.com... > "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 21:35 i must say i agree with you - the potential for stuff up is huge i'll give them the form and keep the import details to myself -- deb "deb" wrote: > "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 13 Nov 2009 02:27 LOL! i managed to convince him to use access even though he didnt know much about it, no that he's discovered how useful its going to be he's like a kid with a new toy. keep in mind, this is a company with a multimillion dollar turnover, dozens of buildings and hundreds of tenants running on spreadsheets...... -- deb "Jeff Boyce" wrote: > As you've no doubt found, "easy ... is HARD!" > > Making a user interface that users find simple and easy is not an > insignificant task. > > It sounds a little like the person directing you isn't clear on what he > wants! > > -- > > 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:756988FB-0EC5-43D4-848E-822E826C780B(a)microsoft.com... > > "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 15 Nov 2009 19:11 ok, so what am i doing wrong - i cant get it to pick up the meter number in the meter reading table -- 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 16 Nov 2009 08:19
You would generally use an unbound combo box for searching. I can't tell if you have done that, so I'm mentioning it. You say that part is working OK, so I assume you have this set up correctly. The subform is in a "box" on the main form. The box is the subform control. Be sure its Link Child and Link Master properties are set to the linking fields. To view the subform control property sheet, click the subform to select it, then click View >> Properties. Be sure you are looking at the control properties, which will be labeled something like Subform/Subreport: ControlName. Click the three dots next to Link Child or Link Master. Access will probably select the correct fields if the tables are properly related. deb wrote: >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? > >> 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 >[quoted text clipped - 22 lines] >> >> >> >> Just so it's billable hours... -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1 |