Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012

    Unanswered: Create query by form utilizing two tables and query

    Hello! I am fairly new to Access but have done database's in the past. I am trying to develop a database that has a query by form with two tables, customers and purchase orders.

    What I am running across is how to actually do this:

    I want to pull data from the customer table and purchase order table where the records indicate that a customer has created a purchase order. I want, however, to join these two tables in such a way that when I search by form if I just enter something in the form such as a customers name or address or both - as well as say purchase order information like - PO# or Shipping Method - it will query the results and define for me in another form what those results are.

    In the customer table I might have:

    Customer Name:

    Purchase Order Table

    PO# 5500123456 made by Joe and Shipping Method is 'Deliver'

    Then Joe has created another PO where the number is 5100123789 and the shipping method might be 'COD'.

    If I had my form search by Joe's name it would bring up both results.
    If I had my form search by Joe's name and just one of the PO#'s it would just bring up that record. If I had it do just by 'Deliver' it would bring up just that record. I hope this is clear enough - I know it is probably a 'Join' issue. My relationships are 'One-To-Many' on all my tables and are in at least first normal form for the data.

    Thank you and any help is appreciated!

  2. #2
    Join Date
    Apr 2004
    outside the rim
    Sounds like you only need one query: join customer and POs. Setup that query so that you have all the records.

    Then use the filtering at form level (which the wizards will setup for you as you build the form).

    That gets you in the door, and then if you need to tweak it, you can modify the wizard code.
    have fun!

    Todd S.
    click to email

    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET

    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

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