Results 1 to 4 of 4
  1. #1
    Cecil861 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Dec 2018
    Posts
    11

    Help with Idea

    I need some help with a idea,

    I have a database of training dates for each associate. We have a group of 10 processes we call our base processes, I am trying to get a report of something that shows what each associate has left to get trained in those 10 base processes.



    Any ideas would be greatly appreciated.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I see three tables in this design:

    1. An Associate table that lists all associates

    2. A Process table lists your 10 processes as 10 separate records.

    3. A Process Completion table that lists a separate record for each associate and each process that they have completed. So if Jack has completed 6 processes, he should have 6 records in this table.

    Now, create a query between your first two tables with no join. They will create a Cartesian product of 10 records for every associate.

    Then, do a Left Outer Join from this query to the third table, joining on BOTH the Associate and Process fields.
    Add all the fields from the first query, and the Associate field from the Process Completion table.
    Change it to not show/display the Associate field from Process Completion table, and put the following Criteria under that field: Is Null
    (Basically, we are creating an unmatched query between these two objects).
    This will return all the processes that associates have left to complete.

  3. #3
    Cecil861 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Dec 2018
    Posts
    11

    Thank you

    Thank you for your help that worked great I was missing one of the tables you suggested.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome.
    Glad it worked out for you!

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

Similar Threads

  1. No idea where to begin
    By Kkennedy in forum Database Design
    Replies: 2
    Last Post: 09-18-2017, 07:11 AM
  2. Replies: 7
    Last Post: 01-30-2013, 09:47 AM
  3. Replies: 1
    Last Post: 08-03-2012, 12:32 PM
  4. Can I use Access for this Idea?
    By sandiego5 in forum Access
    Replies: 5
    Last Post: 11-17-2011, 09:14 AM
  5. Need help with 'note book' idea.
    By Luk3y in forum Access
    Replies: 0
    Last Post: 06-26-2009, 09:54 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