Re: Normalizing Question

My solution has a continuous subform which provides multiple text boxes. I
guess if you can't get users to enter data in that style of form, my next
step would be to create a large unbound form with a grid of text boxes. It
would take a fair amount of code to fill the text boxes from a recordset and
then push the values back out to normalized tables when finished entering.
--
Duane Hookom
MS Access MVP

"RMires" <RMires@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:32ACDA2D-B64F-4C0B-A1AD-5413A0F5DEE5@xxxxxxxxxxxxxxxx
I think this gets down to the crux of the problem I've had reguarding every
step of this database. I would like to end up with a form that has a
textbox
for EVERY gauge. This may be problematic for the database but would make
it
significantly (to the extreme) more user friendly. I already did this
using
multiple tables for each piece of equipment, but the form couldn't handle
this many sources at one time. I can see that I should have used one table
from the start, with Date and Equip as fields, but I do not see how to
show
all of the readings for one day on one record for the form. My goal would
be
for the end user to be able to pull up the form, enter all of the data for
that date without selecting boxes or changing records, and also to be able
to
go back and select all of the readings for a particular date BY that date.

"Duane Hookom" wrote:

Your subform would be a continous subform based on tblGaugeReadings. I
think
you would need a date field "garReadingDate" in tblGaugeReadings. Your
main
form "frmReadingUpdate" would have a combo box "cboEquID" that would
allow
you to select the equEquID value. You would also have a text box
"txtReadingDate" to enter the reading date.

Create an append query that:
- selects gauGauID and Forms!frmReadingUpdate!txtReadingDate
- from tblGauge
- into tblGaugeReadings!garGauID, garReadingDate
- where gauEquID = Forms!frmReadingUpdate!cboEquID

Set the Link Master/Child of the subform contol to:

Link Master: cboEquID, txtReadingDate
Link Child: garGauID, garReadingDate

Add a command button on the main form that runs the append query. This
should display the new blank records in your subform for the equipment
selected in the combo box and the date entered into the text box.
--
Duane Hookom
MS Access MVP


"RMires" <RMires@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2BDD2C3C-BB1A-4305-942D-ABF71AC078BD@xxxxxxxxxxxxxxxx
You said to come back if I had questions about the form...and boy do I
!
I am trying to create a form with all of the points on it for a day. I
can
create the text boxes but if I use garValue (as in your example) as the
control source, how do I use that one field for many text boxes
(gauges)?

"Duane Hookom" wrote:

I mostly agree with Duncan from Ole Hansen and Sons, Inc. I would have
a
minimum of three tables:

tblEquipment
============
equEquID autonumber primary key
equEquipment text name of equiptment

tblGauge
===========
gauGauID autonumber primary key
gauGauge text name of guage
gauEquID long integer links to tblEquipment.equEquID

tblGaugeReadings
============
garGaRID autonumber primary key
garGauID long integer links to tblGuage.guaGuaID
garValue actual reading value

All of these autonumbers in my opinion and experience speaking.
Whether
you
want to use a good natural key is up to you.

If you would have trouble creating forms or reports for this, come
back
with
additional questions.
--
Duane Hookom
MS Access MVP

"Duncan Bachen" <dbachen@olehansen_dot_com> wrote in message
news:%23vq2VTUZGHA.3704@xxxxxxxxxxxxxxxxxxxxxxx
RMires wrote:
I have a question on normalizing tables that has come up several
times.
As an example:

If I have ten pieces of equipment with ten guages and I want to
record
the data on the gauges every day, should I create one table with
100
fields:

*gack* Definately not. You should rarely need more than 25-30 fields
(at
most with a few exceptions)

equip1gauge1 equip1gauge2 day1
day2

or 10 tables with 10 fields:

I wouldn't do this either. You need to think in even smaller terms
for
your normalization.


tblEquip1
gauge1 gauge2 gauge 3
day1 day2

Is there a standard for this or does it just depend on the
application?

Your normalized table would be 1 table, with 4 fields.

Table: tblGaugeReading
Fields:
ReadingID (an autonumber to uniquely identify every reading)
EquipmentID (a long, which has a relationship to your Equipment
table)
GaugeID (a long, which has a relationship to your Gauge Table)
Reading (the actually reading value, either a string, long, int,
whatever).
(Additionally, add things like ReadingDate, EmployeeID [who read
it])

Alternately, GaugeID could just store the gauge number itself, and
not
be
linked to another table. But if you had a Gauge table, you'd be able
to
define the kind of gauge it is, or whatever additional information
you
needed. If you don't need it, don't store it.

This way, every Equipment has one record in the record table, and
every
reading has one record in the reading table. If you did it your way,
you'd
wind up with a lot of blanks for equipment that doesn't have 10
gauges.
Even *if* every piece of equipment has 10 gauges, you should
consider
single field storage like this


--
-D
Duncan Bachen
Director of I.T., Ole Hansen and Sons, Inc.








.