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

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/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"BDP III" wrote:

> 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

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" wrote:

> 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/ex...tributors.html
> http://www.access.qbuilt.com/html/search.html
> __________________________________________
>
> "BDP III" wrote:
>
> > 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

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" wrote:

> 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/ex...tributors.html
> http://www.access.qbuilt.com/html/search.html
> __________________________________________
>
> "BDP III" wrote:
>
> > 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

Found my error .. sorry and thanks again Tom.

"BDP III" wrote:

> 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" wrote:
>
> > 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/ex...tributors.html
> > http://www.access.qbuilt.com/html/search.html
> > __________________________________________
> >
> > "BDP III" wrote:
> >
> > > 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

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" wrote:

> Found my error .. sorry and thanks again Tom.
>
> "BDP III" wrote:
>
> > 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" wrote:
> >
> > > 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/ex...tributors.html
> > > http://www.access.qbuilt.com/html/search.html
> > > __________________________________________
> > >
> > > "BDP III" wrote:
> > >
> > > > 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

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?

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/ex...ributors2.html for contact info.


"BDP III" wrote:

> 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" wrote:
>
> > Found my error .. sorry and thanks again Tom.
> >
> > "BDP III" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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/ex...tributors.html
> > > > http://www.access.qbuilt.com/html/search.html
> > > > __________________________________________
> > > >
> > > > "BDP III" wrote:
> > > >
> > > > > 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

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" wrote:

> 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?
>
> 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/ex...ributors2.html for contact info.
>
>
> "BDP III" wrote:
>
> > 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" wrote:
> >
> > > Found my error .. sorry and thanks again Tom.
> > >
> > > "BDP III" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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/ex...tributors.html
> > > > > http://www.access.qbuilt.com/html/search.html
> > > > > __________________________________________
> > > > >
> > > > > "BDP III" wrote:
> > > > >
> > > > > > 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

Hi BDP,

You just did. I'm glad to read that you've had pretty good success. Dealing
with duplicate records can be a bit tedious.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"BDP III" wrote:

> 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

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/ex...ributors2.html for contact
info.


"BDP III" <BDP (E-Mail Removed)> wrote in message
news:098597A7-F36A-46A9-96E3-(E-Mail Removed)...
> 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" wrote:
>
>> 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?
>>
>> 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/ex...ributors2.html for contact
>> info.
>>
>>
>> "BDP III" wrote:
>>
>> > 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" wrote:
>> >
>> > > Found my error .. sorry and thanks again Tom.
>> > >
>> > > "BDP III" wrote:
>> > >
>> > > > 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" wrote:
>> > > >
>> > > > > 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/ex...tributors.html
>> > > > > http://www.access.qbuilt.com/html/search.html
>> > > > > __________________________________________
>> > > > >
>> > > > > "BDP III" wrote:
>> > > > >
>> > > > > > 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