relationship issue - query?

[carriey]

I have two tables (Main and Detail) which have a one-to-many relationship. I
need to tie into some company information and have been given links to two
tables of company data that are in Oracle and will change.

I have set up a one-to-many relationship between the two company tables.

What I need to happen is that when the user is in the detail section of the
form, they will type in (or select from a drop down list) the location (which
is in one company table) and the rest of the data from these two tables will
automatically fill in. They will then need to fill in some other data.

I am not sure how to get all the data to fill in but thought that I may need
to create a query including all the fields from the Detail Table and the two
company tables. Am I on the right track?

Thanks in advance

 

[John Vinson]

I have two tables (Main and Detail) which have a one-to-many relationship. I
need to tie into some company information and have been given links to two
tables of company data that are in Oracle and will change.

I have set up a one-to-many relationship between the two company tables.

What I need to happen is that when the user is in the detail section of the
form, they will type in (or select from a drop down list) the location (which
is in one company table) and the rest of the data from these two tables will
automatically fill in. They will then need to fill in some other data.

I am not sure how to get all the data to fill in but thought that I may need
to create a query including all the fields from the Detail Table and the two
company tables. Am I on the right track?

It would be simplest to use a Form based on the company table, with a
Subform based on the Detail table. Use the linking field (CompanyID) as the
subform's master/child linking field, and put a Combo Box on the form using
the combo wizard to look up a company using the company name.

This can all be done without the wizards - post back if you want step by
step, though the wizards really do a decent job.

You should *not* be using table datasheets for this purpose. They're much
too limited in their capabilities.

John W. Vinson/MVP

 

[carriey]

Thanks for your response. I think I need to provide more detail - my Main
Table actually contains information about the specific record (which happens
to be Inspections). There is information about the Inspection there (such as
the agency, inspection type....)

The reason I wanted the info from the two company tables (actually a
hierarchy which breaks down locations by District/Sub-District....) is that
there can be a number of locations that were inspected and found satisfactory
or unsatisfactory on one inspection. Each of those that are unsatisfactory,
need to be closed but may be closed on different dates. The details for each
location is in my Detail Table.

I'm wondering if my Main Form (based on Main Table) contains a Sub-Form
based on the Detail Table (linked by Inspec_ID), can I somehow link those two
company tables into the Sub-Form as well? If the Combo box works, must I
actually be pulling my Sub-Form from a query that includes, Detail Table,
Company Table 1, Company Table 2?

 

[John Vinson]

I'm wondering if my Main Form (based on Main Table) contains a Sub-Form

based on the Detail Table (linked by Inspec_ID), can I somehow link those two
company tables into the Sub-Form as well? If the Combo box works, must I
actually be pulling my Sub-Form from a query that includes, Detail Table,
Company Table 1, Company Table 2?

I'm not certain how your company information is stored. You have two
*company tables*? Do you have two tables *for each company*, and when you
have to deal with a new company you create two more tables? If so, your
database structure could be markedly improved by using just *two tables* in
all, a master table and a detail table, with a CompanyID. Storing data in
table names is a VERY bad idea.

If I'm misunderstanding please post back; I'll be travelling tomorrow so you
might want to post a description of your table structure as a new thread so
someone else can answer, or I'll reply on Wednesday if I see a reply here.

John W. Vinson/MVP

 

[carriey]

Hi John, thanks for being so patient, I don't think I'm doing a very good
job explaining this but, I'll try again. I hope this isn't too long

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!