Query Relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am unable to make a relationship with any kind of referential integrity,
etc. to a query I need on a permanent basis. In fact, it doesn't even name
the type of relationship (one to many, etc.). Can you only make relationships
query to query, or can you link them to a table?
 
Mrs. Ellis said:
I am unable to make a relationship with any kind of referential integrity,
etc. to a query I need on a permanent basis. In fact, it doesn't even name
the type of relationship (one to many, etc.). Can you only make relationships
query to query, or can you link them to a table?
Click to expand...

Relationships are between tables.

What are you doing? Show your query and explain what you mean by
relationships between queries/tables in that query.
 
Thank you for your help-
I am very new at Access and really the only information I have is a
beginner's guide book. My query is based on an equipment table that contains
an ID field, make, model, location, etc. as well as a date for its next
scheduled maintenance. The query I'm referring to retrieves all of the
equipment due for maintenance on a certain date. I also have another table
that serves as a work order log, and yet another table to track labor hours.
They are related as follows: The work order log and the hours log are related
by primary key WorkOrder# in a 1-1 relationship with ref. int. enforced and
Cascade update. The equipment table and the work order log have a 1-many
relationship with ref. int. enforced and update by the key Equip#. I am
trying to ultimately create a form that will use the query results as the
basis for a new work order record, with new related records in the labor
hours table. So far my attempts with subforms have been fruitless, because no
matter the combination of relationships I use, I am unable to get either the
labor log to reference or associate with the work order number, or the query
results to associate with a work order number. Will I have to run the query,
and enter the results manually into a new work order? In either case, how can
I fix the reference issue between the labor log and the work order? Most of
the time I have more than one mechanic on more than one date working on one
order, and I need to associate the labor with a specific work order.
 
I had another thought after this post, which was would a make-table query be
a solution to this problem? I thought if the query made a table of my
results, I could then make a form and subsequent subform to use in my work
order form. If I added additional fields to the table made from the query,
like a Work Order # key to make a relationship with my other tables, would
those fields remain in place when I ran the query for the next date, or would
they disappear or assign the wrong number to the wrong order?
 
Thank you for your help-
I am very new at Access and really the only information I have is a
beginner's guide book. My query is based on an equipment table that contains
an ID field, make, model, location, etc. as well as a date for its next
scheduled maintenance. The query I'm referring to retrieves all of the
equipment due for maintenance on a certain date. I also have another table
that serves as a work order log, and yet another table to track labor hours.
They are related as follows: The work order log and the hours log are related
by primary key WorkOrder# in a 1-1 relationship with ref. int. enforced and
Cascade update.
Click to expand...

One to one relationships are VERY rare and in this case would seem
completely inappropriate. Does each work order have *one and only one,
never any more* entry in the hours log? Surely this should be a one to
many!
The equipment table and the work order log have a 1-many
relationship with ref. int. enforced and update by the key Equip#. I am
trying to ultimately create a form that will use the query results as the
basis for a new work order record, with new related records in the labor
hours table. So far my attempts with subforms have been fruitless, because no
matter the combination of relationships I use, I am unable to get either the
labor log to reference or associate with the work order number, or the query
results to associate with a work order number.
Click to expand...

The main form should be based on your Work Order table; the subform on
the Log table; and the Master Link Field should be the unique Work
Order Number in the workorder table, and the Child Link Field the work
order number in the log table. This field should NOT be the log
table's primary key; it should be a separate field.

It is NOT necessary to create a relationship between queries or
between the table and a query to do this. Creating a relationship
between the work order table and the log table (a correct
relationship, which I fear you do not now have!) is enough; it will
make Access provide that relationship by default when you do create a
query or add a subform to a form.

Will I have to run the query,
and enter the results manually into a new work order? In either case, how can
I fix the reference issue between the labor log and the work order? Most of
the time I have more than one mechanic on more than one date working on one
order, and I need to associate the labor with a specific work order.
Click to expand...

No. You do NOT need to do this.

Take a look at the Northwind sample database to see how subforms work.
They're much less complicated than you're making them out to be!

John W. Vinson[MVP]
 
Back
Top