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

 

[Doug]

I forgot to mention the Order Details table that collects the order
quantity or the analogy to mine would be to collect actual readings.

Your assessment is pretty accurate. Though, I don't think the
GaugeTypes table is necessary. We currently have 32 sites spread across
Texas. Each site is independant.

You basically note 2 options. One is code and the second is the drop
down menu like Northwind. I would prefer all Gauges (from tblGauges)
for that particular site show up automatically. This would rule out
your second option. I am interested in your first option of code. Can
you provide more details on this option?

Thanks,
Doug

 

[John Nurick]

Hi Doug,

As I said, this isn't very simple (and I probably wouldn't do it this
way). Every time the site or date on the main form changes, you have to
check whether there are records in the Readings table (i.e. records that
will be displayed onthe subform) for the site and date in question, and
if not, create them.

Probably the simplest way to do this is to ensure that the Readings
table has an index that will prevent multiple records being created for
the same gauge and date. Then, every time the site or date on the main
form changes, run a query to append the necessary records and let it
fail if they already exist.

So if there's

tblGauges
GaugeID - primary key
SiteID - foreign key into tblSites
other stuff

tblReadings
GaugeID - foreign key into tblGauges
SiteID - foreign key into tblSites
ReadingDate
ReadingValue
(primary key is GaugeID and ReadingDate)

and the main form frmSites has a combobox cboSite to select the site and
a textbox txtReadingDate for the date, the SQL of the query required
will be something like

INSERT INTO tblReadings GaugeID, SiteID, ReadingDate
SELECT tblGauges.GaugeID, tblGauges.SiteID
"#" & Forms!frmSites!txtReadingDate & "#" As ReadingDate
FROM tblGauges
WHERE tblGauges.SiteID = Forms!frmSites!cboSite;

Just create the query in the ordinary way, and save it as (say)
qryAppendSiteGaugeRecords.

Then when you need to ensure the records are created, use code like this
(probably in the AfterUpdate events of cboSite and txtReadingDate) to
execute the query and requery the subform:

Dim dbD as DAO.Database

Set dbD = CurrentDB()
dbD.Execute "qryAppendSiteGaugeRecords"
Me.SubformName.FOrm.Requery
Set dbD = Nothing

 

[Doug]

Thanks, I'll have to study this and give it a try.

Thanks, Doug