Referential Intergrity On Tables With Compound Key
Mar 3, 2008
I am trying to enforce referential integrity on tables where a compound key is implemented.
I want to enforce integrity on the DETAIL table so that it can only use an SOR_ID from the SOR table that has a corresponding PhaseID in the HEADER table. Here is my current diagram:
The only solution i can think of at the moment is to build two queries. One which concatenates SOR.PhaseID & SOR.SOR_ID, and another which concatenates HEADER.PhaseID & DETAIL.SOR_ID... and then create a relationship between the two queries.
There must be a much nicer way of doing this though?
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?
I am new to access and I am trying to set a relationship between two tables, with the data types the same but keeps coming up with violates referential integrity rules....and I simply do not understand what this means, and how to solve it...
The help for junctions says to make a compound key in the junction table, but I don't understand the intructions for this. Do they mean make all three of ID's the junction table PK's or is there a literal "Make a Compound Key" button somewhere?
Currently, the table has an AutoIncrementing field as the PK and the two FK's copied in from the Primaries (converted from Auto to Number). As a result, I can only make one-to-one relationships to the junction table.
In a form named PRODUCTION there are 3 text box for invoice data: xtype, xserie, xnumber
I need valid this data from the INVOICE table, where its key is a compound key (TPINV+SERINV+NUMINV)
I tried with:
Private Sub xnumber_AfterUpdate() Dim searchinv as string searchinv=Dlookup("*","[Invoice]", [tpinv]="& Forms![production].[xtype] and [xserie]= &Forms![production].[xnumber] and "&Forms![xnumber] If searchinv ="" then MsgBox ("Invoice doesn't exist'") Cancel = True End If End Sub
When you create a column chart and then change one of the series to a line, how can you then apply formatting to that line? I've attached a stripped down database showing what I'm trying to do. Series 1 shows individual monthly values (percentages) and series 2 shows the target which is 80% for each month.
The problem is that although I can apply formatting to the column series, I can't see any way of selecting the line series so I can set the formatting I want. I'm using Access 2003, on Win XP Professional, in case that's important.
I'm getting back into Access after retiring and about 10 years of isolation (from Access). I have 2010 version and know that one should avoid duplicate entries and a way to do this is make those fields unique key fields. I have a Customers table and have bounced back between CusID (AutoNumber) and Compound Keys (CusFName and CusLName) as the key fields. The compound keys prevent duplicates but become very hard to work with later in code and expressions. The CusID is preferable from that standpoint, but can't prevent inadvertent entry of duplicate names.
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?
These tables have been imported from another Data Base. I have copied the fields into a new table and they don’t work. I have made new tables and they seem to be functioning normally
The tables: Working Links Customer-House Type House Type-Elevations Elevations-test
Non Working Links Elevations-Options Per Elevations Elevations- Copy of Options Per Elevations Options Per Elevation-House Type Materials Details
I’m going to construct new tables from scratch, unfortunately I have over a thousand records in the details table that I’ll have to deal with but what are you going to do?
The reason I’m putting this on the forum is to see if anyone else has ever come across this, or if you know a fix.
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 ?
