Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2016
    Posts
    14
    Provided Answers: 1

    Unanswered: Fetch newest record

    Hello,

    If I have multiple records for the same client in a table, how do I tell a query that I only want to see the most recent record?

    Thanks,

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    How do you know which is tge most recent row?

    Use the select top syntax
    Set a a sort order

    Or use select max
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2016
    Posts
    14
    Provided Answers: 1
    We enter an "inspection date" in the row. The problem I am facing is that if we do 2 or more inspections my query qives me duplicate records when I really only want the most recent inspeciton record to trigger a result. I will see if select max will work.

    Thanks

  4. #4
    Join Date
    Jan 2016
    Posts
    14
    Provided Answers: 1
    Here is my query:


    SELECT q_orderlist_1.Intl_letter_MailingID, q_orderlist_1.key_apn, q_orderlist_1.County, q_orderlist_1.Coalition, q_orderlist_1.APN, q_orderlist_1.Acres, q_orderlist_1.Initial_letter_Owner1, q_orderlist_1.Initial_letter_Owner2, q_orderlist_1.Initial_letter_Address, q_orderlist_1.Initial_letter_City, q_orderlist_1.Initial_letter_ST, q_orderlist_1.Initial_letter_Zip, Staff_notes.Staff_notes, Staff_notes.Staff_notes_Date, Staff_notes.Additional_Notes, t_13260_Orders.Issued_Date, t_Coalition_Notes.CG_Notes, t_Inspections.Results, t_Inspections.Date_Inspected, t_Inspections.Inspection_Type, t_part_list.Cg_status, t_part_list.List_Year, t_Inspections.File_Location
    FROM 13260_Orders_Holding_Table RIGHT JOIN (t_part_list RIGHT JOIN (t_Inspections RIGHT JOIN (t_Coalition_Notes RIGHT JOIN (t_13260_Orders RIGHT JOIN (Staff_notes RIGHT JOIN q_orderlist_1 ON (Staff_notes.MailingID = q_orderlist_1.Intl_letter_MailingID) AND (Staff_notes.key_apn = q_orderlist_1.key_apn)) ON (t_13260_Orders.MailingID = q_orderlist_1.Intl_letter_MailingID) AND (t_13260_Orders.key_apn = q_orderlist_1.key_apn)) ON (t_Coalition_Notes.MailingID = q_orderlist_1.Intl_letter_MailingID) AND (t_Coalition_Notes.key_apn = q_orderlist_1.key_apn)) ON t_Inspections.key_apn = q_orderlist_1.key_apn) ON t_part_list.key_apn = q_orderlist_1.key_apn) ON ([13260_Orders_Holding_Table].MailingID = q_orderlist_1.Intl_letter_MailingID) AND ([13260_Orders_Holding_Table].key_apn = q_orderlist_1.key_apn)
    WHERE (((Staff_notes.Staff_notes_Date) Is Null)
    AND ((t_13260_Orders.Issued_Date) Is Null) AND ((t_Coalition_Notes.CG_Notes) Is Null) AND ((t_part_list.Cg_status) Is Null)
    AND ((IIf(IsNull([Forms]![13260 Order Maintenance]![cbo_cg_list]),True,[q_orderlist_1.Coalition] Like "*" & [Forms]![13260 Order Maintenance]![cbo_cg_list] & "*"))<>False)
    AND ((IIf(IsNull([Forms]![13260 Order Maintenance]![txt_County]),True,[q_orderlist_1.County] Like "*" & [Forms]![13260 Order Maintenance]![txt_County] & "*"))<>False)
    AND (([13260_Orders_Holding_Table].Holding_Staff) Is Null));

    How do i incorporate "Select Max" ? Sorry I am still learning.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I expect you to be better off using select top syntax

    Presumabky on the tibspections table

    You may need to yse a sub select
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2016
    Posts
    14
    Provided Answers: 1
    I was looking into using a select top or a select max as a subselect and I think that is going to limit my entire data results right? I want the query to return all the results, but if there are 2 rows with the same parcel number (each row having a different inspection date) I want the most recent inspection date to be included in the results.

    Thanks for the help!!!!!!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So i vestigate using a sub select
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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