Contact Records-extracting contact names to linked table

G

Guest

I have a table of industry contact information for locations across the
county many records are duplicate company names which have a different
contact name. I would like to make a primary table with company contact
information with a related table with the individual names from each
location. Is there a way to accomplish this through a query or two?

this is a sample of what I have to start
tbl contacts: (* primary key)
*ID company addy1 phone firstname lastname
001 ABC Inc. 123 a st. 123-4567 Joe Doe
002 ABC Inc. 123 a st. 123-4567 Jane Doe
003 ABC Inc. 123 a st. 123-4567 John Deer

What I need is a table with the company info as records and a linked table
with contact names for each company. There may be duplicated company names
in different states so I am not sure how make sure the info is link to each
company since the primary keys are different for each record.

Any suggestions would be appreciated
 
G

Guest

Here are some step-by-step instructions that you can try. Do this on a copy
of your database.

1.) Create the following query:

SELECT company, addy1, phone
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company;

Notes:
Use [tbl Contacts] in place of [tblContacts] if your contacts table includes
a space in it's name.

To use the SQL statement shown above, create a new query. Dismiss the Add
Tables dialog without adding any tables. In query design view, click on View
SQL View. You should see the word SELECT highlighted. Copy the SQL
Click to expand...
statement (Ctrl C) and paste it into the SQL view (Ctrl V), replacing the
SELECT keyword. You can then switch back to the more familiar design view, if
you wish, by clicking on View > Design View. Run the query.

Scan the data to check for duplicate entries. You will end up with duplicate
entries if the data in the company, addy1 and phone fields is not identical
for a given company record. For example, an address might be entered as "123
a st" in one record, "123 a st." (with a period) in another record, and "123
a street" in a third record. These entries, along with company names and/or
phone numbers that are not exactly the same will result in individual records
in the above query. Make any necessary corrections to your data, so that you
can run this query and produce unique records for each company.

2.) Create the following Make-Table query:

SELECT company, addy1, phone
INTO tblCompanies
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company

3.) Open your new tblCompanies table in design view. Add a new primary key
field. My recommendation is to name it pkCompanyID and to use an autonumber
data type.

4.) Open your existing contacts table in design view. Add a matching foreign
key field to this table. If you used an autonumber data type for your primary
key, then pick Number / Long Integer as the data type for this new foreign
key field. Name it something like fkCompanyID. Also, I recommend removing the
default value of 0 for numeric fields.

5.) Create the following update query to populate the values in your new
foreign key field:

UPDATE tblContacts INNER JOIN tblCompanies
ON (tblContacts.phone = tblCompanies.phone)
AND (tblContacts.addy1 = tblCompanies.addy1)
AND (tblContacts.company = tblCompanies.company)
SET tblContacts.fkCompanyID = tblCompanies.pkCompanyID


6.) Open tblContacts in design view. Delete the company, addy1 and phone
fields. Save your changes and close the table.

7.) Click on Tools > Relationships. Add both tables to the relationships
view (unless you already had your contacts table added). Create a
relationship with Enforce Referential Integrity checked as an option. You
should not need the Cascade Update option if you used an autonumber data type
for the primary key in tblCompanies. I do not recommend including Cascade
Delete. This is a dangerous option. To create the relationship, select and
drag pkCompanyID and drop it on top of fkCompanyID. Close the relationships
view and save changes.

Hope this helps.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
G

Guest

Thank you very much Tom, I will give this a try today. I am familiar with
SQL design of queries, but was unsure how to accomplish this in a effecient
maner.

Barry

Tom Wickerath said:
Here are some step-by-step instructions that you can try. Do this on a copy
of your database.

1.) Create the following query:

SELECT company, addy1, phone
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company;

Notes:
Use [tbl Contacts] in place of [tblContacts] if your contacts table includes
a space in it's name.

To use the SQL statement shown above, create a new query. Dismiss the Add
Tables dialog without adding any tables. In query design view, click on View
SQL View. You should see the word SELECT highlighted. Copy the SQL
Click to expand...
statement (Ctrl C) and paste it into the SQL view (Ctrl V), replacing the
SELECT keyword. You can then switch back to the more familiar design view, if
you wish, by clicking on View > Design View. Run the query.

Scan the data to check for duplicate entries. You will end up with duplicate
entries if the data in the company, addy1 and phone fields is not identical
for a given company record. For example, an address might be entered as "123
a st" in one record, "123 a st." (with a period) in another record, and "123
a street" in a third record. These entries, along with company names and/or
phone numbers that are not exactly the same will result in individual records
in the above query. Make any necessary corrections to your data, so that you
can run this query and produce unique records for each company.

