Using one-to-one relationship

Anonymous

For various reasons, I need to break up a large table into smaller tables. I've set up the smaller tables with one-to-one relationships.

If I assign a form's record source using just one of the smaller tables (and its fields), the form opens correctly showing the first record. And, I can move through all the records using the Access record controls.

If I add one or more of the smaller tables (which participate in the one-to-one relationship with the original table), the form opens but no records are available - i.e., it opens as if I asked it to display a new record, and it doesn't recognize that there are other records available.

The one-to-one relationships are correctly set, with each table's primary key being a single key.

I have form controls that need to be bound to several of the one-to-one tables, so several tables need to be the form's record source.

As an example: Table A has an autonumber primary key field, AID, and another field, Aname. Table B has a primary key field BID (numeric long integer type) joined to AID in a one-to-one relationship (with referential integrity), and another field, Baddress.

If Table A is a recordsource for a form, the value of field Aname appears on the form and I can move through all Table A records. As soon as I add Table B (along with Table A) as a recordsource, the form opens with no records available (i.e., a blank form as if I told it to go to a new reocrd).

I realize I can resolve this through a single large table, but that's what I'm trying to avoid.

Has anyone worked with one-to-one relationships enough to have run into this problem? And come up with a solution?

Thanks for any help.

Mike

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

ScottGem 68,840 Volunteer Moderator

Mike,

I'm going to agree with Roger yet again. Helping you has been a pleasure and we will be here to support you as your project continues.

The main difference between bound and unbound forms is that, with a bound form, Access handles all the I/O between the form and the table. With an unbound form, the user has to write code (VBA), to handle the I/O. The complexity of that code can vary widely depending on the usage and purpose of a form. For example, a form solely for entering new records is fairly easy to code. But a form for retrieving data and populating the unbound controls, can be very complex. The coding involved depends on the table structure and form layout. Personally, I'm lazy and tend to use bound forms wherever I can.

There is only ONE circumstance in Access where a child record can be created with an automatically populated FK. And that's when using a subform. When you correctly link a subform to the main (parent) form, then any records created in the subform have the FK automatically populated. This is one of the reasons I said that subforms are a very powerful feature. But this only works with bound forms. So, in every other circumstance, when you add a record to a child table, you have to make sure the FK is populated correctly.

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

0 comments

Answer accepted by question author

Anonymous

Mike,

It's posts like this that makes the whole process of answering questions here worth while. I think we've seen a lightbulb go off above your head <grin>

As you no doubt realize, there are two distinct knowledge sets necessary for database development: design and implementation. The Design set is universal and can be applied to any DBMS implementation. The Implementation set is specific to the DBMS.

For Design knowledge, I recommend Michael Hernandez' "Database Design for Mere Mortals". I taught 12 years of college courses in db design from that book. It was a revelation to me.

For Access Implementation knowledge, I recommend "Access Cookbook" by Ken Getz, Paul Litwin, and Andy Baron. I learn best from examples and this book is a set of common problems and solutions in Access development. It's not really a book that you read through. It's best used to find how to solve a specific problem.

I created the samples on my website (www.rogersaccesslibrary.com) with the same idea in mind.

Good luck!

-- Roger Carlson

MS Access MVP 2006-2010

www.rogersaccesslibrary.com

If you want a detailed answer, ask a detailed question!

0 comments

44 additional answers

Anonymous

2011-02-07T03:46:34+00:00

Jim,

I'm thiking your tables are not set up properly because you should only need to add to one place not several tables. Perhaps you could post the make up of your tables and/or have a look at...

Jeff Conrad's resources page...

http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...

http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...

http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...

http://allenbrowne.com/links.html#Tutorials

Sample data models...

http://www.databasedev.co.uk/table-of-contents.html

Remember Access tables are not Excel spreadsheets on steriods...

--

Gina Whipp

Microsoft MVP (Access)

Please post all replies to the forum where everyone can benefit.
Was this answer helpful?

0 comments No comments
ScottGem 68,840 Reputation points Volunteer Moderator

2011-02-07T13:46:21+00:00

To both Mike and Jim,

First, I agree with Gina that you need to look at the normalization of your tables. If you had tables with lots of fields, there is a good chance they are not normalized properly and instead of splitting them with one to one relationships, you may need to redesign. Please post your table structure so we can judge.

But the answer to your problem is simple. Use subforms. If there is truly a 1:1 relationship, then you can embed a subform bound to the related table . You can remove the navigation bar (since you only want one record), you remove the record selectors and borders so the subform appears to be a part of the main form.

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries
Was this answer helpful?

0 comments No comments
Anonymous

2011-02-08T00:56:24+00:00

Thanks All.

As it turned out, one of my "subtables" was corrupted, which caused most of the problem.

Closely related to my problem is difficulty in updating the PKs (which are all the same amongst the five subtables) for a single record, when I'm creating a new record. Although all the subtables are included in the record source for the form, the only PKs that are changed are those which are connected to a control on the form. I had assumed that if all the subtables have a common PK in a one-to-one relationship, all I need do is have one of them assigned a PK value to have that value appear in each subtable. Instead, I've had to put a non-visible control for each table's PK on the form and update them through a macro, after the autonumber is assigned to a single table. While this is workable, and is only necessary to do once when I'm creating a new record, I'd prefer to avoid the extra controls and macro.

Do y'all know of a simpler, more efficient way to update all the related PKs at once (for a new record) once one of them is assigned its autonumber? (One table's PK is autonumber, the remaining PKs are long integer numeric, related to the table with the autonumber PK.)

Mike
Was this answer helpful?

0 comments No comments