Trouble with relating three tables

Just thought I'd repost this and see if anyone can give me some advice....

I have one table Compl_Main_Tbl with unique Inspec_ID. We have a number of
"sites" that will be physically inspected by govt. agencies and we will
receive Satisfactory or Unsatisfactory assignments. All the specifics about
the inspection are in the main table (ie. Inspector_Name, Govt_Agency,
Enforcement_Level, Date_Issued).

Each "site" has a number of "locations" assigned to them (actually called
UWI). Any or all of the UWI's at that site could be inspected at once and
included in the one inspection. And they can be enforced for more than one
issue (ie. noise, emissions, staining....). So, I have the details of the
inspection in the Detail_Tbl and linked one-to-many to Compl_Main_Tbl.

Each UWI actually has been assigned a breakdown based on geographical area
and the company I work for has built two Oracle databases that I am supposed
to link into (because the areas will change). I am not able to change the
structure of these two tables.

One is called UWI_Corp_Hier and incl. these fields that I need:
District_Code, District_Name (Sub-District, Area, Field)

The other table is called UWI_Points and it includes these fields that I
need: Survey_Sys and UWI. The two tables are linked by the Field_Code.

What I need is to be able to type in the UWI and have the rest of the fields
from the UWI tables fill in. Then I need to add some data that is currently
in the Detail_Tbl.

I have built the main part of the database into a form and in the past I
have added a sub-form for detail information but, I am unsure how to do this
when I really have three tables that I need to work with. I was thinking
that maybe I could build a query based on the three tables and put that in
the sub-form but I'm not sure if this is appropriate. I also am not sure how
to get the other info to fill in; I've been searching the newsgroups and I
think I may need to make them combo boxes.

Thanks again!

"=?Utf-8?B?Y2FycmlleQ==?=" wrote in
:


I have one table Compl_Main_Tbl with unique Inspec_ID. We have a
number of "sites" that will be physically inspected by govt. agencies
and we will receive Satisfactory or Unsatisfactory assignments. All
the specifics about the inspection are in the main table (ie.
Inspector_Name, Govt_Agency, Enforcement_Level, Date_Issued).

Each "site" has a number of "locations" assigned to them (actually
called UWI). Any or all of the UWI's at that site could be inspected
at once and included in the one inspection. And they can be enforced
for more than one issue (ie. noise, emissions, staining....). So, I
have the details of the inspection in the Detail_Tbl and linked
one-to-many to Compl_Main_Tbl.

So far, I think you have a load more than three tables! As far as I can
tell, we are into (*=primary key, +=Foreign key):-

Inspections(*InspecID, InspectorName, etc)
// approx similar to Compl_Main_Tbl

InspectionDetails(*InspecID+, *LocationID+, IssueCode+, etc)

Sites() Locations(*LocationID, SiteNumber+, etc)

not to mention all the supporting stuff like Inspectors, IssueCodes,
EnforcementLevels and so on.

One is called UWI_Corp_Hier and incl. these fields that I need:
District_Code, District_Name (Sub-District, Area, Field)

The other table is called UWI_Points and it includes these fields that
I need: Survey_Sys and UWI. The two tables are linked by the
Field_Code.

What I need is to be able to type in the UWI and have the rest of the
fields from the UWI tables fill in. Then I need to add some data that
is currently in the Detail_Tbl.

If the data are really static, then you may be best off just importing
these as either one or two tables. I guess most Oracle database
designers know what they are doing, so it's pretty unlikely from your
description that they are in a genuine one-to-one relationship. You'll
need to see the actual schema and that will tell you how to join them.
Would the admin make you a View that you could link to, to avoid having
to import the data?



I have built the main part of the database into a form and in the past
I have added a sub-form for detail information but, I am unsure how to
do this


Please, don't even _think_ about the user interface until you have the
tables and relationship design down solid!

All the best


Tim F