Linking Fields from different tables (one table empty)
So I have 3 tables (table 3 being the issue but I have to explain the relationships for clarity):
Table 1: Information is provided by Home Realtors into our online database and updated automatically to an access database on my computer.
Info given:
Home Serial #
Home sq footage
Home Location
Table 2: Is in another database and linked to table #1 (same information copied for record keeping)
Info given:
Home Serial #
Home sq Footage
Home Location
***Below is where I'm having problems***...
Table 3: Is in the same database as Table 2 and is not linked to any other table, so it currently has no information and was created manually.
It has 3 fields:
Serial #
Home Cost
New Homeowner Name
I need the Serial # column to update when Table 2 is updated and contain the exact same information. The Home cost and Homeowner name will be updated manually by me.
I've tried different relationships to no avail. Any help would be appreciated.
Software/Hardware used:
Software/Hardware used:
ASKED:
December 29, 2010 8:51 PM
UPDATED:
March 11, 2011 2:38 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
Hi JRob,
What programs and versions are you using?
Hi,
I’m using Microsoft Access 2007
I think i see what you are trying to do. just a recap, to see if i have it right.
You have the web entry record, with a serial number as the key field, (which is linked into a DB stored somewhere else, with table(s) storing all the other property data. You didn’t mention this, but it is logical for it to be so.)
The web data is copied into your (local working) table1, which is then copied to table2 (for record keeping backup?), where table3 is linked into table2 by serial number. Table3, i think, is a many to 1 relationship with table 2.
May I suggest a few changes.
1. Incorporate tables 1 and 3 into a single data table, and copy the web record into it, (call it table1 still.)
2. Create a form to read table1 record, linking each field to it’s respective datatable. This should allow you to add what you want to the table3 fields and then append only the table3 fields into table3, leaving table1 untouched, and saved fields as received into table2.
3. The table3 will need to be amended to have the key field, (serial number) allow duplicates, so the keyfields can be linked as normal.
Please, keep us updated, even if it works, or you’ve found the solution yourself.