Relationship

[Guest]

I'm trying to decide what the best relationship I should have. here's a
simplified analogy of what I have.

4 Possible combinations:
Cage is empty
Cage with ONE animal
Cage with ONE bowl
Cage with one animal AND one bowl

There are several fields in each table.
Tables: Bowl, Cage, Dog, Cat, Bird etc..

In the ANIMAL and 'Bowl' table, one of the fields is "Cage Number". "Cage
Number" is the primary key in the 'Cage' table.

Not all animals belong in a cage (some are sold)
Not all bowls belong in a cage (some are in stock)

What kind of relationship should I have between the tables (on the "Cage
Number" field) to ensure that only the four possible combinations listed
above can exist while keeping in mind that a form is need for data entry.
Any help would be muchly appreciated
TIA

 

[Guest]

Hafeez,

It is difficult to advise you based on what you've given us so far. Please
post the structures of your tables and a generic description of what you're
using the database application to do, and what kinds of inputs and outputs
you want.

Sprinks

 

[Guest]

Hi Sprinks,

I've spent a lot of time trying to figure it out, and it pretty much boils
down to one question.

I have a field "CableID" in a table. I want it to be limited to a list (in
another table), make it optional (so you don't have to enter something in if
you don't want to) and I want to ensure that there cannot be any duplicates,
but multiple records containing a blank "CableID" field is allowed.

Example of something I want:
rec#, ........., CableID
1 ............... A15
2 ............... A16
3 ...............
4 ...............
5 ............... A11

 

[Guest]

Hafeez,

You can’t do it with referential integrity because if you create an index
with Uniqueness specified, it will only permit a single blank value.

I think the best way to do it is to use a query for the combo box RowSource
that only shows those values which have not already been used. You will, of
course, want to requery the combo box in its AfterUpdate and in the form’s
OnCurrent event.

Let’s call the other table, where all the cable ID’s are listed CableMaster,
and your second table Main. The SQL for the RowSource would then be:

SELECT CableMaster.CableID
FROM CableMaster
WHERE (([CableMaster].[CableID]) Not In (SELECT Main.CableID FROM Main));

Obviously, since this approach uses a form control to effect the integrity,
you cannot permit users to access the table directly--where they would be
permitted to enter any value they want, even one not in the master table.
You should, however, habitually prevent them from doing so anyway.

Hope that helps.
Sprinks

 

[Guest]

Thanks for the prompt reply Sprinks!
I've got one last question! (for now ;-))
Is there a way for me to prevent users from entering data in the table (if
so how) or is it just hiding the database window under a form that loads up

Hafeez,

You can’t do it with referential integrity because if you create an index
with Uniqueness specified, it will only permit a single blank value.

I think the best way to do it is to use a query for the combo box RowSource
that only shows those values which have not already been used. You will, of
course, want to requery the combo box in its AfterUpdate and in the form’s
OnCurrent event.

Let’s call the other table, where all the cable ID’s are listed CableMaster,
and your second table Main. The SQL for the RowSource would then be:

SELECT CableMaster.CableID
FROM CableMaster
WHERE (([CableMaster].[CableID]) Not In (SELECT Main.CableID FROM Main));

Obviously, since this approach uses a form control to effect the integrity,
you cannot permit users to access the table directly--where they would be
permitted to enter any value they want, even one not in the master table.
You should, however, habitually prevent them from doing so anyway.

Hope that helps.
Sprinks

Hi Sprinks,

I've spent a lot of time trying to figure it out, and it pretty much boils
down to one question.

I have a field "CableID" in a table. I want it to be limited to a list (in
another table), make it optional (so you don't have to enter something in if
you don't want to) and I want to ensure that there cannot be any duplicates,
but multiple records containing a blank "CableID" field is allowed.

Example of something I want:
rec#, ........., CableID
1 ............... A15
2 ............... A16
3 ...............
4 ...............
5 ............... A11

 

[Guest]

Hafeez,

As I work in a small office where a high degree of trust exists, I have only
used the "hiding the database under the main form" approach, and close the
database in the form's OnClose event. If a user holds down the Shift key as
the database opens, he is able to bypass this security measure, since the
database will open in database view.

Access comes, however, with security features built-in, and I believe they
are now relatively straightforward to implement. I suggest you post a thread
on the Security newsgroup. Also, before attempting to implement Security, be
sure to make a backup copy of your database to avoid accidentally locking
yourself out!

Sprinks

Thanks for the prompt reply Sprinks!
I've got one last question! (for now ;-))
Is there a way for me to prevent users from entering data in the table (if
so how) or is it just hiding the database window under a form that loads up

Hafeez,

You can’t do it with referential integrity because if you create an index
with Uniqueness specified, it will only permit a single blank value.

I think the best way to do it is to use a query for the combo box RowSource
that only shows those values which have not already been used. You will, of
course, want to requery the combo box in its AfterUpdate and in the form’s
OnCurrent event.

Let’s call the other table, where all the cable ID’s are listed CableMaster,
and your second table Main. The SQL for the RowSource would then be:

SELECT CableMaster.CableID
FROM CableMaster
WHERE (([CableMaster].[CableID]) Not In (SELECT Main.CableID FROM Main));

Obviously, since this approach uses a form control to effect the integrity,
you cannot permit users to access the table directly--where they would be
permitted to enter any value they want, even one not in the master table.
You should, however, habitually prevent them from doing so anyway.

Hope that helps.
Sprinks

Hi Sprinks,

I've spent a lot of time trying to figure it out, and it pretty much boils
down to one question.

I have a field "CableID" in a table. I want it to be limited to a list (in
another table), make it optional (so you don't have to enter something in if
you don't want to) and I want to ensure that there cannot be any duplicates,
but multiple records containing a blank "CableID" field is allowed.

Example of something I want:
rec#, ........., CableID
1 ............... A15
2 ............... A16
3 ...............
4 ...............
5 ............... A11


:

Hafeez,

It is difficult to advise you based on what you've given us so far. Please
post the structures of your tables and a generic description of what you're
using the database application to do, and what kinds of inputs and outputs
you want.

Sprinks

:

I'm trying to decide what the best relationship I should have. here's a
simplified analogy of what I have.

4 Possible combinations:
Cage is empty
Cage with ONE animal
Cage with ONE bowl
Cage with one animal AND one bowl

There are several fields in each table.
Tables: Bowl, Cage, Dog, Cat, Bird etc..

In the ANIMAL and 'Bowl' table, one of the fields is "Cage Number". "Cage
Number" is the primary key in the 'Cage' table.

Not all animals belong in a cage (some are sold)
Not all bowls belong in a cage (some are in stock)

What kind of relationship should I have between the tables (on the "Cage
Number" field) to ensure that only the four possible combinations listed
above can exist while keeping in mind that a form is need for data entry.
Any help would be muchly appreciated
TIA

 

[Guest]

Hi Hafeez.

This is easy to do in an Access MDB, but harder in SQL Server.

In Access, define a unique index on the field CableID, make sure Required is
set to No and make sure there is no default value set.

That way, Access will happily let you define new records with no CableID
value, but also ensure that if you do enter a value, then it must be unique.

The "gotcha" here is that most people forget to remove the Default Value if
there is one, so if your foreign key field is numeric it defaults to zero. A
unique index won't allow two zero values, but it will allow two null values.

SQL Server is less forgiving of nulls, so if you're writing an Access
Project it's harder to implement these optional-one-to-many relationships.

Regards,