Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Re: Step 5 problem

Discussion in 'Information Technology' started by '69 Camaro, Jul 28, 2006.

Tags:
  1. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    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" <BDP 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
    >> 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" 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/expert_contributors.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
     
  2. Loading...


Share This Page