Table relationship problems

[WILYWAYNE11]

We have two tables joined in a one to many relationship however we cannot select any options without getting an error message. None of us are programs and are in need in plain remedies to correct the relationship problem with losing any data. I have attached screen shots of the tables and connections.

 

[bastanu]

I don't think we can help you with what you provided, look at the error description and try to understand the cause; you probably have duplicates in a field used in the join when you shouldn't or you have missing key(s) in one of the tables. Without analyzing the actual data you won't be able to solve this. I suggest you make copies of the two tables in question to preserve the existing data, empty them and, once empty, set the desired reference. Now run two append queries to repopulate them from the copies; when running the inserts you should get some more messages identifying the cause of the problem (you should end up with partially populated tables that satisfy the integrity constrains, the missing records being the problem).

Cheers,

 

[Pat Hartman]

The message is pretty clear compared to most error messages. It even offers a solution. Did you try editing the data and removing/editing the invalid data? The position table contains rows where the foreign key (the field that joins to the other table) is invalid. It is possible that this field has a default value of 0. Foreign key fields should always have a default of null so just remove the 0. Then, if the field is required, mark it as required and Access will not let you save a record unless it has a valid value in the foreign key.

Below is a picture of a relationship between three tables. This is a many-many relationship. The fields with the "key" marks are primary keys. The fields that are pointed to in the middle table are called foreign keys. When you want to add a row to the middle table, you must provide valid values for the DocID so we know what document the bookmark is in and you need to provide a valid value for a FieldID so you know what table/query field you would use to populate the bookmark when you fill the bookmarks in the document with actual data.

I'm not sure what the file you posted is supposed to be. If it is the relationship diagram, you can't post it as a text file since that removes all the graphics which are what we need to see.

If you can upload the database, we can identify or possibly fix the bad data. If you can't, then you'll need to do it yourself. One way to start is to create a query using the Wizard. choose the unmatched query. Select the two tables. pick the linking field. select the data you want to see. When you run the query, it will show you the records with the missing data.

 

[WILYWAYNE11]

Hi Pat..
We tried to resolve the problem SEVERAL TIMES and we still cannot modify the relationship to a one to many profile. If you can take a look at what has been designed and help I would be grateful.
Wayne Devincenzi

 

[CarlettoFed]

From what you have created it is clear that you have never read a book on access, it will be good if you do if you want to create something decent.

 

[bastanu]

Wayne,

Please have a look at the updated file. tblPosition needed its own primary key (autonumber) but you had CompanyID as autonumber, you cannot create a relationship between those and wouldn't be one to many as intended. I also changed the name of the Date field in tblCompany as that is a reserved word and can cause issues. Made few changes to the form\subform linking fields (used the CompanyID foregin key from tblPosition which now is set as Number-Long instead of autonumber). And removed the macro on the click event of the tab control...

Cheers,

 

[Pat Hartman]

Vlad also changed the name of the "Date" field. It is dangerous to use Function names and Properties as column names. You should try to avoid all reserved words because you never know when there will be a conflict but some are actually dangerous. Date and Name are two. I would also remove all dashes and any other punctuation characters in addition to spaces. All names should be formed from only letters, numbers, and the underscore. The two common naming methods are CamelCase where you capitalize the first letter of each word and the_underscore where you separate words with the underscore. I have a strong preference for CamelCase because that means I never have to use the shift key to type code. It also gives me a visual clue when I go to the next statement when Access changes the case of what I typed. If the case doesn't change, I know there is a typo.

Also, using the search/replace dialog is dangerous as a searching tool even though it seems convenient. You don't ever want your user to go crazy on you and use replace all and mess up all the data in a table by choosing the partial string option and replacing all instances of pat with abc.

Here's a link to a database that includes a form that shows why these two column names along with a couple others are dangerous.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

 

[WILYWAYNE11]

From what you have created it is clear that you have never read a book on access, it will be good if you do if you want to create something decent.

I didn't need a smart ass answer...........