Creating a new table from information in a form??

  • Thread starter Thread starter Bill Doodson
  • Start date Start date
B

Bill Doodson

I have been playing around with a database of meter
readings for a couple of years and now want to build a real
DATABASE to do a real job. The meters are water, electric,
gas etc and are read every week. I will want to add new
meters at times so I dont want to have a fixed meter table
with fields called say W1, W2, W3, E1, E2, E3 etc. I want
to use a form to input the relevant data and generate a new
table for that particular meter. Is this easily done or am
I asking to much?

Thanks

Bill
 
Bill

Any time I see "repeating fields" (W1, W2, ..) I am reminded of Excel... so
your desire not to have repeating fields is a good idea!

I am confused by your follow-on statement, however, and your comment about
generating "a new table" for a new meter. If "repeating fields" are bad (in
a relational database, trust me, it's bad), then "repeating tables" are
worse!

Without more description of what you'd include, the following is just a
generic guess. See if it doesn't give you a way to record the facts you
need:

tblMeter
MeterID
MeterTag (?manufacturer serial number or ...?)
MeterType (water, gas, electric, ...)
DateAdded
DateRemovedFromService
ServiceLocation

trelMeterReading
MeterReadingID
MeterID (foreign key, from tblMeter)
ReadingDate
Reading

I included the initial xxxID fields in each in case there was no other
reasonable candidate for a unique row identifier.

Good luck!

Jeff Boyce
<Access MVP>
 
PMFJI,
How is your tblWeeklyMeterReadings related to MeterDetails? Does a single
record in tblWeeklyMeterReadings contain readings for 42 different meters or
42 different readings from the same meter. Either way, this is highly
un-normalized and should be restructured if possible.

I am reading "MeterW1" as "Week 1" but it may be something else since you
also have a [Date] field. BTW: Date is not a good name for a field since it
is a function name in Access.

--
Duane Hookom
Microsoft Access MVP


Bill Doodson said:
Jeff,

I have a table called MeterDetails that is similar to
your table [tblMeter]. I also have a table
WeeklyMeterReadings which has the following;

tblWeeklyMeterReadings
Batch
Date
MeterW1
MeterW2
Meter-?-
Meter-?+1-
to MeterEff2 42 meters in all.

I want to break up this WeeklyMeterReading table due to
not being able to query it very well due to its size. I
need to get a table of weekly consumptions out of it but
the query I have will only allow me to cover 15 meters.

Your second "table" starts trelMeterReading, what is trel?

Is there anyway to send you screen shots to show what I
am doing?

Bill
-----Original Message-----
Bill

Any time I see "repeating fields" (W1, W2, ..) I am reminded of Excel... so
your desire not to have repeating fields is a good idea!

I am confused by your follow-on statement, however, and your comment about
generating "a new table" for a new meter. If "repeating fields" are bad (in
a relational database, trust me, it's bad), then "repeating tables" are
worse!

Without more description of what you'd include, the following is just a
generic guess. See if it doesn't give you a way to record the facts you
need:

tblMeter
MeterID
MeterTag (?manufacturer serial number or ...?)
MeterType (water, gas, electric, ...)
DateAdded
DateRemovedFromService
ServiceLocation

trelMeterReading
MeterReadingID
MeterID (foreign key, from tblMeter)
ReadingDate
Reading

I included the initial xxxID fields in each in case there was no other
reasonable candidate for a unique row identifier.

Good luck!

Jeff Boyce
<Access MVP>

.
Click to expand...
Click to expand...
 
Duane,

The tables are related by the meter names W1, W2 etc as the
is added. There are fields for 42 meters in
tblWeeklyMeterReadings, the meter MeterW1 is water meter 1
and MeterW2 is water meter 2 MeterEff2 is effluent meter 2
(don't ask). I also have MeterG1 as gas meter 1 for
example. The table would read as follows for the first 5
fields.

tblWeeklyMeterReadings
Field1 Field2 Field3 Field4 Field5
Batch Date MeterW1 MeterW2 MeterS1 and so for 42
meters.

So each row is the meter readings for a week with the batch
as a unique identifier. Ineed the date in so that I can
search for meter usages between dates, can you suggest
another name.


Bill
-----Original Message-----
PMFJI,
How is your tblWeeklyMeterReadings related to MeterDetails? Does a single
record in tblWeeklyMeterReadings contain readings for 42 different meters or
42 different readings from the same meter. Either way, this is highly
un-normalized and should be restructured if possible.

I am reading "MeterW1" as "Week 1" but it may be something else since you
also have a [Date] field. BTW: Date is not a good name for a field since it
is a function name in Access.

--
Duane Hookom
Microsoft Access MVP


Jeff,

I have a table called MeterDetails that is similar to
your table [tblMeter]. I also have a table
WeeklyMeterReadings which has the following;

tblWeeklyMeterReadings
Batch
Date
MeterW1
MeterW2
Meter-?-
Meter-?+1-
to MeterEff2 42 meters in all.

I want to break up this WeeklyMeterReading table due to
not being able to query it very well due to its size. I
need to get a table of weekly consumptions out of it but
the query I have will only allow me to cover 15 meters.

Your second "table" starts trelMeterReading, what is trel?

Is there anyway to send you screen shots to show what I
am doing?

Bill
-----Original Message-----
Bill

Any time I see "repeating fields" (W1, W2, ..) I am reminded of Excel... so
your desire not to have repeating fields is a good idea!

I am confused by your follow-on statement, however, and your comment about
generating "a new table" for a new meter. If "repeating fields" are bad (in
a relational database, trust me, it's bad), then "repeating tables" are
worse!

Without more description of what you'd include, the following is just a
generic guess. See if it doesn't give you a way to record the facts you
need:

tblMeter
MeterID
MeterTag (?manufacturer serial number or ...?)
MeterType (water, gas, electric, ...)
DateAdded
DateRemovedFromService
ServiceLocation

trelMeterReading
MeterReadingID
MeterID (foreign key, from tblMeter)
ReadingDate
Reading

I included the initial xxxID fields in each in case there was no other
reasonable candidate for a unique row identifier.

Good luck!

Jeff Boyce
<Access MVP>

.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top