Unique Index

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

"Douglas J Steele" wrote:

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.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"jerseydreams" wrote in message
...
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" wrote:

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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"jerseydreams" wrote in message
...
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?

"Twas" wrote:

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.
--
Twas


"Barry Gilbert" wrote:

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

"jerseydreams" wrote:

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

On Thu, 13 Jul 2006 07:53:02 -0700, jerseydreams
wrote:

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]

On Thu, 13 Jul 2006 08:50:01 -0700, jerseydreams
wrote:

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]

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?



"John Vinson" wrote:

On Thu, 13 Jul 2006 07:53:02 -0700, jerseydreams
wrote:

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]

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"jerseydreams" wrote in message
...
Tried to do that and says no unique index found for the referenced field
of
the primary table.

"Douglas J Steele" wrote:

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.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"jerseydreams" wrote in message
...
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" wrote:

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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"jerseydreams" wrote in
message
...
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?

"Twas" wrote:

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.
--
Twas


"Barry Gilbert" wrote:

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

"jerseydreams" wrote:

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

On Thu, 13 Jul 2006 11:41:01 -0700, jerseydreams
wrote:

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]