Results 1 to 3 of 3
  1. #1
    MichaelSB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    12

    Query between 2 tables

    I am trying to create a query between two tables. My two tables look like the below examples.


    Click image for larger version. 

Name:	table1.png 
Views:	17 
Size:	20.3 KB 
ID:	42865Click image for larger version. 

Name:	table2.png 
Views:	16 
Size:	7.5 KB 
ID:	42866

    What I would like to do is create a query that will display all of the information in the staff list table along with the most recent date associated to that staff member. So if the staff member has two date entries I only want to display the most recent date. I do however want every date they ever receive to be listed in the dates table though as it is essentially an archive table. I have this part figured out but the part I am not sure about is the following.

    I would like every staff member to show up in the list regardless if they have a date associated to them or not. And if they don't the field for them in the Augment date field should just be null. The reason for this is because I am then going to create a report based off this query that displays this information sorted first by the staff members EOD(seniority date) then next by the last date they were augmented(the most recent date in the dates table). To create this report all staff members need to be listed at all times regardless if they have had a date entry already or not. How would I go about do this? As of right now everything displays correctly but the only staff members that are displayed are people who currently have a date entered in the dates table. Thanks for everyone's time and help.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Build an aggregate query that returns the maximum date.

    SELECT StaffID, Max(Augment_Date) AS MaxDate FROM Augments GROUP BY StaffID;

    Now build another query joining that query to Staff table. Use LEFT or RIGHT join (I forget which work) to return all records from Staff.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    MichaelSB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    12
    edited post as I figured out error on my part. Thanks for the help I just have to look into the joining queries now. You have been great June7. Much appreciated.

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

Similar Threads

  1. Replies: 17
    Last Post: 05-10-2020, 09:49 AM
  2. Replies: 4
    Last Post: 08-24-2016, 06:48 AM
  3. Replies: 2
    Last Post: 02-05-2014, 09:27 AM
  4. Replies: 3
    Last Post: 01-20-2014, 09:04 AM
  5. Replies: 3
    Last Post: 01-05-2012, 12:04 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