SQL Help!

Results 1 to 4 of 4

Thread: SQL Help!

  1. #1
    Matt Harvey Guest

    Default SQL Help!

    I have a table in Access that has the following structure:<BR><BR>CompanyID, blah, blah...PhoneNum, blah...n<BR><BR>where companyID is the primary key<BR><BR>Some records are duplicates except for the companyID field. What I want to do, keying off the PhoneNum, is create a query that will return a new table with OldCompanyID and NewCompanyID as the fields, where oldCompanyID is CompanyID &#062;3100 and the newCOmpanyID is the ID for the same company but &#060; 3100. The easiest way to compare the 2 are with PhoneNum. For example, say that my existing table has these records:<BR><BR>CompanyID Name PhoneNum <BR>5403 ABC 212-212-2222<BR>312 ABC 212-212-2222<BR><BR>The query should return:<BR><BR>OldCompanyID NewCompanyID<BR>312 5403<BR>.<BR>.<BR>.<BR>Can anyone give me some help?<BR><BR><BR>Thanks,<BR><BR>Matt

  2. #2
    Tim Snyder Guest

    Default RE: SQL Help!

    This type of analysis is not easy to do in a database.....<BR>Suggest you try Perl

  3. #3
    Join Date
    Dec 1969

    Default RE: SQL Help!

    SELECT oldCompany, (SELECT newCompany FROM companies WHERE phoneNum = &#039;" & phoneNumber & "&#039; AND companyId &#062; 3100) FROM companies WHERE phoneNum = &#039;" & phoneNumber & "&#039; AND companyId &#060; 3100<BR><BR>Or something like that, can&#039;t remember specifically. Give it a whirl

  4. #4
    Join Date
    Dec 1969

    Default Self join

    &nbsp;<BR>select o.companyid,n.companyid from inxs o join inxs n<BR>on o.phone = n.phone <BR>and o.companyid &#060; 3100 and n.companyid &#062; 3100

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts