Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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...


Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page