Error in db realtionships

[Lou_Reed]

In the following tutorial shown here: http://www.functionx.com/access/Lesson36.htmI get an error when I try and enforce Referential Integrity, I get an error
when I try to enforce it on Genres: GenreID and Videos: GenreID. The error states

Relationships must be on the same number of fields with the same data types.

I checked and on the Genres Table and the Videos Table, the GenreID the data types are different. That
must be the problem. So I tried to change it and MS Access 2010 said you must delete original


relationship and create new. That made sense so I deleted the link and began again.

Then I tried to make GenreID auto number instead of just number and it said you cannot change data type if data is already entered. So I created
a new record GenreIDD and gave it the data type auto number and autonumbered everything as expected.. Then I deleted the first GenreID that was incorrect and I changed GenreIDD to GenreID. It seemed to work.

Now I do not know what to make the GenreID in the Videos table. It clearly cannot be autonumber; its numbering just does not work out that way.

I have posted a compressed form of the Video Collection database.

Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[John_G]

I haven't looked at the attached DB yet, but if you make GenreID in the Videos table type Numeric - Long it should work - Autonumber is a special case of type Long.

[Lou_Reed]

Okay, but it is not Numeric - Long in the Genre table. I will try as you say.

R,

Lou Reed

[Lou_Reed]

That worked. I am not sure why. I was worried about number and autonumber. I could not change the GenreID data type in the Videos table to autonumber. I just made
it number for both tables (Genre and Video) and that seemed to work. It is still autonumber in Genre table and number in Video table for the data types. It works however.

Thanks for your help. Please comment on my solution if it seems incorrect.

R,

Lou Reed

[Lou_Reed]

Now in the same db that I have attached above I cannot enforce referential integrity when I create a relationship between Categories Table and Videos Table. I get a
different message than the error above. The relationship violates data base integrity rules. It claims that data in that is in the Video table may not be in the Categories table.

Edit the record in the Video table so that all records in that table exist in the Categories table. I am not sure what to do, but the error is in the attached db above.

Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[June7]

There are records in Videos that do not have data in the Category field. Also, there are 5 categories but you have a category 6 in Videos. Null and 6 are not acceptable. Enter/edit data (or delete Videos records) then set the relationship to enforce integrity.

[Lou_Reed]

If you look in the tutorial before Tutorial 36. that would be tutorial 35 and it is shown here: http://www.functionx.com/access/Lesson35.htm.

You will see towards the end of the tutorial that it tells you precisely how to populate the CategoryID records in Table Categories and how to populate
CategoryID in Table Videos. I followed their instructions. I am assuming in Lessom 36 they want the relationship table for Video Collection setup as they show
them in: Practical Learning: Creating Inner Join Relationships. I did it that way. It should work unless Tutorial 36 has an error.

Any thoughts?

R,

Lou Reed

[June7]

The lessons are jumping around different databases.

Lesson 35 uses Video Collection in the part you described.

Lesson 36 starts with example of Bethesda to demonstrate integrity.

The tutorial does not instruct to set integrity for the Categories in Video Collection. If you want to then make sure the data is good.

Can't enforce integrity if tables already have data that is not going to fit the relationship. Period.

[Lou_Reed]

I guess that you are correct. It seems that they wanted to enforce data integrity on the Categories and Videos table with respect to CategoryID (see Lesson 36 about halfway though). It just cannot be done with the data and the examples given. If you put in the data they suggest you will put in a category 6 (when there is no category 6) and have many films with no categories at all.
Respectfully,

Lou Reed

[June7]

You can have a relationship without enforcing referential integrity and images in the tutorial show the relationship. I don't see any instruction to also set referential integrity. So am guessing the tutorial left it up to you to figure that out. Sometimes can learn more when encountering an issue.