If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Linking First & Last Names in a table to a Full Name field in anot
Hi. We have been using Access for a project database for years. However, we
just added our customer information (formerly in ACT) to Access. Now I have two fields in our contact table (First Name, Last Name) but only one field in the existing project table (Full Name). I would like to link the customer to his active projects in our existing project table. How do I link these so that when I view a contact I will see a list of his projects? I have created a query that combines the First Name and Last Name but then I am not sure what to do with that query field. Thanks, Stefanie |
#2
|
|||
|
|||
Linking First & Last Names in a table to a Full Name field in anot
You can use that query in another to join it with the Full Name field.
But better would be to create a NameList table with primary key and unique index of First Name & Last Name. Populate it from both tables to build a consolidated table, checking for spelling errors and typos. Then match it back to your two tables by adding a foreign key field related to the NameList table primary key. -- Build a little, test a little. "steflark" wrote: Hi. We have been using Access for a project database for years. However, we just added our customer information (formerly in ACT) to Access. Now I have two fields in our contact table (First Name, Last Name) but only one field in the existing project table (Full Name). I would like to link the customer to his active projects in our existing project table. How do I link these so that when I view a contact I will see a list of his projects? I have created a query that combines the First Name and Last Name but then I am not sure what to do with that query field. Thanks, Stefanie |
#3
|
|||
|
|||
Linking First & Last Names in a table to a Full Name field in anot
Hi,
You really need to change your databases' structure. Right now, if you have two John Smiths, how do you distinguish the one from the other? First make a backup or two of the database. You should add a person ID column to the contact table so that there is a way to identify each person in some unique way. Typically the column would be of the AutoNumber type, which creates unique numbers within the table. Make this the primary key. When you save, it will automatically assign a number. Then in the project table add a person ID column also, but make it a Long Integer Number type. This is the type that is used for AutoNumber columns. Run an update query to set the values in the project table to the values in the contact table. (Post back with your modified table structures if you are not able to figure it out.) You should also have a project ID column in the projects table that is similar to the person ID in the contact table (i.e. AutoNumber). If you do all this, then when you create your queries you will link on the person ID, not the name(s). You will also be able to create and enforce a referential integrity relationship. Hope this helps, Clifford Bass steflark wrote: Hi. We have been using Access for a project database for years. However, we just added our customer information (formerly in ACT) to Access. Now I have two fields in our contact table (First Name, Last Name) but only one field in the existing project table (Full Name). I would like to link the customer to his active projects in our existing project table. How do I link these so that when I view a contact I will see a list of his projects? I have created a query that combines the First Name and Last Name but then I am not sure what to do with that query field. Thanks, Stefanie -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200912/1 |
#4
|
|||
|
|||
Linking First & Last Names in a table to a Full Name field in anot
On Tue, 29 Dec 2009 10:06:01 -0800, steflark
wrote: Hi. We have been using Access for a project database for years. However, we just added our customer information (formerly in ACT) to Access. Now I have two fields in our contact table (First Name, Last Name) but only one field in the existing project table (Full Name). I would like to link the customer to his active projects in our existing project table. How do I link these so that when I view a contact I will see a list of his projects? I have created a query that combines the First Name and Last Name but then I am not sure what to do with that query field. Thanks, Stefanie Let me just agree with Karl and Clifford. A primary key should meet three criteria: it *must* be unique, and should also be stable and short. Names fail all three! I once worked with Dr. Lawrence David Wise and his colleague, Dr. Lawrence David Wise. People can change their names, by marriage or by legal name change. Names can be nicknamed - is "Robert Jones" the same person as "Bob Jones"? Restructure your table with a unique CustomerID and use THAT to link to your projects. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|