Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Determining a match in two tables (Access 2000)

    In one table we want to add a field that will determine if the

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

    Re: Determining a match in two tables (Access 2000)

    You cannot (and need not) do this in a table.
    Instead, create a query based on the tblClients and tblRFP and Proposal Log tables.
    Join them on CompanyName and CODE fields.
    Double click the join and select the option to display ALL records from tblClients.
    Add the fields from tblClients you want to see, plus a calculated column:

    rfp: Not IsNull([tblRFP and Proposal].[Code])

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determining a match in two tables (Access 2000)

    Hello, Hans...Thanks for your quick response. I meant to say in my original post that I was trying to make the determination in a query. I tried the expression you sent but it still doesn't work. The query results show all the records in the tblClients with a "0" indicated in the calculated field, when indeed there are probably only 200 matches out of 600 clients. Any other suggestions would be appreciated. Many thanks!...Mary

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

    Re: Determining a match in two tables (Access 2000)

    I don't think we can solve this without seeing the database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determining a match in two tables (Access 2000)

    Hans, for confidentiality purposes, I imported into a new blank database a much stripped down version of the two tables and a query. I would like a calculated field added to the query that will read true or false (or 1 or 0, or yes or no) if the Company Name in tblClients appears at least once in the second table. Appreciate your help!...Mary

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

    Re: Determining a match in two tables (Access 2000)

    It's as I more or less expected (but impossible to determine without seeing the tables, or getting LOTS of information about them): you must join the tables on the CODE field. This field displays the Company Field in the tbl RFP and Proposal Log table, but corresponds to CODE in the other table.

    I have attached the modified database. The calculated column shows 0 for False (no match), and -1 for True (match). You can change the Format property of this column to Yes/No or True/False if you like (you cannot select those from the dropdown list, but you can enter them manually)

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determining a match in two tables (Access 2000)

    YES! Hans--you are right, of course; I copied the calculated field into our database query and changed the relationship and it now reflects the matches correctly! Many, many thanks for your help!...Mary

Posting Permissions

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