2.) Create the following Make-Table query:

SELECT company, addy1, phone
INTO tblCompanies
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company

3.) Open your new tblCompanies table in design view. Add a new primary key
field. My recommendation is to name it pkCompanyID and to use an autonumber
data type.

4.) Open your existing contacts table in design view. Add a matching foreign
key field to this table. If you used an autonumber data type for your primary
key, then pick Number / Long Integer as the data type for this new foreign
key field. Name it something like fkCompanyID. Also, I recommend removing the
default value of 0 for numeric fields.

5.) Create the following update query to populate the values in your new
foreign key field:

UPDATE tblContacts INNER JOIN tblCompanies
ON (tblContacts.phone = tblCompanies.phone)
AND (tblContacts.addy1 = tblCompanies.addy1)
AND (tblContacts.company = tblCompanies.company)
SET tblContacts.fkCompanyID = tblCompanies.pkCompanyID


6.) Open tblContacts in design view. Delete the company, addy1 and phone
fields. Save your changes and close the table.

7.) Click on Tools > Relationships. Add both tables to the relationships
view (unless you already had your contacts table added). Create a
relationship with Enforce Referential Integrity checked as an option. You
should not need the Cascade Update option if you used an autonumber data type
for the primary key in tblCompanies. I do not recommend including Cascade
Delete. This is a dangerous option. To create the relationship, select and
drag pkCompanyID and drop it on top of fkCompanyID. Close the relationships
view and save changes.

Hope this helps.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

BDP III said:
I have a table of industry contact information for locations across the
county many records are duplicate company names which have a different
contact name. I would like to make a primary table with company contact
information with a related table with the individual names from each
location. Is there a way to accomplish this through a query or two?

this is a sample of what I have to start
tbl contacts: (* primary key)
*ID company addy1 phone firstname lastname
001 ABC Inc. 123 a st. 123-4567 Joe Doe
002 ABC Inc. 123 a st. 123-4567 Jane Doe
003 ABC Inc. 123 a st. 123-4567 John Deer

What I need is a table with the company info as records and a linked table
with contact names for each company. There may be duplicated company names
in different states so I am not sure how make sure the info is link to each
company since the primary keys are different for each record.

Any suggestions would be appreciated
Click to expand...
Click to expand...
 
G

Guest

Tom I am receiving a "sytax error in join operation" on this query (step 5 of
your post). Can you see what I am doing wrong.

UPDATE tblContacts INNER JOIN tblCompanies
ON (tbl.Contacts.phone=tblCompanies.phone)
AND (tbl.Contacts.Address1=tblCompanies.Address1)
AND (tbl.Contacts.Address2=tblCompanies.Address2)
AND (tbl.Contacts.City=tblCompanies.City)
AND (tbl.Contacts.State=tblCompanies.State)
AND (tbl.Contacts.Zip=tblCompanies.Zip)
SET tbl.Contacts.fkCompanyID = tblCompanies.pkCompanyID

After I click ok on the error the "tbl" in tblContacts is highlighted as the
error. Thanks again for your help on this.

Barry

Tom Wickerath said:
Here are some step-by-step instructions that you can try. Do this on a copy
of your database.

1.) Create the following query:

SELECT company, addy1, phone
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company;

Notes:
Use [tbl Contacts] in place of [tblContacts] if your contacts table includes
a space in it's name.

To use the SQL statement shown above, create a new query. Dismiss the Add
Tables dialog without adding any tables. In query design view, click on View
SQL View. You should see the word SELECT highlighted. Copy the SQL
Click to expand...
statement (Ctrl C) and paste it into the SQL view (Ctrl V), replacing the
SELECT keyword. You can then switch back to the more familiar design view, if
you wish, by clicking on View > Design View. Run the query.

Scan the data to check for duplicate entries. You will end up with duplicate
entries if the data in the company, addy1 and phone fields is not identical
for a given company record. For example, an address might be entered as "123
a st" in one record, "123 a st." (with a period) in another record, and "123
a street" in a third record. These entries, along with company names and/or
phone numbers that are not exactly the same will result in individual records
in the above query. Make any necessary corrections to your data, so that you
can run this query and produce unique records for each company.

2.) Create the following Make-Table query:

SELECT company, addy1, phone
INTO tblCompanies
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company

3.) Open your new tblCompanies table in design view. Add a new primary key
field. My recommendation is to name it pkCompanyID and to use an autonumber
data type.

4.) Open your existing contacts table in design view. Add a matching foreign
key field to this table. If you used an autonumber data type for your primary
key, then pick Number / Long Integer as the data type for this new foreign
key field. Name it something like fkCompanyID. Also, I recommend removing the
default value of 0 for numeric fields.

5.) Create the following update query to populate the values in your new
foreign key field:

UPDATE tblContacts INNER JOIN tblCompanies
ON (tblContacts.phone = tblCompanies.phone)
AND (tblContacts.addy1 = tblCompanies.addy1)
AND (tblContacts.company = tblCompanies.company)
SET tblContacts.fkCompanyID = tblCompanies.pkCompanyID


6.) Open tblContacts in design view. Delete the company, addy1 and phone
fields. Save your changes and close the table.

7.) Click on Tools > Relationships. Add both tables to the relationships
view (unless you already had your contacts table added). Create a
relationship with Enforce Referential Integrity checked as an option. You
should not need the Cascade Update option if you used an autonumber data type
for the primary key in tblCompanies. I do not recommend including Cascade
Delete. This is a dangerous option. To create the relationship, select and
drag pkCompanyID and drop it on top of fkCompanyID. Close the relationships
view and save changes.

Hope this helps.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

BDP III said:
I have a table of industry contact information for locations across the
county many records are duplicate company names which have a different
contact name. I would like to make a primary table with company contact
information with a related table with the individual names from each
location. Is there a way to accomplish this through a query or two?

this is a sample of what I have to start
tbl contacts: (* primary key)
*ID company addy1 phone firstname lastname
001 ABC Inc. 123 a st. 123-4567 Joe Doe
002 ABC Inc. 123 a st. 123-4567 Jane Doe
003 ABC Inc. 123 a st. 123-4567 John Deer

What I need is a table with the company info as records and a linked table
with contact names for each company. There may be duplicated company names
in different states so I am not sure how make sure the info is link to each
company since the primary keys are different for each record.

Any suggestions would be appreciated
Click to expand...
Click to expand...
 
G

Guest

Found my error .. sorry and thanks again Tom.

BDP III said:
Tom I am receiving a "sytax error in join operation" on this query (step 5 of
your post). Can you see what I am doing wrong.

UPDATE tblContacts INNER JOIN tblCompanies
ON (tbl.Contacts.phone=tblCompanies.phone)
AND (tbl.Contacts.Address1=tblCompanies.Address1)
AND (tbl.Contacts.Address2=tblCompanies.Address2)
AND (tbl.Contacts.City=tblCompanies.City)
AND (tbl.Contacts.State=tblCompanies.State)
AND (tbl.Contacts.Zip=tblCompanies.Zip)
SET tbl.Contacts.fkCompanyID = tblCompanies.pkCompanyID

After I click ok on the error the "tbl" in tblContacts is highlighted as the
error. Thanks again for your help on this.

Barry

Tom Wickerath said:
Here are some step-by-step instructions that you can try. Do this on a copy
of your database.

1.) Create the following query:

SELECT company, addy1, phone
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company;

Notes:
Use [tbl Contacts] in place of [tblContacts] if your contacts table includes
a space in it's name.

To use the SQL statement shown above, create a new query. Dismiss the Add
Tables dialog without adding any tables. In query design view, click on View
SQL View. You should see the word SELECT highlighted. Copy the SQL
Click to expand...
statement (Ctrl C) and paste it into the SQL view (Ctrl V), replacing the
SELECT keyword. You can then switch back to the more familiar design view, if
you wish, by clicking on View > Design View. Run the query.

Scan the data to check for duplicate entries. You will end up with duplicate
entries if the data in the company, addy1 and phone fields is not identical
for a given company record. For example, an address might be entered as "123
a st" in one record, "123 a st." (with a period) in another record, and "123
a street" in a third record. These entries, along with company names and/or
phone numbers that are not exactly the same will result in individual records
in the above query. Make any necessary corrections to your data, so that you
can run this query and produce unique records for each company.

2.) Create the following Make-Table query:

SELECT company, addy1, phone
INTO tblCompanies
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company

3.) Open your new tblCompanies table in design view. Add a new primary key
field. My recommendation is to name it pkCompanyID and to use an autonumber
data type.

4.) Open your existing contacts table in design view. Add a matching foreign
key field to this table. If you used an autonumber data type for your primary
key, then pick Number / Long Integer as the data type for this new foreign
key field. Name it something like fkCompanyID. Also, I recommend removing the
default value of 0 for numeric fields.

