Creating a new table from information in a form??

[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

 

[Jeff Boyce]

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>

 

[Duane Hookom]

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>

.

 

[Bill Doodson]

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>

.

.