query on multiple fields of same type?

[Jebuz]

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

 

[Jeff Boyce]

If you are saying that your table has repeating fields (e.g., "VelocityA,
VelocityB, ..."), you have ... a spreadsheet! Since Access is a relational
database, you won't get good use of Access' features and functions if you
try to feed it spreadsheet 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]

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]

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

If "VelocityA" used to be a Column (e.g., in a spreadsheet), 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]

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.