5.) Create the following update query to populate the values in your new
foreign key field:

UPDATE tblContacts INNER JOIN tblCompanies
ON (tblContacts.phone = tblCompanies.phone)
AND (tblContacts.addy1 = tblCompanies.addy1)
AND (tblContacts.company = tblCompanies.company)
SET tblContacts.fkCompanyID = tblCompanies.pkCompanyID


6.) Open tblContacts in design view. Delete the company, addy1 and phone
fields. Save your changes and close the table.

7.) Click on Tools > Relationships. Add both tables to the relationships
view (unless you already had your contacts table added). Create a
relationship with Enforce Referential Integrity checked as an option. You
should not need the Cascade Update option if you used an autonumber data type
for the primary key in tblCompanies. I do not recommend including Cascade
Delete. This is a dangerous option. To create the relationship, select and
drag pkCompanyID and drop it on top of fkCompanyID. Close the relationships
view and save changes.

Hope this helps.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

BDP III said:
I have a table of industry contact information for locations across the
county many records are duplicate company names which have a different
contact name. I would like to make a primary table with company contact
information with a related table with the individual names from each
location. Is there a way to accomplish this through a query or two?

this is a sample of what I have to start
tbl contacts: (* primary key)
*ID company addy1 phone firstname lastname
001 ABC Inc. 123 a st. 123-4567 Joe Doe
002 ABC Inc. 123 a st. 123-4567 Jane Doe
003 ABC Inc. 123 a st. 123-4567 John Deer

What I need is a table with the company info as records and a linked table
with contact names for each company. There may be duplicated company names
in different states so I am not sure how make sure the info is link to each
company since the primary keys are different for each record.

Any suggestions would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Tom, I have made it almost to the end (after soem very tedious data
correcting). I am having a problem with updating the fkCompanyID field in
tblContacts when i run the query it only finds 317 records to update, however
I have 10,323 records. It should be updating every record, correct? below
is my actual SQL if you can help any it would be greatly appreciated.

Barry

BDP III said:
Found my error .. sorry and thanks again Tom.

BDP III said:
Tom I am receiving a "sytax error in join operation" on this query (step 5 of
your post). Can you see what I am doing wrong.

UPDATE tblContacts INNER JOIN tblCompanies
ON (tbl.Contacts.phone=tblCompanies.phone)
AND (tbl.Contacts.Address1=tblCompanies.Address1)
AND (tbl.Contacts.Address2=tblCompanies.Address2)
AND (tbl.Contacts.City=tblCompanies.City)
AND (tbl.Contacts.State=tblCompanies.State)
AND (tbl.Contacts.Zip=tblCompanies.Zip)
SET tbl.Contacts.fkCompanyID = tblCompanies.pkCompanyID

After I click ok on the error the "tbl" in tblContacts is highlighted as the
error. Thanks again for your help on this.

Barry

Tom Wickerath said:
Here are some step-by-step instructions that you can try. Do this on a copy
of your database.

1.) Create the following query:

SELECT company, addy1, phone
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company;

Notes:
Use [tbl Contacts] in place of [tblContacts] if your contacts table includes
a space in it's name.

To use the SQL statement shown above, create a new query. Dismiss the Add
Tables dialog without adding any tables. In query design view, click on View
SQL View. You should see the word SELECT highlighted. Copy the SQL
statement (Ctrl C) and paste it into the SQL view (Ctrl V), replacing the
SELECT keyword. You can then switch back to the more familiar design view, if
you wish, by clicking on View > Design View. Run the query.

Scan the data to check for duplicate entries. You will end up with duplicate
entries if the data in the company, addy1 and phone fields is not identical
for a given company record. For example, an address might be entered as "123
a st" in one record, "123 a st." (with a period) in another record, and "123
a street" in a third record. These entries, along with company names and/or
phone numbers that are not exactly the same will result in individual records
in the above query. Make any necessary corrections to your data, so that you
can run this query and produce unique records for each company.

2.) Create the following Make-Table query:

SELECT company, addy1, phone
INTO tblCompanies
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company

3.) Open your new tblCompanies table in design view. Add a new primary key
field. My recommendation is to name it pkCompanyID and to use an autonumber
data type.

4.) Open your existing contacts table in design view. Add a matching foreign
key field to this table. If you used an autonumber data type for your primary
key, then pick Number / Long Integer as the data type for this new foreign
key field. Name it something like fkCompanyID. Also, I recommend removing the
default value of 0 for numeric fields.

