Indexes and referential integrity

I have 6 tables. All 6 tables have the arrow and asterisk at the bottom,
meaning they are editable and you can add records. I have autonumber primary
keys and I have indexes on each table on the fields that I am going to join.
Each field that I am going to join is set to long integer. When I go to set
relationships, 3 of the tables won't let me check referential integrity
because it says I don't have unique indexes. If I make queries out of all 6
tables individually, they are all editable (have the arrow and asterisk at
the bottom.) If I bring the 3 tables into the query that have referential
integrity, the arrow and asterisk are there. If I bring any of the 3 tables
into my query that wouldn't let me set referential integrity then the query
is not editable. What do I need to do? Thanks!!!!

First thing to check is that you are joining from the Primary Key field of at
least one of the tables (for a 1-M) or the PKs in both tables (for a 1-1). If
you have an autonumber PK for each table, and are joining on this PK field,
that shouldn't be the problem.

Next there must be a matching record in the parent table in the PK field for
each record in the foriegn key field of the child table.

For example every sale much have a customer. If someone created a sale in
the Sales table, but forgot to enter the Customer ID, you can't set up RI
between the Customer and Sales tables.

This is probably the problem. Try doing an unmatched query to look for FKs
without a matching PK.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Golfinray" wrote:

I have 6 tables. All 6 tables have the arrow and asterisk at the bottom,
meaning they are editable and you can add records. I have autonumber primary
keys and I have indexes on each table on the fields that I am going to join.
Each field that I am going to join is set to long integer. When I go to set
relationships, 3 of the tables won't let me check referential integrity
because it says I don't have unique indexes. If I make queries out of all 6
tables individually, they are all editable (have the arrow and asterisk at
the bottom.) If I bring the 3 tables into the query that have referential
integrity, the arrow and asterisk are there. If I bring any of the 3 tables
into my query that wouldn't let me set referential integrity then the query
is not editable. What do I need to do? Thanks!!!!

On Mon, 7 Apr 2008 09:05:04 -0700, Jerry Whittle
wrote:

First thing to check is that you are joining from the Primary Key field of at
least one of the tables (for a 1-M) or the PKs in both tables (for a 1-1). If
you have an autonumber PK for each table, and are joining on this PK field,
that shouldn't be the problem.

I've got to disagree with you there, Jerry, or at least clarify what probably
wasn't your intent: you cannot join an Autonumber to another Autonumber, only
to a Long Integer foreign key field.
--

John W. Vinson [MVP]

On Mon, 7 Apr 2008 08:55:00 -0700, Golfinray
wrote:

I have 6 tables. All 6 tables have the arrow and asterisk at the bottom,
meaning they are editable and you can add records. I have autonumber primary
keys and I have indexes on each table on the fields that I am going to join.
Each field that I am going to join is set to long integer. When I go to set
relationships, 3 of the tables won't let me check referential integrity
because it says I don't have unique indexes. If I make queries out of all 6
tables individually, they are all editable (have the arrow and asterisk at
the bottom.) If I bring the 3 tables into the query that have referential
integrity, the arrow and asterisk are there. If I bring any of the 3 tables
into my query that wouldn't let me set referential integrity then the query
is not editable. What do I need to do? Thanks!!!!

DON'T try to make One Great Master Query that does everything - and don't do
any editing in table or query datasheets, at all. That's not their function!

Instead create a Form for the "one" side and one or more Subforms for the
"many" side tables. Multitable queries can be made editable but you'll see
duplicate records (each "one" side record will be displayed as many times as
there are "many" side records), missing records (if there is no matching
value) - it can get really confusing and hard to work with. Subforms make life
much easier!
--

John W. Vinson [MVP]

Thanks a bunch John and Jerry. I got tired of messing with it and I followed
John's advice. I made a mainform and subforms and it mostly works great. I've
got to tweak a little more. Don't we always! Thanks!!! Golfinray

"John W. Vinson" wrote:

On Mon, 7 Apr 2008 08:55:00 -0700, Golfinray
wrote:

I have 6 tables. All 6 tables have the arrow and asterisk at the bottom,
meaning they are editable and you can add records. I have autonumber primary
keys and I have indexes on each table on the fields that I am going to join.
Each field that I am going to join is set to long integer. When I go to set
relationships, 3 of the tables won't let me check referential integrity
because it says I don't have unique indexes. If I make queries out of all 6
tables individually, they are all editable (have the arrow and asterisk at
the bottom.) If I bring the 3 tables into the query that have referential
integrity, the arrow and asterisk are there. If I bring any of the 3 tables
into my query that wouldn't let me set referential integrity then the query
is not editable. What do I need to do? Thanks!!!!

DON'T try to make One Great Master Query that does everything - and don't do
any editing in table or query datasheets, at all. That's not their function!

Instead create a Form for the "one" side and one or more Subforms for the
"many" side tables. Multitable queries can be made editable but you'll see
duplicate records (each "one" side record will be displayed as many times as
there are "many" side records), missing records (if there is no matching
value) - it can get really confusing and hard to work with. Subforms make life
much easier!
--

