Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2015
    Provided Answers: 1

    Unanswered: One Search box, two tables

    Hi Everyone,
    Thank you for your time.

    What is the best way to create a form with a search box that looks across more than one table. I need to search for a prospect by either phone number, last name or street or all. The Results should display Last name, Street, Town, Status, status Date, Table Location or the table the record is located in. I am guessing I will need to create a Form with search box and a sub form in datasheet view to list the result?

    Many thanks indeed,

  2. #2
    Join Date
    Apr 2014
    Provided Answers: 34
    Yes, the form would have the box...txtFind.
    Build a UNION query. It is 2 queries,

    Select * from tbl1 where [fld]=forms!frmFind!txtFind
    Select * from tbl2 where [fld]=forms!frmFind!txtFind

    NOTE: both queries MUST have exact field layout order.

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    My strong advise is not to use a datasheet to display data unless it is just a passive list of 'stuff'
    Looking for the same 'stuff' accross more than one table may suggest your db design is not normalised. Not that it isnt normalised but it may not be. Ferinstance if the addresses are delivery + invoice address then thats fine, but if its the same delivery address in more than ome table then not so fine
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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