5.) Create the following update query to populate the values in your new
foreign key field:

UPDATE tblContacts INNER JOIN tblCompanies
ON (tblContacts.phone = tblCompanies.phone)
AND (tblContacts.addy1 = tblCompanies.addy1)
AND (tblContacts.company = tblCompanies.company)
SET tblContacts.fkCompanyID = tblCompanies.pkCompanyID


6.) Open tblContacts in design view. Delete the company, addy1 and phone
fields. Save your changes and close the table.

7.) Click on Tools > Relationships. Add both tables to the relationships
view (unless you already had your contacts table added). Create a
relationship with Enforce Referential Integrity checked as an option. You
should not need the Cascade Update option if you used an autonumber data type
for the primary key in tblCompanies. I do not recommend including Cascade
Delete. This is a dangerous option. To create the relationship, select and
drag pkCompanyID and drop it on top of fkCompanyID. Close the relationships
view and save changes.

Hope this helps.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have a table of industry contact information for locations across the
county many records are duplicate company names which have a different
contact name. I would like to make a primary table with company contact
information with a related table with the individual names from each
location. Is there a way to accomplish this through a query or two?

this is a sample of what I have to start
tbl contacts: (* primary key)
*ID company addy1 phone firstname lastname
001 ABC Inc. 123 a st. 123-4567 Joe Doe
002 ABC Inc. 123 a st. 123-4567 Jane Doe
003 ABC Inc. 123 a st. 123-4567 John Deer

What I need is a table with the company info as records and a linked table
with contact names for each company. There may be duplicated company names
in different states so I am not sure how make sure the info is link to each
company since the primary keys are different for each record.

Any suggestions would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi, Barry.

Tom probably won't be available until this evening, so he asked me to step
in.
It should be updating every record, correct?
Click to expand...

Yes. It's quite possible that you have NULL's in the address2 column.
Ensure that you don't have any NULL values in any of the columns named in the
JOIN clauses of the query. NULL's are never equal to any value. And NULL's
aren't even equal to NULL. Also, ensure that the data in the columns for
each company in the tblCompanies table match with the corresponding columns
in the Contacts table, i.e., no multiple spellings for the same thing.

BTW, is the phone number for each company the same for all contacts? If
each contact within the same company has a different phone number, then
you'll want to save the phone number with the contact, not with the company,
so you'd need to structure your tables a little differently if this is the
case.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.


BDP III said:
Tom, I have made it almost to the end (after soem very tedious data
correcting). I am having a problem with updating the fkCompanyID field in
tblContacts when i run the query it only finds 317 records to update, however
I have 10,323 records. It should be updating every record, correct? below
is my actual SQL if you can help any it would be greatly appreciated.

Barry

BDP III said:
Found my error .. sorry and thanks again Tom.

BDP III said:
Tom I am receiving a "sytax error in join operation" on this query (step 5 of
your post). Can you see what I am doing wrong.

UPDATE tblContacts INNER JOIN tblCompanies
ON (tbl.Contacts.phone=tblCompanies.phone)
AND (tbl.Contacts.Address1=tblCompanies.Address1)
AND (tbl.Contacts.Address2=tblCompanies.Address2)
AND (tbl.Contacts.City=tblCompanies.City)
AND (tbl.Contacts.State=tblCompanies.State)
AND (tbl.Contacts.Zip=tblCompanies.Zip)
SET tbl.Contacts.fkCompanyID = tblCompanies.pkCompanyID

After I click ok on the error the "tbl" in tblContacts is highlighted as the
error. Thanks again for your help on this.

Barry

:

Here are some step-by-step instructions that you can try. Do this on a copy
of your database.

1.) Create the following query:

SELECT company, addy1, phone
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company;

Notes:
Use [tbl Contacts] in place of [tblContacts] if your contacts table includes
a space in it's name.

To use the SQL statement shown above, create a new query. Dismiss the Add
Tables dialog without adding any tables. In query design view, click on View
SQL View. You should see the word SELECT highlighted. Copy the SQL
statement (Ctrl C) and paste it into the SQL view (Ctrl V), replacing the
SELECT keyword. You can then switch back to the more familiar design view, if
you wish, by clicking on View > Design View. Run the query.

Scan the data to check for duplicate entries. You will end up with duplicate
entries if the data in the company, addy1 and phone fields is not identical
for a given company record. For example, an address might be entered as "123
a st" in one record, "123 a st." (with a period) in another record, and "123
a street" in a third record. These entries, along with company names and/or
phone numbers that are not exactly the same will result in individual records
in the above query. Make any necessary corrections to your data, so that you
can run this query and produce unique records for each company.

