Unique Index

[Guest]

Im trying to define relationships for my tables but I keep getting a message
stating no unique index is found for the referenced table when I click
referential integrity.

Basically I have two tables:

Table #1 consists of Team ID and Date
Table #2 consists of Team ID, Date, and Sales Price

Im trying to join Date together.

In defining relationships you have to join rows from the tables that are
equal to each other? Like date and date?

Thx

 

[Douglas J Steele]

It doesn't make sense to me to join those two tables on Date. It looks to me
as though you should be joining them on the two fields Team ID and Date.
Presumably the combination of those two fields is the Primary Key for Table
#1.

 

[Guest]

Access will only allow one-to-one or one-to-many relationships. One-to-many
are far more typical. In this case, the table on the 'one' side of the
relationship needs to have the column you are joining set as a unique index.
This means that there are no duplicated value in that column. This is usually
the table's primary key column. It is highly recommended that every table
have a primary key, even though Access doesn't require it. This is the cause
of your error message.

Also, yes, the fields on both sides of the relationship need to be of the
same data type.

Barry

 

[Guest]

what Barry said was correct. Note that "same data type" does allow that a
field of "autonumber" type can be joined to a field of "long integer" type.
If you use "autonumber" primary keys, you need to use this kind of
relationship because you will need to have Access create matching values the
the "long integer" field. Two autonumber fields could be joined to each
other, but the usual result would be at best random garbage.

 

[Guest]

Ok. How do I go about dealing with the unique index message when setting
referential integrity for joing Date with Date between the two tables?

Thx

 

[Guest]

Im trying to join "Date" on one table with "Date" on another but I still get
"Indeterminate" relationship type. The data type is the same between the two
tables. What do you think is the problem?

Does "Date" have to be a primary key from the one side?

 

[Douglas J Steele]

Why are you trying to related them? It doesn't make any sense to!

If it were possible (I don't believe it is), what benefit do you think
you'll realize?

 

[John Spencer]

One of the Date fields has to be unique (must have an index set to unique
values). The field does not have to be the primary key for the table.

To have a one to many relationship, one side of the relationship must have
unique values.

 

[Guest]

Im trying to create a DB that I can input data into regarding sales
transactions. I have one table that has unique Team ID's. I have another
table that will have applicable information for that particular Team ID (such
as Date, Tickets, etc.) What I want to do is be able to scroll thru records
and have applicable information come up from the joined tables. I assume I
have to use a query for the two tables and use it as a record source?

When scrolling thru the records I want to be able to input data onto the
form as well that is applicable.

What route should I take in making the DB in your opinion?

Thx

 

[Douglas J Steele]

Your original post says:

Table #1 consists of Team ID and Date
Table #2 consists of Team ID, Date, and Sales Price

The relationship between table 1 and table 2 should be on both Team ID and
Date.

 

[Guest]

Tried to do that and says no unique index found for the referenced field of
the primary table.

 

[John Vinson]

Ok. How do I go about dealing with the unique index message when setting
referential integrity for joing Date with Date between the two tables?

By opening the "one" side table in design view; selecting the date
field (preferably renaming it, since Date is a reserved word); and
selecting Indexed (No Duplicates) on the index property at the bottom
left of the screen.

If several records in this table can have the same date, then the
field is not unique and cannot be used in a join.

I have to agree that joining two tables on a date field is *very
unusual* and probably indicates a problem with the design of the
tables.

John W. Vinson[MVP]

 

[John Vinson]

Im trying to create a DB that I can input data into regarding sales
transactions. I have one table that has unique Team ID's. I have another
table that will have applicable information for that particular Team ID (such
as Date, Tickets, etc.) What I want to do is be able to scroll thru records
and have applicable information come up from the joined tables. I assume I
have to use a query for the two tables and use it as a record source?

Not necessarily, though it may be a good idea.

When scrolling thru the records I want to be able to input data onto the
form as well that is applicable.

What route should I take in making the DB in your opinion?

I'd use a Form based on the Teams table, with a continuous Subform
based on the sales table, joining the two on TeamID.

John W. Vinson[MVP]

 

[Guest]

So basically what should I do than? Join both tables based upon team ID? This
will be a one to one relationship. Is this efficient?

What is the deal with referential integrity rule and join types/properties?
What does it mean? Should I use it?

 

[Douglas J. Steele]

So what are the Primary Keys of the two tables?

Assuming there can be be multiple rows for the same Team ID value in the
table, Table #1 should have both Team ID and Date defined as its Primary
Key. If not, you'd better explain what exactly your data model is.

 

[John Vinson]

So basically what should I do than? Join both tables based upon team ID? This
will be a one to one relationship. Is this efficient?

What is the deal with referential integrity rule and join types/properties?
What does it mean? Should I use it?

You know the nature of the data in your tables. You know your business
rules. I don't.

I have no idea what the proper joins would be, since I don't know what
domain of information the two tables represent.

A couple of questions:

- Why is there a Date field in the first table? What is the value of
the date for a given TeamID?

- Does the date in the Teams table have any logical connection to the
date in the Sales table?

- Is it your intention that each Team should have one, and only one,
sale? Or might a Team have multiple sales, each on a different date?

John W. Vinson[MVP]