Many thanks for all the help thus far with my Scout Database :)
I have a problem with referential integrity, I am getting errors.
In my form 'frmHeader' , when I select a member, and click achievements (a command button in top right corner) i always have to enter a date, it never creates a record in VenturerDetails (table) for a Venturer.
When it goes to frmVenturerAchievements (for a Venturer) I get referential integrity errors when I enter data into the subforms.
I have a load of tables with primary key fields and then a table with just the appropriate foreign keys (we'll call this the f table). I have all the table links set up to Enforce Referential Integrity.
When I import the data i need into the f table a warning comes up. It basically says 200 records will be "Lost" as they dont relate to the primary key tables.
Now i dont mind this as I know some of the data is bad and needs ammending ('??' or 'UNKNOWN' etc. in some fields).
THE PROBLEM i have is: Is there anyway to see this "lost" data after the import so that i can easily go through it as a block correcting the irregularities? it seems to me this "lost" data in has actually been deleted- is there anyway of retrieving it?
Ideally id like it to be able to export this "lost" data into a new table, spreadsheet (or anything else!) so i can sit down and go through it all as a block.
I know i could go through, editing the original data before importing but this will be a needle and haystack situation so i'd like to avoid at all costs!
Can anyone suggest any advice on approaching this problem?
Basically I have a table Customers and a table Accounts. #customer is the sole primary key of Customers, and #account+#customer is the composite key of Accounts.
A customer cannot create a new account without being a customer, and so normally I would enforce referential integrity and in access it would appear as a one to many relationship.
However, according to my boss, in rare circumstances, an entry in Customers can be deleted, and the orphaned accounts associated with a new #customer.
So at the moment I have it set up how I would have, only without referential integrity, but I don't think this is going to work properly for three main reasons:
1) The address in Customers needs to be displayed in the accounts form.
2) The #customer part of the composite key would need to be changed somehow.
3) There is obviously no referential integrity, and there are too many customers to limit accounts.#customer to a combo.
Note that I've made this simpler than it looks - the customer table is connected to other repeating groups too.
I think my question is whether this is a common problem, and if so is there a common solution to it?
Hello, I have a new database with two tables, one of which is a linked table. I want to link the two using a one to many relationship from one table to the other.
I have a primary key field in the main table which is an autonumber, and a foriegn key field in the second table which is a number type. The field size is long integer. When I go into the relationship view to set up the relationship, I dragged the primary key field from one table to the foriegn key field in the other table. The create relationship dialog box comes up, but it does not allow me to set Referential Integrity. The Access help indicates that the database of the linked table must be open, in order to establish Referential integrity betwee a linked table and a local table. I tried that and still could not establish Referential integrity. The dialog box does not give an error message, the check box for Referential integrity is just grayed out.
Has anyone had this problem, if so what was the fix? I have never had this happen before.
Please see attached word document with a screenshot of my tables and their relationships. I'm trying to link from Table:Line,Field:Line to Table:Shift-Line and similarly with the shift table and access is not able to maintain referential integrity.
Can anyone explain why? and generally what I've done wrong with my relationships please?
Being as how I'm new in using Access I need some guidance. I have three tables I've set up my relationships as follows:
tblPatient tblContact tblPhone PatientID PK ContactID PK PhoneID PK PatientAccount FK PatientID FK PatientID FK PatientFirstName ConatctFirstName ContactID PatientLastName ContactLastName DOB Address City State Zip Intial Visit
I have set up a 1 to many between tblPatient PatientID and tblContact PatientID.
and a 1 to many between tblContact ContactID and tblPhone ConatctID
I have given each table referential integrity, to mine understanding in short it means change it in one table it changes in all tables. Please let me know if I'm headind in the right or wrong direction the way I have this set up.
I just read in another thread about enforcing referential integrity between tables so that deleting a record in a primary table won't leave orphans in a subordinate table. I was able to edit the relationship for one subordinate table to enforce referential integrity (cascade update and cascade delete), but not for the other two. It keeps giving me an error saying that referential integrity for those two tables can't be enforced. I've gone through every record in those two tables, and I can't find any more orphans. What am I doing wrong?
I am trying to remove a 'Referential Integrity' from a relationship in an MsAccess database using Visual Basic. Does anybody know how I could achieve this?
I am currently working in A2007. I added a table to the db to hold look up data and when I went into the the relationships to drag and drop to create the relationship it pops up an error stating that it cannot create referential integrity. See the attachment below. I checked and double checked the data types are the same.
media (section_id PKEY, from_pair, to_pair, from_ip, to_ip)
Network_element( Name PKEY, MAC, IP)
I'd like to be able to make sure access will only allow from_ip and to_ip to be entered if there is a corresponding value in the Network_element table, however I do not know how to enforce this.
Any help much appreciated.
Thanks
Dan
ps - if anyone has done something similar, I'd be keen to see how they gone about it.
Lets say u have a database that keeps track of all items sold and their respective prices. So u have a items table that keeps the product information and price. Then these items are displayed on invoice kept in a invoice table linked with a foreign key and a one to many relationship.
My question is lets say the price of an item should change....will the invoice table of old invoices display the new price or old price. Is this where the referential integrity comes to play ? Should u select it when doing the relationship ticking the cascade update related fields and cascade deleted records boxes respectively or not ?
Lets say u have a database that keeps track of all items sold and their respective prices. So u have a items table that keeps the product information and price. Then these items are displayed on invoice kept in a invoice table linked with a foreign key and a one to many relationship.
My question is lets say the price of an item should change....will the invoice table of old invoices display the new price or old price. Is this where the referential integrity comes to play ? Should u select it when doing the relationship ticking the cascade update related fields and cascade deleted records boxes respectively or not ?
Just a quick question, it appears that I can't Enforce RI for relationships where one of the tables is linked from another *.mdb (the checkbox is greyed out)... is this a general thing with linked tables?
If someone can help solve this for me, I would be forever grateful. I have attached the database to which I am referring for clarity.
I am creating a database designed to input data for morbidity and mortality at my hospital. I have set up four main tables: tbldemographics (basic patient information), tbladmissions (information about each different admission a patient may have), tblmorbidity (information about each untoward event suffered by a patient, which I would like to have linked to the specific admission, not just the specific patient), and tblmortality (information about a patient's death, which I would also like to have linked to the specific entry in the admissions table rather than linked to the more general information in the demographics table).
I have created relationships which I thought would work--but with referentail integrity invoked (which it seems like it should be), the very first key stroke on the form that I created to input the data (frmmain) results in an error message that the record can not be added or chaged because related record is required in tbldemographics--and yet this is the table to which I am inputting the data! Turning off referential integrity stops the error, but I don't want to just blindly do that--seems risky.
Problem number two: when I disable referential integrity and enter data into the demographics section of the form, any entry at all results in an automatic entry into the admissions table--even if I haven't entered that part of the subform yet. So if I then exit the form and return later, ANOTHER entry into the admissions table is made--again, even if I have not ventured into that subform yet. So that table winds up with bunches of entries with little to no data, which are NOT cleared up with a compact and compare maneuver.
I'm at a loss on both of these issues. I apologize for the long post, but appreciate any ideas you all might have.
I have a table I am splitting into three tables in a one to one fashion(because I was told to do it) The Index of the original table is set to: Item Key (autonumber) and UPC Number
I have created the two new tables from the original table, have added Item Key as the Primary key on the original table and the two child tables. I have set referential integrity for the relationships, but adding a new record will not add a new record to the child tables. Can anyone suggest something that I am doing incorrectly?
How do you prevent updates to a 1->M relationship as defined using a main form and sub-form ie. when the user only enters details in the main form and leaves the sub-form area blank?
With a 1->M relationship defined in the Relationships window - I would have expected Access to trap the error.
On a number of forms where I am using a 1->M main/subform interface in my DB - if the user does the above - Access does not error - as a result it is easy to break referential integrity rules and have instances where single records exist with no related records in the other table?
I'm not sure if I am heading down the right path here. I have searched the forum but not come up with anything on the subject of deleting relationships and RI.
I have a project assignments junction table that links project and personnel tables. I want the employee details in tbl_prj_assignments to be fixed so that they are not updated by changes in tbl_Employee_details. This is so archived records will always reflect the personnel who actually worked on them rather than the people currently in that position or if they have left the company.
I had referential integrity from both parent tables to the junction table in the back end database. The project relationship remains unchanged so that any project changes or deletions removes all the related assignments.
I tried to remove the RI from the relationship between tbl_Emp_Details and tbl_Prj_Assignments by unchecking the 3 boxes for Enforce RI, Cascade update, Cascade delete. On testing I found that the junction table was still updating with changes to the employee table.
Next I tried deleting the relationship altogether with the same result. So now I am left with referential integrity being enforced where I cannot see any relationship. :confused: :confused:
The fields in the tables are all text boxes - no lookup. The junction table is populated by a subform based on the table and located on the project details form. The subform uses a combo box with an SQL statement to select the employee.
Questions:
1) Firstly, is this the best way to achieve the desired outcome?
2) Where is the referential integrity being enforced, and can it be removed?
Hello I'm helping a friend with an access project and the Referential Integrity check is failing for some reason unknown to me. I recreated the entire database from scratch and added all relationships before adding any data and then added in the data. The tables giving me problems are the following:
Orders Table: OrderID - Primary Key Autonumber, Format:1000, Indexed: Yes(No Dupes) CustID - Number, foreign key to a customers table, 1-many relationship SalesID - Number, foreign key to a sales table, 1-many relationship ShippingID - Number, foreign key to a shipping table, 1-many relationship 3 different date fields and a price field
This table is filled with data.
Order Details Table: ItemID - Primary Key Autonumber, Indexed: Yes(no dupes) OrderID - Number, Dec. Places: Auto, Required: Yes, Indexed: Yes(dupes OK) RingID - foreign key to rings table, 1-many relationship StoneID - foreign key to gemstones table, 1-many relationship Ring Size - number
There is no data in the order details table.
The Orders to Order Details is a 1 to Many relationship with ref integrity and cascading updates and deletes enforced.
When I try to manually type in a record I get the "You cannot add or change a record because a related record is required in table 'Orders'" even though I added a lookup list box listing all the OrderIDs from the Order table. I know I'm typing in a valid OrderID number, but it's not taking it and throwing this error.
I opened the relationship window to do the relationships among my tables; however, the check box 'Enforce Referential Integrity' is disabled. I don't understand why is that? And is there anyway to change it?
Also, where should relationships be implemented? is it the FE or BE? Any help will be very much appreciated. B
NEw to access. I have a parent child relationship. I thought that referential integrity automatically added new records to the child table. Am I wrong? Is there a workaround solution to add new records?
The ones with stars are "type" tables. All possible IP's are in the tblIP, models in tblModel, office's in tblOffice etc.
Maintenance types are just like "fuser replacement", "toner replacement", "turned on computer" (IT joke)
anyway, On my main form. If I open the form and click the button I made to go to the previous record or next, I am fine.
If I choose an office, or start a record, I get a message that I have to have a record for IP chosen, that it can't leave this new record until it has an IP because of referential integrity.
If I choose an IP and nothing else, I can leave the new record. If I don't choose IP, but choose everything else, I cannot leave the record. AHHH!!!
The thing is, all my combos on the main form are (as explained above) FK's to lookup tables, and all set up the same: all with referential integrity, 1 - many, cascade updates and deletes.
This 1 table is the only one acting this way.
I am including a print screen of my form, as a visual sometimes helps.
If anyone can help me to fix this, I would really appreciate it. I have never had this problem before.
How do I maintain referential integrity between a main form and a subform, each based upon different (but joined with integrity enforced) table?
Here's the situation: I have two tables: tblContracts and tblPayments. tblContracts has an autonumber field called IDKey as its primary key. tblPayments also has an IDKey field (Integer datatype). The two tables are linked in a one-to-many relationship on the field IDKey with referential integrity enforced.
I have a main form based upon tblContracts (the "one" side of the relationship) that has an embedded subform based upon tblPayments. the two forms are linked Parent/Child on the IDKey field.
Here's the problem: If a user goes to a new record in the main form, it allows them to enter information in the subform without entering information in the main form. This means that a new record (and its corresponding autonumber IDKey field value) does not get generated in tblContracts and I have an orphan record in tblPayments that is not linked to any record in tblContracts - which violates the referential integrity that is supposedly enforced between the two tables.
Any guidance on how to deal with this would be greatly appreciated.
Hi, I have a database with a couple of tables. The primary table has a primary key called "StaffNo". I checked the relationships between the primary table and each related table, if I have ticked on the boxes for referential integrity, cascade updates. All done. If I now create a query, which selects the primary table and one related table and enter a new StaffNo, I would expect to see the new StaffNo not only in the primary table but also in the related one. But there is nothing. Do I expect wrong? Have I missed sth.? I read a few posts in this and other forums reg. referential integrity, but I have no clue, what's wrong. :confused: Any quick help is much appreciated. Thanks :)
tblDateReason DateReasonID(pk) EntryNumber(fk) Date Reason Entered
tblEmpMain EntryNumber(pk) EmployeeNumber Etc//
I have a 1 to many setup between tblEmpMain and tblDateReason.
When I try to setup the relationship between the "reason" field in tblDateReason and luTblReason, Referential Integrity along with the other two check boxes are greyed out.
It still shows one to many but it will not let me enforce referential intergrity.
Why is this? Did I setup the database wrong? Or am I trying to do something I shouldn't be doing??
I have attached the db.. It is split with both the backend and the front end.