2.) Create the following Make-Table query:

SELECT company, addy1, phone
INTO tblCompanies
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company

3.) Open your new tblCompanies table in design view. Add a new primary key
field. My recommendation is to name it pkCompanyID and to use an autonumber
data type.

4.) Open your existing contacts table in design view. Add a matching foreign
key field to this table. If you used an autonumber data type for your primary
key, then pick Number / Long Integer as the data type for this new foreign
key field. Name it something like fkCompanyID. Also, I recommend removing the
default value of 0 for numeric fields.

5.) Create the following update query to populate the values in your new
foreign key field:

UPDATE tblContacts INNER JOIN tblCompanies
ON (tblContacts.phone = tblCompanies.phone)
AND (tblContacts.addy1 = tblCompanies.addy1)
AND (tblContacts.company = tblCompanies.company)
SET tblContacts.fkCompanyID = tblCompanies.pkCompanyID


6.) Open tblContacts in design view. Delete the company, addy1 and phone
fields. Save your changes and close the table.

7.) Click on Tools > Relationships. Add both tables to the relationships
view (unless you already had your contacts table added). Create a
relationship with Enforce Referential Integrity checked as an option. You
should not need the Cascade Update option if you used an autonumber data type
for the primary key in tblCompanies. I do not recommend including Cascade
Delete. This is a dangerous option. To create the relationship, select and
drag pkCompanyID and drop it on top of fkCompanyID. Close the relationships
view and save changes.

Hope this helps.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have a table of industry contact information for locations across the
county many records are duplicate company names which have a different
contact name. I would like to make a primary table with company contact
information with a related table with the individual names from each
location. Is there a way to accomplish this through a query or two?

this is a sample of what I have to start
tbl contacts: (* primary key)
*ID company addy1 phone firstname lastname
001 ABC Inc. 123 a st. 123-4567 Joe Doe
002 ABC Inc. 123 a st. 123-4567 Jane Doe
003 ABC Inc. 123 a st. 123-4567 John Deer

What I need is a table with the company info as records and a linked table
with contact names for each company. There may be duplicated company names
in different states so I am not sure how make sure the info is link to each
company since the primary keys are different for each record.

Any suggestions would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Gunny, I was able to figure this out and have accomplished this with a
pretty good success rate, thank Tom again for me.

Barry

'69 Camaro said:
Hi, Barry.

Tom probably won't be available until this evening, so he asked me to step
in.
It should be updating every record, correct?
Click to expand...

Yes. It's quite possible that you have NULL's in the address2 column.
Ensure that you don't have any NULL values in any of the columns named in the
JOIN clauses of the query. NULL's are never equal to any value. And NULL's
aren't even equal to NULL. Also, ensure that the data in the columns for
each company in the tblCompanies table match with the corresponding columns
in the Contacts table, i.e., no multiple spellings for the same thing.

BTW, is the phone number for each company the same for all contacts? If
each contact within the same company has a different phone number, then
you'll want to save the phone number with the contact, not with the company,
so you'd need to structure your tables a little differently if this is the
case.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.


BDP III said:
Tom, I have made it almost to the end (after soem very tedious data
correcting). I am having a problem with updating the fkCompanyID field in
tblContacts when i run the query it only finds 317 records to update, however
I have 10,323 records. It should be updating every record, correct? below
is my actual SQL if you can help any it would be greatly appreciated.

Barry

BDP III said:
Found my error .. sorry and thanks again Tom.

:

Tom I am receiving a "sytax error in join operation" on this query (step 5 of
your post). Can you see what I am doing wrong.

UPDATE tblContacts INNER JOIN tblCompanies
ON (tbl.Contacts.phone=tblCompanies.phone)
AND (tbl.Contacts.Address1=tblCompanies.Address1)
AND (tbl.Contacts.Address2=tblCompanies.Address2)
AND (tbl.Contacts.City=tblCompanies.City)
AND (tbl.Contacts.State=tblCompanies.State)
AND (tbl.Contacts.Zip=tblCompanies.Zip)
SET tbl.Contacts.fkCompanyID = tblCompanies.pkCompanyID

After I click ok on the error the "tbl" in tblContacts is highlighted as the
error. Thanks again for your help on this.

Barry

:

Here are some step-by-step instructions that you can try. Do this on a copy
of your database.

1.) Create the following query:

SELECT company, addy1, phone
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company;

