Table Query!

[Bob]

If I have a table that has 2 Columns HorseID, OwnerID
which contain Unique Id numbers for both Horses and Clients, Is there a way
that I can arrange that if HorseID dose not have a Unique OwnerID number it
will not save in table

Thanks in advance.........Bob Vance

 

[strive4peace]

yes, you can make a UNIQUE index on the combination of
HorseID and OwnerID in the table design

From the table design, Turn on the Indexes window (from the
menu: View, Indexes)

click on the HorseID field in the table design and set the
Index property to
Yes (no duplicates)

that will add a line to the Indexes window

In the row just below the index you just made, in the 2nd
column of the indexes window, click in the fieldname column
and choose the OwnerID field

this will set a unique index on that combination

you will, of course, need to handle errors if you try to add
another owner for a horse in a form. For append queries,
there is nothing you need to do -- the duplicates simply
won't go in.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Pat Hartman\(MVP\)]

A table with just these two fields doesn't make any sense based on your
question. If a horse can have only one owner, the OwnerID belongs in the
table that defines a horse. If a horse can have multiple owners, which is
more likely, then you can't define a unique index on HorseID in this table,
you need a compound index or primary key that includes BOTH columns. If you
need to keep historical records, then the horse/owner relationship table
also needs from and through dates since the owners can own a horse for a
period of time and then sell it and repurchase it. In this case a third
field (FromDate) needs to be included in the index/primary key.

 

[strive4peace]

Good catch, Pat!

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Bob]

The ownerId is a drop down Listbox that may have 4 owners at 25% , there are
many other fields on the form....Regards Bob

 

[strive4peace]

Hi Bob,

in that case, this is incorrect:
"If I have a table that has 2 Columns HorseID, OwnerID..."

you should also have OwnerInterest in this table


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*