John W. Vinson [MVP]

John,

2 Questions on your comment on not doing editing in query datasheets. The
first is "why not? The second is, we do a lot of databases work where the
users know enough to do many "one time only" multi-field sorts and
multi-field filters/conditions in the query design grid. For example
"show me a those records in a table that have "laser" in the description and
with part numbers that start with "9901". Is there a way they can still do
this if they follow your advice and don't edit in the query? (other than
changing the grid of the query behond the form, and then editing data through
the form?

Thanks

Fred



"John W. Vinson" wrote:

On Mon, 7 Apr 2008 08:55:00 -0700, Golfinray
wrote:

I have 6 tables. All 6 tables have the arrow and asterisk at the bottom,
meaning they are editable and you can add records. I have autonumber primary
keys and I have indexes on each table on the fields that I am going to join.
Each field that I am going to join is set to long integer. When I go to set
relationships, 3 of the tables won't let me check referential integrity
because it says I don't have unique indexes. If I make queries out of all 6
tables individually, they are all editable (have the arrow and asterisk at
the bottom.) If I bring the 3 tables into the query that have referential
integrity, the arrow and asterisk are there. If I bring any of the 3 tables
into my query that wouldn't let me set referential integrity then the query
is not editable. What do I need to do? Thanks!!!!

DON'T try to make One Great Master Query that does everything - and don't do
any editing in table or query datasheets, at all. That's not their function!

Instead create a Form for the "one" side and one or more Subforms for the
"many" side tables. Multitable queries can be made editable but you'll see
duplicate records (each "one" side record will be displayed as many times as
there are "many" side records), missing records (if there is no matching
value) - it can get really confusing and hard to work with. Subforms make life
much easier!
--

John W. Vinson [MVP]

On Mon, 7 Apr 2008 13:29:01 -0700, Fred
wrote:

John,

2 Questions on your comment on not doing editing in query datasheets. The
first is "why not? The second is, we do a lot of databases work where the
users know enough to do many "one time only" multi-field sorts and
multi-field filters/conditions in the query design grid. For example
"show me a those records in a table that have "laser" in the description and
with part numbers that start with "9901". Is there a way they can still do
this if they follow your advice and don't edit in the query? (other than
changing the grid of the query behond the form, and then editing data through
the form?

I prefer not to let endusers see either datasheets OR the query grid. There's
just too much damage that people with "a little knowledge" can do! If you
trust your users to (say) never delete master table records, or change the
values of primary keys, or creatively edit foreign key values, then you've got
a pretty good class of users.

A useful Form should certainly have builtin search tools to allow users to
easily select the desired record for editing, using whatever search criteria
are meaningful and useful for your particular database. A "Query by Form"
search is very flexible indeed, if you can't limit the scope of searches in
other ways.

But... whatever works in your shop is fine with me!
--

John W. Vinson [MVP]

Caught me! I meant primary key to primary key for a 1-1. Autonumber to
autonumber would be quite a trick.

Thanks for keeping me straight.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"John W. Vinson" wrote:

On Mon, 7 Apr 2008 09:05:04 -0700, Jerry Whittle
wrote:

First thing to check is that you are joining from the Primary Key field of at
least one of the tables (for a 1-M) or the PKs in both tables (for a 1-1). If
you have an autonumber PK for each table, and are joining on this PK field,
that shouldn't be the problem.

I've got to disagree with you there, Jerry, or at least clarify what probably
wasn't your intent: you cannot join an Autonumber to another Autonumber, only
to a Long Integer foreign key field.
--

John W. Vinson [MVP]

Hello John,

Thanks for the response, it is very informative in may ways, including
learning that I should learn about "query by form"

I've done a lot of DB work but all for a narrower-than-typical set of users
who I have use query grids without incident, and forms where it helps them
(dislay one-to-many data etc.) So I'm very weak on putting such
functionality in forms. Your thoughts help get me pointed in the right
direction of what I need to learn / do.

Sincerely,

Fred





"John W. Vinson" wrote:

On Mon, 7 Apr 2008 13:29:01 -0700, Fred
wrote:

John,

2 Questions on your comment on not doing editing in query datasheets. The
first is "why not? The second is, we do a lot of databases work where the
users know enough to do many "one time only" multi-field sorts and
multi-field filters/conditions in the query design grid. For example
"show me a those records in a table that have "laser" in the description and
with part numbers that start with "9901". Is there a way they can still do
this if they follow your advice and don't edit in the query? (other than
changing the grid of the query behond the form, and then editing data through
the form?

I prefer not to let endusers see either datasheets OR the query grid. There's
just too much damage that people with "a little knowledge" can do! If you
trust your users to (say) never delete master table records, or change the
values of primary keys, or creatively edit foreign key values, then you've got
a pretty good class of users.

A useful Form should certainly have builtin search tools to allow users to
easily select the desired record for editing, using whatever search criteria
are meaningful and useful for your particular database. A "Query by Form"
search is very flexible indeed, if you can't limit the scope of searches in
other ways.

But... whatever works in your shop is fine with me!
--

John W. Vinson [MVP]