Results 1 to 3 of 3
  1. #1
    trident is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    141

    Showing only the Last Data entry for each Main Item

    I have created 2 tables 1st table (Main) 2nd table (child)

    1st table made up of
    1 - ID - Unique Auto number
    2 - Tender Reference No
    3 - Subject
    4 - Staff

    2nd Table


    1 - ID - Unique Auto Number
    2 - MainID - connects to (Main) autoID, can be duplicated
    3 - Date - entry date
    4 - Remarks

    When I create a report i will get the following:

    Main (ID) / Main (Tender Reference No) / Main (Subject) / Main (Staff) / Child (Date) / Child (Remarks)

    This portion i can create in query, however, the next step is what i am try to get.

    I need to get only the LAST ENTRY DATE AND REMARKS for each Main (ID)

    Can you please help and explain this to me.

    Thank you for your help.

    Trident

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to use a couple of queries.

    qry1 - a group by query

    SELECT MainID, max(Date) as latest FROM child

    qry2

    SELECT child.*
    FROM child INNER JOIN qry1 ON child.mainID=qry1.mainID and child.Date=qry1.latest

    qry3

    SELECT *
    FROM main INNER JOIN qry2 ON main.mainID=qry2.mainID


    Note that Date is a reserved word and should not be used as a field name

  3. #3
    trident is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    141
    Dear Mr. Ajax,

    thank you for the solution, it works fine.

    Trident



    Quote Originally Posted by Ajax View Post
    you need to use a couple of queries.

    qry1 - a group by query

    SELECT MainID, max(Date) as latest FROM child

    qry2

    SELECT child.*
    FROM child INNER JOIN qry1 ON child.mainID=qry1.mainID and child.Date=qry1.latest

    qry3

    SELECT *
    FROM main INNER JOIN qry2 ON main.mainID=qry2.mainID


    Note that Date is a reserved word and should not be used as a field name

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

Similar Threads

  1. Importing Excel & Data Entry into Main Table
    By PSSMargaret in forum Import/Export Data
    Replies: 4
    Last Post: 05-04-2017, 04:02 PM
  2. Replies: 3
    Last Post: 12-29-2014, 01:04 PM
  3. Replies: 5
    Last Post: 03-09-2014, 07:16 PM
  4. Replies: 8
    Last Post: 05-30-2013, 05:06 PM
  5. Replies: 23
    Last Post: 02-09-2011, 10:56 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