Notes:
Use [tbl Contacts] in place of [tblContacts] if your contacts table includes
a space in it's name.

To use the SQL statement shown above, create a new query. Dismiss the Add
Tables dialog without adding any tables. In query design view, click on View
SQL View. You should see the word SELECT highlighted. Copy the SQL
statement (Ctrl C) and paste it into the SQL view (Ctrl V), replacing the
SELECT keyword. You can then switch back to the more familiar design view, if
you wish, by clicking on View > Design View. Run the query.

Scan the data to check for duplicate entries. You will end up with duplicate
entries if the data in the company, addy1 and phone fields is not identical
for a given company record. For example, an address might be entered as "123
a st" in one record, "123 a st." (with a period) in another record, and "123
a street" in a third record. These entries, along with company names and/or
phone numbers that are not exactly the same will result in individual records
in the above query. Make any necessary corrections to your data, so that you
can run this query and produce unique records for each company.

2.) Create the following Make-Table query:

SELECT company, addy1, phone
INTO tblCompanies
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company

3.) Open your new tblCompanies table in design view. Add a new primary key
field. My recommendation is to name it pkCompanyID and to use an autonumber
data type.

4.) Open your existing contacts table in design view. Add a matching foreign
key field to this table. If you used an autonumber data type for your primary
key, then pick Number / Long Integer as the data type for this new foreign
key field. Name it something like fkCompanyID. Also, I recommend removing the
default value of 0 for numeric fields.

5.) Create the following update query to populate the values in your new
foreign key field:

UPDATE tblContacts INNER JOIN tblCompanies
ON (tblContacts.phone = tblCompanies.phone)
AND (tblContacts.addy1 = tblCompanies.addy1)
AND (tblContacts.company = tblCompanies.company)
SET tblContacts.fkCompanyID = tblCompanies.pkCompanyID


6.) Open tblContacts in design view. Delete the company, addy1 and phone
fields. Save your changes and close the table.

7.) Click on Tools > Relationships. Add both tables to the relationships
view (unless you already had your contacts table added). Create a
relationship with Enforce Referential Integrity checked as an option. You
should not need the Cascade Update option if you used an autonumber data type
for the primary key in tblCompanies. I do not recommend including Cascade
Delete. This is a dangerous option. To create the relationship, select and
drag pkCompanyID and drop it on top of fkCompanyID. Close the relationships
view and save changes.

Hope this helps.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have a table of industry contact information for locations across the
county many records are duplicate company names which have a different
contact name. I would like to make a primary table with company contact
information with a related table with the individual names from each
location. Is there a way to accomplish this through a query or two?

this is a sample of what I have to start
tbl contacts: (* primary key)
*ID company addy1 phone firstname lastname
001 ABC Inc. 123 a st. 123-4567 Joe Doe
002 ABC Inc. 123 a st. 123-4567 Jane Doe
003 ABC Inc. 123 a st. 123-4567 John Deer

What I need is a table with the company info as records and a linked table
with contact names for each company. There may be duplicated company names
in different states so I am not sure how make sure the info is link to each
company since the primary keys are different for each record.

Any suggestions would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
6

'69 Camaro

You're welcome. Glad you got it working.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


BDP III said:
Thanks Gunny, I was able to figure this out and have accomplished this
with a
pretty good success rate, thank Tom again for me.

Barry

'69 Camaro said:
Hi, Barry.

Tom probably won't be available until this evening, so he asked me to
step
in.
It should be updating every record, correct?
Click to expand...

Yes. It's quite possible that you have NULL's in the address2 column.
Ensure that you don't have any NULL values in any of the columns named in
the
JOIN clauses of the query. NULL's are never equal to any value. And
NULL's
aren't even equal to NULL. Also, ensure that the data in the columns for
each company in the tblCompanies table match with the corresponding
columns
in the Contacts table, i.e., no multiple spellings for the same thing.

BTW, is the phone number for each company the same for all contacts? If
each contact within the same company has a different phone number, then
you'll want to save the phone number with the contact, not with the
company,
so you'd need to structure your tables a little differently if this is
the
case.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


BDP III said:
Tom, I have made it almost to the end (after soem very tedious data
correcting). I am having a problem with updating the fkCompanyID field
in
tblContacts when i run the query it only finds 317 records to update,
however
I have 10,323 records. It should be updating every record, correct?
below
is my actual SQL if you can help any it would be greatly appreciated.

Barry

:

Found my error .. sorry and thanks again Tom.

:

