mods to Northwind DB - need assistance

[Doug]

[I have cross-posted this to m.p.a.gettingstarted and m.p.a.queries]

I have an application that I believe is very analogous to the Northwind
example DB (or any sales order db). The short version of what I am doing
is:
My technicians collect gauge readings daily from different mini-water
treatment plants. There is a site-specific form for each site that lists
each site specific parameter. The biggest difference between the Northwind
db and mine is the Products table. I want to change the Products tables to
a Parameters table. The parameters table will contain a list of the
different gauges/meters at each site. Each site will be a little different
in the number (30-60) and type of gauges/meters.

I haven't figured out yet how to modify the Order From (with subform) such
that instead of selecting the product you want to order, the list of site
specific parameters (30-60) shows up automatically in the subform.

I believe I need to modify and/or add a query and build the subform from
this query, but I haven't figured it out yet. Can anyone point me in the
right direction?

Analogy:
Northwind DB = My DB
Customers = Sites/Project
Orders = Field Visits
Products = Parameters

Thanks,
Doug

 

[John Nurick]

Hi Doug,

Good database design always starts with consideration of the real-world
entities you're modelling, and these aren't quite the same as in
Northwind. It sounds as if you have the following entities:

Sites (I take it a site is the same as a plant - or can you have more
than one plant on a site? if so, there's another entity).

GaugeTypes (or MeterTypes). The corresponding table (let's call it
tblGaugeTypes) will have one record for each different kind of gauge or
meter in your organisation.

Gauges (or Meters). Each Gauge is of a particular GaugeType and is
located at a particular Site. The corresponding table (tblGauges) will
contain one record for each Gauge in the organisation, i.e. 30 to 60
records per Site, and will be related to the tblSites and tblGaugeTypes.

Readings. Each reading is of one gauge at one point in time. tblReadings
will be related to tblGauges.

---

It's possible to set up a subform that would automatically display a
record for each gauge at the current site on the current day, but it
requires writing code that will execute a query to create these records
(first checking whether or not they already exist). It's much simpler to
set things up so that the subform displays (at first) a new record with
a dropdown list of all the gauges at that site. The user selects a
gauge, enters the reading, and goes on to the next one just like adding
items to the Northwind Order form.

[I have cross-posted this to m.p.a.gettingstarted and m.p.a.queries]

I have an application that I believe is very analogous to the Northwind
example DB (or any sales order db). The short version of what I am doing
is:
My technicians collect gauge readings daily from different mini-water
treatment plants. There is a site-specific form for each site that lists
each site specific parameter. The biggest difference between the Northwind
db and mine is the Products table. I want to change the Products tables to
a Parameters table. The parameters table will contain a list of the
different gauges/meters at each site. Each site will be a little different
in the number (30-60) and type of gauges/meters.

I haven't figured out yet how to modify the Order From (with subform) such
that instead of selecting the product you want to order, the list of site
specific parameters (30-60) shows up automatically in the subform.

I believe I need to modify and/or add a query and build the subform from
this query, but I haven't figured it out yet. Can anyone point me in the
right direction?

Analogy:
Northwind DB = My DB
Customers = Sites/Project
Orders = Field Visits
Products = Parameters

Thanks,
Doug