Solved

access 2007, how do related tabled get updated at the same time

Posted on 2010-09-17
16
398 Views
Last Modified: 2012-05-10
I know this is really an "Access 101" type of question, but I made some changes to a database recently (took one giant table of info and broke it into several related tables) and can not get them to work together the way I think they should.

I have a DB w/1 main table and 8 "sub" tables that are directly related (1-1, enforce ref int, cascade up/down) and my question is:

Can one query be created that will automatically update each related table simultaneously with a replication of the new record ID/Community Name so that when basing a Form on that query, I am able to complete the form to the end w/o getting the following error:

The Microsoft Access Database engine cannot find a record in the table 'CommunitySurvey090210TBL' with key matching field(s) 'CommunityID'.

I have included a sample of the DB to demonstrate.
0
Comment
Question by:Laura Sheldon
  • 6
  • 4
  • 2
  • +3
16 Comments
 

Author Comment

by:Laura Sheldon
Comment Utility
Database
Database4.mdb
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"I have a DB w/1 main table and 8 "sub" tables that are directly related"
Well, you really ... don't want to do that.  There is rarely, if ever - a good reason for 1-1 relationships ... unless some sort of sensitive data is part of the table and needs to be separated out.

As you can see, maintaining even one, let alone EIGHT 1-1 relationships is going to be a real PITA.

I would suggest rethinking your design ...

mx
0
 
LVL 1

Expert Comment

by:vinaykrai
Comment Utility
It looks like your database is properly normalized and you are trying to change it. What you shouls be doing is using subforms. The following article I wrote may help you.

Displaying Data from related tables on a form

This is a very frequently discussed issue. But before I get into the methods, you need to understand one of the principles of relational databases. That principle is that data should exist in one place only. Having the same data in multiple tables is a violation of normalization. Related records are indicated by a Foreign Key within the record that holds the Primary Key value of the parent record. So when you want to have data from multiple tables on a data entry form, you set it up to display the data not store it in the form’s Recordsource

There are basically four ways to display related data on a form; Subforms, The Column property, DLookups and Listboxes. I’ll discuss each in turn and suggest where to use each.

1) Subforms. You can use a subform to display several fields from the related table. Create the form using the Subform wizard or create a separate form and place it on the main form as a subform (I generally create a separate form). Using the wizard, you go through the following steps.
• Select whether to use an existing form or create a new one
• If you are creating select the table and fields to use
• Select the linking fields, usually accept the defaults Access proposes
You can customize the subform, so it looks like part of the main form, by removing record selectors, navigation buttons, borders etc. I use subforms when I want to display 4 or more fields from the related record. Another advantage of using subforms is where you have a One to Many relation. Using a Continuous Form or Datasheet view, you can display multiple related records at once.

2) Column Property. Generally Foreign Keys are entered by selecting the related value from a combobox. The combobox uses a query as it Rowsource. This query displays the records from that parent table. At the least, the query includes the primary key field as its bound column and a description field. However, you can add as many other fields from the table as you want. These fields can then be reference using the Column property. Click the Build button […] next to the Rowsource property to enter Query Design Mode. In Query Design Mode you can add tables and fields to the query. You can control what fields actually display in the pull down list by setting their Column Width. Setting the width of an individual column to 0" will hide that column (Note: Column widths are entered separated by a ; for each column listed in the column Count). The combobox will only display the first non zero length column after selection. The following properties of a combo are key to using combos in this way: RowSource (the list), Bound column (what's actual stored), Column Count (how many columns in the list, Column Widths (the size of each column in the list).

You can then set the ControlSource for an unbound control to:
[comboxname].Column(x)
Where comboxname is the name of the control and x is the number of the column in the query for that field. Note: the column count starts with 0 so the 3rd column is 2.
Since the combobox selects a single record, the Column property will also reflect a single record. I use this method if I need to display 3 or less values from the related record.

3) DLookups. DLookups allow you pull a value from a field in a specific record. It uses the syntax: DLookup(“[fieldname]”,”table/queryname”,”Criteria”). The Criteria is used to specify the record you want to return. Since the Comboxname will store the FK value you would use a criteria like: “[keyfield] = “ & [Comboboxname]. This would also be used as the controlsource of an unbound control. Each DLookup should only be returning data from a single record. If its possible that the DLookup might not find a matching record you should use it within a NZ (NullZero) function to prevent errors. I use DLookups when I need to pull data from different tables based on a key value.

4) Listboxes. A Listbox can have multiple columns with column headers. It also can be set to display multiple matching records. I will, sometimes, use a Listbox in place of a continuous form or datasheet subform. Listboxes will also display multiple matching records.

There are two exceptions to the rule of not repeating data in multiple tables. The first is the PK value. Obviously, that value has to be repeated as the FK to relate the records to each other. The other exception is time sensitive data. Sometimes you need to freeze data that will change over time. The best example of this is price data. For example: In an order entry application, you want to freeze the price at the time of the order. In such a case, you would have the Price field repeated in the OrderDetails table. Generally you would use the Column property for this and populate the control in the After Update event of the Products combo use code like:
Me.txtPrice = Me.cboProduct.Column(2)

These guidelines should help you build forms that preserve normalization and are well organized and easy for the user to use.
0
 
LVL 18

Expert Comment

by:Richard Daneke
Comment Utility
You can break down a table into many tables as you mention.
Access provides a wizard to help on this.   If you can open the orginal Access table, under Tools, Database Tools, Anaylze Table to start the routine.
A couple of introductory windows will try to explain what you will be doing.  Then, follow the windows to split your data, name the tables, establish primary keys, and, optionally, create a query that will pull all of the fields back together.
You are headed down this way with your example, except you are doing it all manually.
Can you go back a step and run the Wizard?
 
0
 

Author Comment

by:Laura Sheldon
Comment Utility
DatabaseMX

I was always under the impression that it was better to have separate tables for different types of information.
0
 

Author Comment

by:Laura Sheldon
Comment Utility
vinaykrai

That's kind of what I was trying to avoid, but I understand if that's the only way to do it.

Thanks,
0
 

Author Comment

by:Laura Sheldon
Comment Utility
DoDahD

I'll give this a try and see how it goes!

Thanks
0
 
LVL 18

Expert Comment

by:Richard Daneke
Comment Utility
In Access 2007 and the ribbon, you start the database analyzer from the
Database Tools and in the Analyze tab group, use Anaylze Table to start the routine.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 125 total points
Comment Utility
"I was always under the impression that it was better to have separate tables for different types of information."

No.  Only 'Related' information, as in One To Many.  With EIGHT 1-1 tables - every form, every query and every report is going to be a giant hassle.

Keep this in mind.  In an Access mdb, if you define say a Text for for 100 characters, but only 5 characters are present in any one record, then only 5 bytes are used.  In other words, Null fields really don't take up any space per se.  So, even if a lot of your fields - the ones you've separated out ... don't have data in a lot of records ... no problem as far as spaced used in the mdb.

Also keep in mind that ...  a one to one relationship is still ... just ONE big giant table :-)

mx
0
 
LVL 18

Assisted Solution

by:Richard Daneke
Richard Daneke earned 250 total points
Comment Utility
When you have time, read through this link for some basic concepts on normalization.   The initial work you did was a first step, but there is a lot more work to do to provide you better flexibility in your system forms and reports;  and to reduce storage.
http://www.databasedev.co.uk/database_normalization_process.html
0
 
LVL 10

Assisted Solution

by:Michael Vasilevsky
Michael Vasilevsky earned 125 total points
Comment Utility
yeah that database is not properly normalized and needs some design work. One-to-one Community to Contacts? The fact the the table is called CommunityContacts alone tells you it should at least be one-to-many. Same with CommunityCurrentVendors. I think it's clear that this table should be one-to-many as well. CommunitySurvey090210 should probably be a one-to-many relationship Community to Surveys and record the survey date in the table instead of having a new table for every survey. Etc.
My 2 cents.

MV
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
I opened the main table, bottom of screenshot, and the three tables on the left side of your Relationships diagram, right side of the screen shot, with the Relationships diagram in the background.  Curiously, each joined table shows only two fields, CommunityID and Community.  None of the other fields in the three table designs show up when the table is viewed in datasheet view.
Clipboard01.jpg
0
 

Author Comment

by:Laura Sheldon
Comment Utility
DoDahD

OK, I got all my info back together in one table in the sample database and am running the analyzer on it and it's telling me that "Lookup Table 2 has no data fields, Please remove it." Does this make sense?
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Sorry, wrong screen shot.
Clipboard01.jpg
0
 
LVL 18

Assisted Solution

by:Richard Daneke
Richard Daneke earned 250 total points
Comment Utility
Yes, the window allows you to drag fields from the large table into new tables.  Access will create the links to the new tables.   You, however, need to drag fields from the large tables into the new tables you desire.  When you want another table, a button on the right will add another blank table for you to create.
You are rushing the analysis side of this table splitting wizard.  
0
 

Author Closing Comment

by:Laura Sheldon
Comment Utility
I think I finally got it all worked out!

Thanks everyone!
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now