Tom I am receiving a "sytax error in join operation" on this query
(step 5 of
your post). Can you see what I am doing wrong.

UPDATE tblContacts INNER JOIN tblCompanies
ON (tbl.Contacts.phone=tblCompanies.phone)
AND (tbl.Contacts.Address1=tblCompanies.Address1)
AND (tbl.Contacts.Address2=tblCompanies.Address2)
AND (tbl.Contacts.City=tblCompanies.City)
AND (tbl.Contacts.State=tblCompanies.State)
AND (tbl.Contacts.Zip=tblCompanies.Zip)
SET tbl.Contacts.fkCompanyID = tblCompanies.pkCompanyID

After I click ok on the error the "tbl" in tblContacts is
highlighted as the
error. Thanks again for your help on this.

Barry

:

Here are some step-by-step instructions that you can try. Do this
on a copy
of your database.

1.) Create the following query:

SELECT company, addy1, phone
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company;

Notes:
Use [tbl Contacts] in place of [tblContacts] if your contacts
table includes
a space in it's name.

To use the SQL statement shown above, create a new query. Dismiss
the Add
Tables dialog without adding any tables. In query design view,
click on View
SQL View. You should see the word SELECT highlighted. Copy the
SQL
statement (Ctrl C) and paste it into the SQL view (Ctrl V),
replacing the
SELECT keyword. You can then switch back to the more familiar
design view, if
you wish, by clicking on View > Design View. Run the query.

Scan the data to check for duplicate entries. You will end up
with duplicate
entries if the data in the company, addy1 and phone fields is not
identical
for a given company record. For example, an address might be
entered as "123
a st" in one record, "123 a st." (with a period) in another
record, and "123
a street" in a third record. These entries, along with company
names and/or
phone numbers that are not exactly the same will result in
individual records
in the above query. Make any necessary corrections to your data,
so that you
can run this query and produce unique records for each company.

2.) Create the following Make-Table query:

SELECT company, addy1, phone
INTO tblCompanies
FROM [tblContacts]
GROUP BY company, addy1, phone
ORDER BY company

3.) Open your new tblCompanies table in design view. Add a new
primary key
field. My recommendation is to name it pkCompanyID and to use an
autonumber
data type.

4.) Open your existing contacts table in design view. Add a
matching foreign
key field to this table. If you used an autonumber data type for
your primary
key, then pick Number / Long Integer as the data type for this
new foreign
key field. Name it something like fkCompanyID. Also, I recommend
removing the
default value of 0 for numeric fields.

5.) Create the following update query to populate the values in
your new
foreign key field:

UPDATE tblContacts INNER JOIN tblCompanies
ON (tblContacts.phone = tblCompanies.phone)
AND (tblContacts.addy1 = tblCompanies.addy1)
AND (tblContacts.company = tblCompanies.company)
SET tblContacts.fkCompanyID = tblCompanies.pkCompanyID


6.) Open tblContacts in design view. Delete the company, addy1
and phone
fields. Save your changes and close the table.

7.) Click on Tools > Relationships. Add both tables to the
relationships
view (unless you already had your contacts table added). Create a
relationship with Enforce Referential Integrity checked as an
option. You
should not need the Cascade Update option if you used an
autonumber data type
for the primary key in tblCompanies. I do not recommend including
Cascade
Delete. This is a dangerous option. To create the relationship,
select and
drag pkCompanyID and drop it on top of fkCompanyID. Close the
relationships
view and save changes.

Hope this helps.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have a table of industry contact information for locations
across the
county many records are duplicate company names which have a
different
contact name. I would like to make a primary table with
company contact
information with a related table with the individual names from
each
location. Is there a way to accomplish this through a query or
two?

this is a sample of what I have to start
tbl contacts: (* primary key)
*ID company addy1 phone firstname
lastname
001 ABC Inc. 123 a st. 123-4567 Joe
Doe
002 ABC Inc. 123 a st. 123-4567 Jane
Doe
003 ABC Inc. 123 a st. 123-4567 John
Deer

What I need is a table with the company info as records and a
linked table
with contact names for each company. There may be duplicated
company names
in different states so I am not sure how make sure the info is
link to each
company since the primary keys are different for each record.

Any suggestions would be appreciated
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to find similar records? 8
Separating table, maintaining link without losing data? 4
Qry to put list of contacts in one single row 2
not exact duplicates... 3
extracting data from one format to a different format... 2
Appending records to new table using field names as values 2
Changed relationship; records no longer linked 5
Multiple Vendors for products - Table relationships 4

Top