Page 4 of 4 FirstFirst 1234
Results 46 to 55 of 55
  1. #46
    UCBFireCenter is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2009
    Location
    Berkeley, CA
    Posts
    38

    Now...how to get back to square one....

    Okay, well, I think I've set up the module to work as well as it can...



    So, now that I have run this "make table" query creating an "owner class" table that has 3 fields: 1) APN (which in the "owner class" table became Exp 1), 2) Owner1 name, 3) Ownerclass (i.e. GOVT, Non-buisness/ind, etc). I need to run another query that will combine the fields of "owner class" to the original table "Humboldt_Join_noURB" which contains other fields I need (i.e. address,x & y-coordinates, etc).

    So, "owner class" table (after the make table query is run) shows having 37,153 records...which sounds right because there was exactly 37,153 records in the "Humboldt_Join_noURB" table.

    But, watch this...

    So, I go into relationships...and create a one-to-one relationship between between the "Exp 1" field (in "owner class") and the "APN" field (in "Humboldt_Join_noURB")....then I query using the SQL:

    SELECT Humboldt_OwnerClass.*, Humboldt_join_noURB.*
    FROM Humboldt_OwnerClass INNER JOIN Humboldt_join_noURB ON Humboldt_OwnerClass.Expr1 = Humboldt_join_noURB.Humboldt_firethreat_APN;
    and...instead of 37,153 records...I get 38,151 records....

    Two tables joined with precisely 37,153 records should return 37,153...but it doesn't...

    What did I do wrong??

  2. #47
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Try using a LEFT JOIN.

  3. #48
    UCBFireCenter is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2009
    Location
    Berkeley, CA
    Posts
    38
    Same Problem arises (with Left and Right Join)....

  4. #49
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Hmm...how about sending me a fresh copy of what you have so far? You still have the addy right?

  5. #50
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You are aware of the fact that both tables have duplicate records for the field you are trying to match up right? Without the Join field being unique in both tables I do not see how you are going to do the matchup. I'll keep looking at it.

  6. #51
    UCBFireCenter is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2009
    Location
    Berkeley, CA
    Posts
    38
    I thought (I was fairly sure in fact), that the APN field was unique field no duplicates....would making that a primary key help?

    I might have a bigger problem then I thought, because I've performed an inner join on these records before....did this create duplicates, or...oh dear.

    If that original join was flawed, this whole thing is flawed.

  7. #52
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If you index on that field with "no duplicates allowed" it will fail because of the duplicates. You can use the query wizard to show you the duplicate records.

  8. #53
    UCBFireCenter is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2009
    Location
    Berkeley, CA
    Posts
    38
    Did that...306 found.

    Does Query Wizard delete these from the original table? I'm having problems finding the duplicates in the table I queried.

  9. #54
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    No records are deleted. It should return a recordset with the duplicates. Mine did.

  10. #55
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I returned the FACRES field as well so I could see the difference.

Page 4 of 4 FirstFirst 1234
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Return records not updated by update query
    By ars80 in forum Queries
    Replies: 2
    Last Post: 05-01-2006, 09:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums