Relating Access Tables

[rshafran]

I will probably embarrass myself with this most basic question, but I am a newcomer to Access.

The question below illustrates the essence of what I am trying to accomplish:

I have two tables in a database. Table A has 2 variables, ID (primary key, autonumber) and Last Name. Table B has 3 variables: ID, Last Name and Age. What I want to be able to do is use a Form to enter ID and Last Name in Table A, and have those data show up identically in Table B. (I can then use another form to add Age to Table B.)

Any help would be greatly appreciated!

 

[OBP]

rshafran, you do not need to put the Last Name and Age in the Second Table.
The Age can go in the First table with the Name.
If you want to understand Relating tables then it is normal to have the data in the Second table where you have more than one record for each record in the First Table.
A typical example would be CD or DVDs where you have the CD Or DVD Name in the First Table and then have the Sound Tracks or Film Names in the second table.
You would then have The ID in the first Table as Autonumber, a separate Track or Film ID Autonumber in the second table AND a CDID or DVDID which is NOT an Autonumber. It must be an Long Integer or Double Number.
The Tables are then related by First Table ID = CDID or DVDID.

Another example of such tables would be Family Names where you have Surnames in the First Table along with say Address and in the second Table have all the First Names and Ages.

 

[rshafran]

Thanks for the reply. It is obvious that I did not provide sufficient information to fully describe my situation.

I need to split records between to tables because I have over 255 variables that I am tracking. So, for example, in Table A, I would have 100 demographic variables on subjects, while in Table B I have 200 survey questionnaire variables. The information for Table A is acquired prior to the information in Table B. I want certain demographic variables from Table A (i.e., ID#, Last Name, etc.) to automatically appear in Table B, so that Table B contains the identical subject list as Table A. THen I can use another Form to enter the survey data into Table B.

Could you please be more specific as to how to establish the relationship? I have been all over the program, have created relationships in every which way I know how, and cannot get it to work. I am missing something basic. Your equation may be it, but I don't see where one enters such an equation/

I am working with Access 2003.

 

[OBP]

rshafran, the Last Name does not need to appear in the Second Table just the ID#.
The way that I would structure this is have the ID# in table 1 as an Autonumber and in Table 2 as a Long Integer or Double Number depending on how many records you expect to have.
Long Integer is only good for about 35,000 records.
In the Relationships relate ID# to ID#.
Have you considered having the data on a Single Form with Tabbed Subforms?
This would allow you to just enter the ID# in the Main form for the First Table and it would be automatically entered for the Second Table.
The only other way to synchronise tables would be to use VBA to update the Second Table when the ID# is entered in the First Table.
If you need any help with this I am quite happy to assist you.

 

[rshafran]

Can't thank you enough. I feel we are getting close to a solution.

1) I have tried establishing a relationship between ID Autonumber in Table A and ID Long Integer in Table B. I have set it as a one-to-one, with referential integrity set to On, and Cascade Update Related Fields checked. When I enter data in Table A, nothing happens in Table B. Is there some kind of update function that I need to run? What am I missing?

2) I would very much like to explore the tabbed form idea. I had created a tabbed form, but I am only able to relate the entirety of it to a single database. Would you be so kind as to walk me through the very specific steps for accomplishing what you suggest?

 

[OBP]

rshafran, I am afraid that just relating the tables does not actually put the data in to the second Table, it only controls "how" it goes in.
If you would like to do a simple Tabbed form then Create a MainForm that just has the ID Autonumber and say one other field on it.
Add the Tabbed Forms to the main form.
In the First Tab add some Fields from your First table, you may need to add the ID field as well.
Create a Form with a couple of Feilds from your second table.
Add this form to your MainForm's second Tab as a Subform.
Use the ID in the Mainform and the ID in the Subform as the Master/Child Links.
When you add a record to the Mainform you should be able to add records to the First tab
and when you go to the second tab the Master/Child link should put in the ID numbe for the Subform for the second table.
If you can post a zipped copy of the Database on here (less data) I will have a go at setting it up for you.

 

[rshafran]

How do I post the database?

 

[OBP]

Underneath where you post your replies is a button for "Go Advanced".
Click that and then "Manage Attachments", browse to you "Zipped" database and then "Upload" it.
It must be zipped.

 

[slurpee55]

Thanks for the reply. It is obvious that I did not provide sufficient information to fully describe my situation.

I need to split records between to tables because I have over 255 variables that I am tracking. So, for example, in Table A, I would have 100 demographic variables on subjects, while in Table B I have 200 survey questionnaire variables. The information for Table A is acquired prior to the information in Table B. I want certain demographic variables from Table A (i.e., ID#, Last Name, etc.) to automatically appear in Table B, so that Table B contains the identical subject list as Table A. THen I can use another Form to enter the survey data into Table B.

Could you please be more specific as to how to establish the relationship? I have been all over the program, have created relationships in every which way I know how, and cannot get it to work. I am missing something basic. Your equation may be it, but I don't see where one enters such an equation/

I am working with Access 2003.

As someone who works in market research, I fail to understand why you even want last names mixed with your data and demographics. First, unless you have requested from the participants to be able to be release their names and they have allowed it, then you are breaching ethical standards, and second, assuming that you have made this request and they all have consented, then you still have the names in your first table and you may link the information together in a simple query by using the ID.

 

[OBP]

slurpee, exactly.

 

[rshafran]

Thanks for the reply. Please don't get worked up about the ethics of including names, etc. I am only using variable names as examples. What I need help with is the mechanics of linking the two tables with some identical variable. Can someone please WALK ME THROUGH THE KEYSTROKES that accomplish this task? I have tried all combinations that seem intuitive, and nothing works. I KNOW I am missing something very basic: after all, this is what Access was designed to be able to do! I just don't know how to do it.

Any help will be appreciated.

 

[OBP]

rshafran, take a copy of the database, delete all the records from it's Tables, Compact & Repair it, Zip it and post it on here and one of us will fix it for you.

 

[rshafran]

Here is the database, compacted, zipped, and delivered. All I want to be able to do is link the two tables, so that as cases are entered into "Contact" I get at least the unique identifier in "Survey." (Actually, I want to be able to have several variables from Contact appear in Survey - for very specific reasons I need not go into. If that cannot be done, I need to know.)

I do appreciate your time and effort on my behalf. I hope this will do it. Thanks for being patient.

 

[OBP]

rshafran, I have looked at you database and made some corrections, the only fields from the first table that should be in the survey table is the ID number. I have created a ContacID Field to hold this.
I have created the required Relationships.
I have created a Query and Form for each table, the survey Form only holds the first few fields for demo purposes. There are too many fields for one User Friendly Form.
The Contact form has Tabbed Subforms so that you can split up the data in to meaningful
Groups to make it manageable.
I have put the first Survey Form on to the first TAB as a Subform so that you can see how it works.
I have created 3 dummy records and filled in some survey fields for the first one.

 

[rshafran]

Thanks, but I don't see how you've solved the problem. If I enter new cases in the Contact database via the form, I still only have one case with one Contact ID in the Survey database? If I have 5 cases in the Contact database, shouldn't I have 5 matching IDs in the Survey database?