Results 1 to 5 of 5
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    why too many records? (Access 2003)

    I have two tables, Table1=205,343 records, Table2 = 71,577 records

    I wrote a query to do a lookup of two fields in Table2 against the same two fields in Table1 so that I can determine a 3rd field (only present in Table1). The query results in 79,877 records, why is that when I want a match for the 71,577 records in Table2? The SQL is very simple:
    <pre>SELECT cu.City, cu.State, cu.Zip, cu.NPcode, cu.NXcode as cu, ph.Lcode as ph
    FROM Customers as cu, Phone as ph
    WHERE ((cu!NPcode=ph!NPcode And cu!NXcode=ph!NXcode));</pre>

    (I added the 'as' statement in the FROM clause)

    I only use queries no forms, reports, etc. and am an Access newbie (I know Excel really well but the data is too big to fit in a single worksheet). I need to find the corresponding LCode for the NPcode/NXcode fields.

    Thnx,
    Deb

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: why too many records? (Access 2003)

    Does a combination of NPCode and NXCode determine a unique record in the phones table ?
    If there are distinct records that share the same NPCode and NXCode then both records will be returned for the one customer.

    If they are actually duplicate records, the problem can be fixed by putting Distinct after Select.

    If they are not duplicates, then they have different LCodes and you don't know which one you really want.

    PS In Access it is normal to Join the two tables to have a join clause in the
    SQL rather than a where clause. In the query grid drag the NPCode field from Customers and drop on the corresponding field in Phones, then repeat for NXCode.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: why too many records? (Access 2003)

    Apparently, there are duplicates in one or either table, and/or multiple matches for some of the records. See if you lose the extra records if you use SELECT DISTINCT instead of SELECT.

    Note: you can also write the SQL as

    SELECT cu.City, cu.State, cu.Zip, cu.NPcode, cu.NXcode as cu, ph.Lcode as ph
    FROM Customers as cu INNER JOIN Phone as ph ON ((cu!NPcode=ph!NPcode And cu!NXcode=ph!NXcode));

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: why too many records? (Access 2003)

    Yes only the combination of NPCode+NXCode are unique not the codes themselves. For any given city/state there can be multiple NPCode+NXCode pairs (these relate to area codes and phone prefixes, actually).

    I wrote the SQL myself as I found the 'wizard' a bit confusing for me because when I used it, I saw the JOIN clause which I thought was why I had more records than the source file.

    Ok I just now added DISTINCT and got 40,295 records. At least now I can import it back to Excel and do my other analysis there (since I'm much better at Excel and the people who want this report are more used to Excel than Access). Now that I have this 'master' list I need to do further lookup against 5 other Excel files to get their LCodes. I tried doing the lookups in Excel (array formulas) but it was so slow and after almost 3 hrs of crunching Excel died. I'll try doing it all in Access and exporting the results.

    Thanks <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    Deb

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: why too many records? (Access 2003)

    FWIW, I once posed the question to Joe Celko (the SQL guru) about whether or not it made a difference in how you constructed a query (that is, Left Join, Right Join, etc.). He actually said that he would use the format:
    SELECT A.*,B.* from A,B where A.keyfield = B.keyfield

    His reasoning is that this allows the query optimizer to make the necessary decisions.

    Such a query isn't updatable (at least in Access), but it is easier to write when you can't use the QBE grid!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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