Re: query on multiple fields of same type?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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





.

Quantcast