Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Unanswered: Query with Subform

    I just added a new table and subform to my dB. They are linked by "Contract_ID". I created a query that pulled the "Contract Name" from the master table, and Audit info ("Auditor";"Trend";"Action", etc.) from the new form.

    I am trying to find a way to make the query pull all records from the Master listing whether they have been audited in the subForm or not.

    Any ideas?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    I just looked up "subform" in the online docs and it came back with nothing. Please explain to me what a subform is. Thanks!

    I suspect you want an outer join between the two tables.


    [edit]
    how the heck did I get in the MS Access forum?
    I do Oracle not Access, sorry for the bad information if I gave any.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Nov 2003
    Posts
    6
    I have a form where we do all the data entry. I have a new form for the Audit and I embedded it into the main, data entry form.

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: Query with Subform

    Originally posted by bennettR
    I just added a new table and subform to my dB. They are linked by "Contract_ID". I created a query that pulled the "Contract Name" from the master table, and Audit info ("Auditor";"Trend";"Action", etc.) from the new form.

    I am trying to find a way to make the query pull all records from the Master listing whether they have been audited in the subForm or not.

    Any ideas?
    Try either using the Audit table directly or use the ContractID from the Audit table and not the master when you create your query.

    Gregg

  5. #5
    Join Date
    Nov 2003
    Posts
    6
    The result I keep getting is where I have 774 possible contracts (records), but only 74 have been audited, so My query only gives me 74 files. Is this because of the One-to-Many relationship?

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445
    Originally posted by bennettR
    The result I keep getting is where I have 774 possible contracts (records), but only 74 have been audited, so My query only gives me 74 files. Is this because of the One-to-Many relationship?

    The way it should work:

    Forgive me if I get the table names wrong.

    I'm assuming that you have a one to many relationship between the Contracts table and the Audits table with the one being on the Contracts side. This can be a standard inner join.

    Set the main form up with a recordsource of the Contracts table.

    Set the subform up with the recordsource of the Audits table.

    Set the link fields the way that you have them.

    This should allow you to browse all of the 774 contracts whether or not they have had audits. If they have audits, then the records will show in your subform if not the subform will be blank.

    Gregg

Posting Permissions

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