If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Unique Index
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] |
#13
|
|||
|
|||
Unique Index
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] |
#14
|
|||
|
|||
Unique Index
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] |
#15
|
|||
|
|||
Unique Index
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 |
#16
|
|||
|
|||
Unique Index
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] |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Website Index | Eridyard407 | General Discussion | 14 | July 12th, 2006 05:43 AM |
unique index still allow duplicates when date fields are null | theelio | Database Design | 2 | June 27th, 2006 03:20 PM |
How to fix the no unique index found problem in laymen terms? | RacerX | General Discussion | 5 | May 2nd, 2005 04:11 PM |
No Unique Index Found | TinleyParkILGal | General Discussion | 3 | April 19th, 2005 05:21 AM |
Indexing - Global refresh/deletion of index marks | Ray Cleere | Formatting Long Documents | 0 | April 30th, 2004 10:46 AM |