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 |
#1
|
|||
|
|||
One-To-Relationship
Hi,
This is my first post, I have a Database that I have two tables with a one-to-one relationship setup. I have done this as they track two groups of information on the same PK, there is potential to have other tables setup the same way. It would get messy quickly to have all the fields in one table. I've setup the relationship no problem, Ref. Int is on and does what it is supposed to do. Each table is edited by a different form, I canot figure out how I add a record to either table as it breaks Ref Intg. (The key does not exist in the other table). I understand this should happen, but I'm not sure of a best practice to get around it. I suspect I should setup a event 'Before Update' with an append sql statement, testing for the record first? Any thoughts would be welcome. |
#2
|
|||
|
|||
One-To-Relationship
Although the relation is one-to-one, you will still have one table as the
primary table, and the other as the related table. It's important to know which is which, as you cannot insert a record into the related table until it exists in the primary table. 1. Choose the form where the record is entered into the primary table. Open it in design view. 2. Set its After Insert property to: [Event Procedure] 3. Click the Build button (...) beside this. Access opens the code window. 4. Set up the code like this: Private Sub Form_AfterInsert() Dim strSql AS String strSql = "INSERT INTO Table1 (F1, F2) SELECT " & Me.ID & _ " AS F1, " & Me.Something & " AS F2;" dbEngine(0)(0).Execute strSql, dbFailOnError End Sub To get the SQL statement right, mock up a query without any table, and type a value such as 99 into the Field row. (Access will alias it as Expr1.) Now change it to an Append query (Append on Query menu.) Access asks for the table to append to: choose the related table. Type other values in subsequent columns in the Field row if you have more items to add. Enclose the value in quotes if appending to a Text field, or in # if appending to a date field. Now switch this query to SQL View (View menu), and you will see an example of the string you need to create. You will close the string (with quotes) in the middle, and concatenate the value from the text box on the form in place of the 99, as shown above. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Sean Smith" wrote in message ... Hi, This is my first post, I have a Database that I have two tables with a one-to-one relationship setup. I have done this as they track two groups of information on the same PK, there is potential to have other tables setup the same way. It would get messy quickly to have all the fields in one table. I've setup the relationship no problem, Ref. Int is on and does what it is supposed to do. Each table is edited by a different form, I canot figure out how I add a record to either table as it breaks Ref Intg. (The key does not exist in the other table). I understand this should happen, but I'm not sure of a best practice to get around it. I suspect I should setup a event 'Before Update' with an append sql statement, testing for the record first? Any thoughts would be welcome. |
#3
|
|||
|
|||
One-To-Relationship
Sean
You've described your one-to-one relationship, and mentioned it getting "messy ... to have all the fields in one table". This is not, by itself, a reason to have a one-to-one table relationship. That decision needs to be based on the actual data, not on convenience. If you'll provide a bit more specific description of what 'domain' you're working in, what data you're working with, and an example of what/why you decided to split the data into two table, but maintain a one-to-one relationship, folks here may be able to offer more specific suggestions. Good luck! -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Sean Smith" wrote in message ... Hi, This is my first post, I have a Database that I have two tables with a one-to-one relationship setup. I have done this as they track two groups of information on the same PK, there is potential to have other tables setup the same way. It would get messy quickly to have all the fields in one table. I've setup the relationship no problem, Ref. Int is on and does what it is supposed to do. Each table is edited by a different form, I canot figure out how I add a record to either table as it breaks Ref Intg. (The key does not exist in the other table). I understand this should happen, but I'm not sure of a best practice to get around it. I suspect I should setup a event 'Before Update' with an append sql statement, testing for the record first? Any thoughts would be welcome. |
#4
|
|||
|
|||
One-To One Relationships
Thanks Jeff,
The two tables with the one to one relationship are used to track Invoices through to payment. The first table is used by contract administrators to status their processes (Draft received, Draft verified, final invoice received, sign off authority started and finished, invoice paid.) The second table tracks the sign off authority, there can be up to 10 signatures for each Invoice (Name and Sign Date tracked), In this table I also have a flag for each sig as to who currently has the Invoice. The Contract No. and Sequence no. are the keys for both tables and there is a one-to-one relationship. For every invoice there is an approval process. There is potential to have further processes tracked, again they would be for every invoice. Thanks again for your help with this, the system does work but I’m having issues with the data getting out of Sync. I have turned ref integrity off. |
#5
|
|||
|
|||
One-To One Relationships
|
Thread Tools | |
Display Modes | |
|
|