One-To-Relationship

[Sean Smith]

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.

 

[Allen Browne]

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.

 

[Jeff Boyce]

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


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Sean Smith]

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.

 

[John W. Vinson]

On Wed, 18 Mar 2009 17:46:00 -0700, Sean Smith <Sean

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.

Well... you're shooting yourself in the foot, both by turning off the one
thing which MIGHT help keep your tables in synch, and more so by having
incorrect normalization. One to one relationships are rarely appropriate and
certainly not appropriate here!

You have an Invoice which has *many* processes, and each Process can apply to
many Invoices. An invoice also needs *many* signatures, and each signer can
prsumably sign many invoices. I see two (or perhaps just one, if a signature
can be considered a process) Many to Many relationships.

How about a table of Invoices; a table of Procedures (e.g. "Draft received",
""Draft verified", etc. etc.; and a Progress table with fields InvoiceID,
ProcedureID (or ProcedureName if you use a text primary key), and
DateProcessed. Similarly a table of AuthorizedSigners, and a Signatures table
with InvoiceID, SignerID, DateSigned. There'd be a one to many relationship
from Invoices to Progress, and from Procedures to Progress, and similarly from
Invoices to Signatures, and AuthorizedSigners to Signatures.

This would be a more flexible system (you can add a new signer or a new
process just by adding a record to a table rather than changing a table's
design), and will allow you to maintain RI and to generate flexible reports.