Using one-to-one relationship

Anonymous
2011-02-07T01:32:55+00:00

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

Microsoft 365 and Office | Access | For home | Windows

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 Reputation points Volunteer Moderator
2011-02-10T19:13:03+00:00

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

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2011-02-10T18:15:24+00:00

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!

Was this answer helpful?

1 person found this answer helpful.
0 comments

44 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. ScottGem 68,840 Reputation points Volunteer Moderator
    2011-02-09T13:15:02+00:00

    First, I'm not sure if you are using the terms front end and back end correctly. In Access parlance a back end is an Access container file (mdb or accdb) that contains just the tables. The front end contains everything else.

    Second, I'm not so much worried about duplication of data as I am about using fields to define data. Lets take your Milestones table for example. If you have fields with names that describe milestones, then your database is not normalized properly. Not knowing the business I would guess that milestones might be things like purchase date, ground breaking, construction progress etc. If you have fields like PurchaseDate, GroundBreakingDate, etc. Then your database is not normalized properly. This is also borne out by your statement that it is "easier to add and organize fields in the future". In fact, this type of design is not easier to maintain. If you were to add a field, you have to modify the table, your forms, your reports, your queries etc. The better (and normalized) desing would be to use TWO tables that are in a 1:Many relation with the project:

    tblProjectMilestones: ProjectMilestoneID (PK Autonumber), ProjectID (FK), MilestoneID (FK), MilestoneDate, MilestoneValue, Comments

    tluMilestones: MilestoneID (PK Autonumber), Milestone

    The first table is your data table, it records each milestone for each project as a separate record. The second table is a lookup table (hence the tlu prefix) that contains a list of all milestones you track. With this structure, add a new milestone is a simple as adding a record to the lookup table. No change is needed to other objects. You enter data using a subform bound to tblProjectMilestones. The subform is set to Continuous Form mode so you can browse through all the milestones recorded for a project.

    In fact, this structure can be applied to almost any circumstance. I can definitely see it applying to your Evaluation Criteria and maybe Site Description as well.

    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
  2. Anonymous
    2011-02-08T17:55:37+00:00

    Scott:

    It's a database for a land trust.  The "frontend" basically models the trust's process from initiating a project to investigating its feasibility to evaluating if it's appropriate for their Board to invest in.  The "backend" is simply a repetitive "stewardship" process (i.e., maintaining/administering the trust property).  The "frontend" is one long record of criteria, memos, evaluation judgments, results from Board meetings, etc., all tied to a single project under development.  So, I've broken up the project table into 1-1 subtables:  Project, Site Description, Evaluation Criteria, Milestones, etc.  I've done this because it makes it much easier to add and organize fields in the future (basically, an object-oriented approach).  Many of the project tables have fields linked to other 1-many tables to hold data like Property information, Owner Information, Consultant Info, and the like.

    It seems to me that the "backend" of the database is a more traditional approach - i.e., a repetitive process that lends itself to smallish 1-many relationships.  But, the "frontend", since it is a long linear process with few actual records, seems to lend itself to a large single table that models the process.  And, rather than having a single large table, I'd like to break it up into a set of 1-1 tables for easier organization and future update.  I couldn't see any advantage to making the "subtables" into 1-many relationships, not least of which was difficulty in keeping the PK, FKs coordinated.

    I can assure you that amongst all the 1-1 tables there is no duplication of information, and where useful, they make use of other 1-many tables (e.g., Owner Information, Property Information). 

    So, I can provide much more detail (like the table fields), but I doubt it would be helpful for you.  The fields simply represent all the extensive process steps necessary to evaluate a potential property for the trust.

    Mike

    Was this answer helpful?

    0 comments
  3. ScottGem 68,840 Reputation points Volunteer Moderator
    2011-02-08T13:12:28+00:00

    Scott:

    I'm pretty ignorant about subforms.  My impression is that they must reside within the same real estate as the parent form, which would be unworkable for me.

    Is it possible to open a subform in a separate window, or, perhaps, on an individual page of a tab control?  That could be useful?

    Thanks.

    Mike

    Subforms are one of the best and most powerful features of Access. If you aren't using them You should be. Generally a subform is an embedded form on a mainform. But there is something called a synchronized form that can be opened separately. But I'm not convinced you need that. Can you explain why you think that would be unworkable for you?

    But yes a subform can reside on a tab control, so you can have several subforms without using any additional screen real estate. I often do it that way when there is a lot of data to display.

    You thought incorrectly in assuming that creating a one to one relationship would automatically keep the PKs in sync. Access has no provision for doing that. The closest it comes is when using subforms. If you add a record to a table using a subform, the Foreign key (which in a 1:1 is often the PK of the child table) is automatically entered. But synchronizing these tables is not difficult. However, I'm still not convinced you have a normalized structure. I strongly suspect its not and really needs to be looked at. So before I explain how to keep them in sync, I'd like some idea of the structure.

    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