Re: Step 5 problem

#1 (**permalink**) 28-Jul-2006, 08:00 AM

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" III@discussions.microsoft.com> wrote in message
news:098597A7-F36A-46A9-96E3-2558E9781E5E@microsoft.com...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9220-re-step-5-problem.html
>> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9220
>> > > >
>> > > > 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