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

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

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

On Tue, 10 Nov 2009 23:14:05 -0800, deb <deb@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]

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@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]
> .
> 

On Wed, 11 Nov 2009 15:20:01 -0800, deb <deb@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]

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@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]
> .
> 

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@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]
> .
> 

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
> 
> .
> 

ah crap, well that didnt work did it - i should have listened to you 
-- 
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
> 
> .
> 

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
> 
> .
> 

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@discussions.microsoft.com> wrote in message 
news:8E2402DB-DA85-4A07-A7B0-77257E830A28@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@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]
>> .
>> 

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

"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@discussions.microsoft.com> wrote in message 
> news:8E2402DB-DA85-4A07-A7B0-77257E830A28@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@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]
> >> .
> >> 
> 
> 
> .
> 

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
> 
> .
> 

On Thu, 12 Nov 2009 15:32:02 -0800, deb <deb@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]

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@discussions.microsoft.com> wrote in message 
news:756988FB-0EC5-43D4-848E-822E826C780B@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@discussions.microsoft.com> wrote in message
>> news:8E2402DB-DA85-4A07-A7B0-77257E830A28@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@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]
>> >> .
>> >>
>>
>>
>> .
>> 

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@discussions.microsoft.com> wrote in message 
> > news:8E2402DB-DA85-4A07-A7B0-77257E830A28@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@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]
> > >> .
> > >> 
> > 
> > 
> > .
> > 

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@discussions.microsoft.com> wrote in message 
> news:756988FB-0EC5-43D4-848E-822E826C780B@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@discussions.microsoft.com> wrote in message
> >> news:8E2402DB-DA85-4A07-A7B0-77257E830A28@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@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]
> >> >> .
> >> >>
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 

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

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

I've used an  unbound combo box and it works but the meter number i select is 
not being pulled into the meter reading table - ive got no other fields in 
the main form, could that be the problem, should i have the meterID in there 
somewhere (that doesnt make sense though)

this is the row source SELECT [ElectricityMeter].[ElectricityMeterID], 
[ElectricityMeter].[MeterNumber] FROM ElectricityMeter ORDER BY 
[MeterNumber]; 

nothing else is set, no events etc

the subform is set up correctly - i checked, child and master are set to 
ElectricityMeterID and thats the link field and it drops the readings into 
the table nicely so it must be set up correctly - its just the meter number 
from the main form thats not working

i've stumbled my way through waaaaay more complicated forms than this - 
aaaarrrrggg!
-- 
deb


"BruceM via AccessMonster.com" wrote:

> 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
> 
> .
> 

my combo box works and the subtable works - checked all the things you 
mentioned and everything is in the right spot, its just not picking up the 
meter number in the meter reading table when you input a new reading, the 
date shows, the readings show but the meter number spot is blank


-- 
deb


"BruceM via AccessMonster.com" wrote:

> 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
> 
> .
> 

by george i think i've got it!!

i didnt have anything in the control source for the combo box

yaaaayyyy!
-- 
deb


"deb" wrote:

> my combo box works and the subtable works - checked all the things you 
> mentioned and everything is in the right spot, its just not picking up the 
> meter number in the meter reading table when you input a new reading, the 
> date shows, the readings show but the meter number spot is blank
> 
> 
> -- 
> deb
> 
> 
> "BruceM via AccessMonster.com" wrote:
> 
> > 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
> > 
> > .
> >