Results 1 to 11 of 11
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    How to use criteria for one table in two different fields for another

    Bad title. So I have two tables with phone number info and I'm trying to match them via a query. One table just has a phone number field [table1.phone] and the other table has a home [table2.homephone] & mobile phone number [table2.mobilephone] field. Basically I'm using the query to filter down to customers who are in both tables by matching the phone numbers.



    Is there a way to match table 1's phone with either table 2 home or mobile. Just I dropped table 1 in the criteria for both, and basically it only matches instances where the same number is in both fields in table 2. Any ideas?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    use a form.
    the form has the criteria,
    any # of queries can then use it as criteria: where [field]=forms!fMyForm!txtPHone

  3. #3
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    It would be easier if you only had one field in table2 for phone numbers and then add a second field for the type of number. Then you could join your tables on the phone number fields. You could add the type of phone field. So I believe that your design is perhaps flawed.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're saying that table1 field has both cell and home numbers in it but table2 has them in separate fields? UNION query comes to mind. First return a match where tbl1.field1 matches tbl2.field1 and query 2 matches tbl1.field1 on table2.field2
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Or you can create a query to concatenate the two phones Phones:[Homephone] & " " & [mobilephone] then use Instr on that: Instr([Phones],[phone])>0 would give you the records where at least one of them matches.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    You're saying that table1 field has both cell and home numbers in it but table2 has them in separate fields? UNION query comes to mind. First return a match where tbl1.field1 matches tbl2.field1 and query 2 matches tbl1.field1 on table2.field2
    So table1 only has one phone number field and its the customer's home phone. Whereas, table two has two fields: home phone and mobile phone. Basically I'm trying to construct a query that pulls customers listed in both tables by matching the phone numbers. However, I need to table 1's phone number to match either of table 2's numbers, not both. If that makes sense

  7. #7
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Or you can create a query to concatenate the two phones Phones:[Homephone] & " " & [mobilephone] then use Instr on that: Instr([Phones],[phone])>0 would give you the records where at least one of them matches.
    Hi Vlad, So I concatenated both fields and it worked great. But where do I put the Instr function? In the criteria of that concatenated field?

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    In a new field you add the Instr expression and on its criteria row you add >0.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So table1 only has one phone number field and its the customer's home phone. Whereas, table two has two fields: home phone and mobile phone.
    I'll leave it to Vlad I guess, as he seems to know why this isn't just an equal join between the two home phone fields.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    So I was able to input that and it's identifying matches as "-1" and non-matches as "0".

    Is this correct? And how would you suggest having it auto filter down to "-1" matches?

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Put -1 or (True) in the criteria row of the query. Of course would also work with the union query approach recommended by Micron
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-17-2017, 06:32 AM
  2. Replies: 4
    Last Post: 11-12-2015, 01:15 PM
  3. Replies: 7
    Last Post: 08-31-2015, 12:53 PM
  4. Replies: 1
    Last Post: 02-19-2014, 05:49 PM
  5. Replies: 1
    Last Post: 01-30-2013, 03:27 PM

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