Re: query on multiple fields of same type?

My real world problem is this. I have multiple physical sites, at which
each has a monitor device. Each monitor has ports for sensors, up to 5
total. The sensors can be level or velocity or h2s etc. The monitors get
taken out & repaired or replaced entirely as well as do the sensors. I need
to track those changes to be able to see which are "problem"
monitors/sensors.

Originally (with repeating VelA, VelB, LevelA, etc) it worked great until I
decided it would be nice to show monitors w/ their connected sensors and the
sensor type.

I understand seperating each into a "velocityA" table is correct but then
it's far too complex to ask "what are the past configs of sensors this
monitor had?" Then it's multiple conditions of 5+ sensor tables dates'
fields!

In short I am having a terrible time designing the tables for monitors and
sensors and how I can relate them, maintain history and query them easily and
efficiently. I'v tried almost every approach i can think of.

Thanks for all your help so far, it's been very helpful. I am still playing
around with the design of it and trying to decide on one that I think is
right, or the most correct for my purposes.

"Jeff Boyce" wrote:

I'm having a little trouble visualizing your proposed table structure...

If "VelocityA" used to be a Column (e.g., in a spread***), I'll guess that
you'd need to have a table with two fields to "replace" it.

One field is for something like "VelocityType" (whatever A, B, ...
represent), and a second field for the value associated with that Type.

If you are also looking for a "history", you might add a third field for
something like "GoodUntilDate" or whatever is the appropriate term. That
way, your table could hold multiple records about something's VelocityType
and value, each for a different end-date.

Again, I'm still not real clear, so you might need a date range rather than
an expiration date. Either way, having a history table implies having
multiple records for the same configuration, the only difference being the
date-related info (i.e., history info).

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jebuz" <Jebuz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B16184CA-A5B8-4C92-A043-FF0DD8908D93@xxxxxxxxxxxxxxxx
So I was reading on normalization and now am guessing I have to make the
table into something like this.

tables: Monitors, Sensors, Monitor_Configuration

Monitors has serial #, Model etc.

Monitor_Configuration has fkMonitorSerial, fkSensorSerial, Port (where
port
is now field with data text such as VelocityA, VelocityB etc.)

Sensors has Serial, type, model etc.

The only problem is I need to also keep a history of past monitors and
thier
configurations. So should I also use a seperate table to store records of
changes to the table Monitor_configuration.??

"Jeff Boyce" wrote:

If you are saying that your table has repeating fields (e.g., "VelocityA,
VelocityB, ..."), you have ... a spread***! Since Access is a
relational
database, you won't get good use of Access' features and functions if you
try to feed it spread*** data.

Consider looking into relational database design and normalization. It
will
take a bit of work, but it will make using Access much easier.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Jebuz" <Jebuz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E710B911-A658-481D-B36D-E6994F133598@xxxxxxxxxxxxxxxx
Hi,

I have 2 tables, "monitor_configurations" & "Sensors"

Monitor_configurations has fields :VelocityA, VelocityB, VelocityC all
text
type storing the serial of the sensor that is plugged into the monitor
at
that port.

Sensors has fields: serial, type both text with all the sensors serials
and
the type of sensor it is in the type field.

I need to join these to show something like: Monitor X with Velocity A
=
035ff25 type "Doppler", Velocity B = 093hh56 type "submerged" etc. for
each
monitor.

I can't seem to create a SQL query to join the type field correctly
with
the
monitor's multiple fields, because each field in Monitor_configurations
needs
a type next to it... ?? *sigh* or is my DB design not gonna work for
that
or is it just plain done wrong?

Thx in advance!!!






.