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. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
how to handle meter readings
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/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 . |
#12
|
|||
|
|||
how to handle meter readings
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" wrote in message ... 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] . |
#13
|
|||
|
|||
how to handle meter readings
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/For...esign/200911/1 |
#14
|
|||
|
|||
how to handle meter readings
"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" wrote in message ... 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] . . |
#15
|
|||
|
|||
how to handle meter readings
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/For...esign/200911/1 . |
#16
|
|||
|
|||
how to handle meter readings
On Thu, 12 Nov 2009 15:32:02 -0800, deb 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] |
#17
|
|||
|
|||
how to handle meter readings
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" wrote in message ... "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" wrote in message ... 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] . . |
#18
|
|||
|
|||
how to handle meter readings
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" wrote in message ... 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] . . |
#19
|
|||
|
|||
how to handle meter readings
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" wrote in message ... "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" wrote in message ... 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] . . . |
#20
|
|||
|
|||
how to handle meter readings
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 |
Thread Tools | |
Display Modes | |
|
|