Results 1 to 7 of 7
  1. #1
    Amyfb is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    64

    How to combine queries from multiple tables into one or two forms?

    Please point me in the right direction on forms design.

    project title: Document Version Control
    purpose: manages the four-step development cycle of a document , with multiple comments per cycle step.

    I created a bunch of queries based on two tables and each has a form created from it.

    BUT that is as far as I am able to go. I cannot get two queries together on one form and wonder what I don't know about how to accomplish that.

    Ideally, I have no more than three forms total for user attention. If all could be on one form, that would be pretty awesome.

    the sequence of user operations is:
    create a document (if not already existing)


    link the document to a)customer - if applicable; b) decision; c) version; d) versioncycle; e)version location; e) department; f) comments

    Attached is the zipped copy of the db, with tables, queries, forms and relationship diagram.

    Thank you in advance for pointers, how to, corrections, suggestions.

    kind regards
    AmyfbDocumentControl_PB.zip

  2. #2
    JoeM is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    3,904
    To have more than one Query/Table on a Form, you can create and use Sub Forms.
    Have a look here for details: https://support.microsoft.com/en-us/...a-1e74d6f5f06b
    You can find lots of other articles and videos on it using a Google or YouTube search.

  3. #3
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Any

    You currently have no relationship defined between the Document and Versions?

    I suggest that you should change your relationships so that there is an Autonumber field in all tables.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  4. #4
    Amyfb is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    64
    Quote Originally Posted by mike60smart View Post
    Hi Any

    You currently have no relationship defined between the Document and Versions?

    I suggest that you should change your relationships so that there is an Autonumber field in all tables.
    Hi Mike,
    i have a link table for Documents and Versions. In the base tables, there is an autonumber id in every case. In the link table is formatted just as number, using the table primary key from each as a composite key for the link table.

    In the attachment here, now the live relationship diagram is updated to match the relationship report - to show the relationship is established.DocumentControl_PB-r1.zip

    when i try to put two of the queries together into a new query, i get no records returned, never. sometimes i try to put a table with a query, but that gets no results either. This sounds like a gap in my skills in writing a complex query.

    hope this helps you to help me!

    thanks
    Amy

  5. #5
    Amyfb is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    64
    Quote Originally Posted by JoeM View Post
    To have more than one Query/Table on a Form, you can create and use Sub Forms.
    Have a look here for details: https://support.microsoft.com/en-us/...a-1e74d6f5f06b
    You can find lots of other articles and videos on it using a Google or YouTube search.
    Hi Joe,
    my issue comes up when I want to create a new query based on two or more existing queries. Each existing query is based on two tables (because I can't ever get three tables to play nice in a query).

    I'll check out the link you provided - thanks for sending it. Maybe i'll find some clues there.

    thanks!

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I agree with @mike60smart, so I modified your table relationships. See DocumentControl_PB_r1_ss.accdb

    Then I started modifying your forms... didn't get far because I don't understand your process. You do have a fair amount of link/junction tables, more than I would think necessary.



    In "DocumentControl_PB_r1_ mod_ss.accdb" I did make a couple of forms: see "frmDocuments" and I further modified your tables and relationships.



    Your naming conventions is pretty good.
    I use suffixes "_PK" and "_FK" to have the Pr and FK fields stand out.
    And only one reserved word used: "tblVersionCycle.Path".
    Soooo, "Path" of what??? Add another word to Path...."VersionPath"??



    My $0.05 worth........
    Attached Files Attached Files

  7. #7
    Amyfb is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    64
    Quote Originally Posted by ssanfu View Post
    I agree with @mike60smart, so I modified your table relationships. See DocumentControl_PB_r1_ss.accdb

    Then I started modifying your forms... didn't get far because I don't understand your process. You do have a fair amount of link/junction tables, more than I would think necessary.



    In "DocumentControl_PB_r1_ mod_ss.accdb" I did make a couple of forms: see "frmDocuments" and I further modified your tables and relationships.



    Your naming conventions is pretty good.
    I use suffixes "_PK" and "_FK" to have the Pr and FK fields stand out.
    And only one reserved word used: "tblVersionCycle.Path".
    Soooo, "Path" of what??? Add another word to Path...."VersionPath"??



    My $0.05 worth........
    Hi Steve, thank you for you input! I look forward to seeing what you came up with. Good catch on that 'bad name'; I'll fix that right off. Had a nice chat with Mike a few minutes ago.

    I can't say enough good about how generous you all are with your time and efforts to support us up and coming newbie.

    Have an awesome day!

    Cheerios
    amyfb

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

Similar Threads

  1. Replies: 3
    Last Post: 09-04-2013, 03:21 PM
  2. Replies: 4
    Last Post: 03-04-2013, 05:00 PM
  3. Query to combine multiple queries
    By jortizz in forum Queries
    Replies: 4
    Last Post: 05-03-2011, 09:02 PM
  4. Replies: 1
    Last Post: 05-28-2009, 05:08 AM
  5. Combine queries results in forms
    By frasilvio in forum Queries
    Replies: 12
    Last Post: 01-10-2008, 01:34 AM

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