Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: comparing fields in two tables

    All,
    I have two tables. One has two fields and the other has four fields. An account number field exist in both. I need to compare both account number fields to view if there is an account number in one field but not the other. I tried unmatched query but it only compares records. I've not had to do msaccess queries in a while. Please help

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Create a new query using the two tables. Join the tables using the account number, then click on the line that joins the table and select join properties. You will find three options, now select the option all records from table 1 and only those records that match in table 2. Or you could select the option that will use all records from table 2 and only the matching records from table 1.

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    I tried this before. This is what I'd normally do. But I think it has something to do with the fact that the tables were imported into the database from a text file and the acct. field datatype is text with different widths. So, the results are not showing the way it should because I know there are acct # in one field that match the majority in the other table and yet I am getting that none of them match. Any suggestions? Thanks

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you tell us more about the offending tables and fields please?
    Datatypes, lengths, nullability....
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2004
    Posts
    214
    Both field datatypes are text. One has 13 field size and the other has 16 field size. There are spaces in both which I think is causing the problem.
    Thanks

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Example data?

    E.g. something that does match correctly
    Something that should match but doesn't
    etc
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Make the table that has the field which is 13 in field size to 16 in field size. Spaces don't normally cause a problem but if you do need to get rid of them, highlight the field (column) in the table (looking at the data) and do a simple search/replace where you put a <space> in the search field and leave the replace field blank (or run a query to remove the spaces using the instr function.) Once the space data has been cleaned up and you've made both fields the same size, then try your unmatched query again.
    Last edited by pkstormy; 03-07-08 at 16:07.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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