How to show last date on a continuous form including blanks?

[riccal90]

Hi everyone,

Thanks in advance for any help you can give me.

I have a table "tbl_CHcontactlist" connecting in a 1-many relationship with another table "tbl_CHchecklists".

I have build a continuous form that I intend to show all records from tbl_CHcontactlist" on. My manager has asked me to also show the most recent date a checklist was completed for each care home, however, it also needs to show blank fields where a checklist has not yet been completed. Does anyone have any suggestions on the best way to go about this?

I have tried building a query using the max function but can't seem to get it to work right and simply adding the date field from the "tbl_CHchecklist" table makes it so I can't see all the records from "tbl_CHcontactlist"

[twinnyfo]

riccal90,

Welcome to Bytes!

You may have to create a query to find the most recent checklist for all care homes and left join it to your table in a new query with the criteria that the Date either equals that max date or is null.

Hope this makes sense and hope it hepps.

[riccal90]

Thanks Twinnyfo, the first part certainly makes sense as I can use a max query to find the latest date for each care home. However you mention left joining onto the table... how would I go about doing that?

[twinnyfo]

First, let's make some assumptions on naming conventions. You will have to change the field names to make this work properly.

tbl_CHcontactlist
CHcontactID
ContactEMail
ContactAddress
etc.

and

tbl_CHchecklists
CHcontactID
ChecklistDate
etc.

To find the most recent date for each Care Home:

SELECT 
    CHcontactID 
    Max(ChecklistDate) AS LastDate 
FROM 
    tbl_CHchecklists 
ORDER BY 
    CHcontactID;

I presume you have probably gotten that far....

But, you want to join that query (which can be a subquery in your main query like this:

SELECT 
    tbl_CHcontactlist.CHcontactID 
    qsubChecklists.LastDate
    etc. 
FROM 
    tbl_CHcontactlist 
LEFT JOIN (
    SELECT 
        CHcontactID 
        Max(ChecklistDate) AS LastDate 
    FROM 
        tbl_CHchecklists 
    ORDER BY 
        CHcontactID) 
    AS qsubChecklists 
ON 
    tbl_CHcontactlist.CHcontactID = qsubChecklists.CHcontactID;

"I think" that this will give you a list of all the Care Home IDs, and the most recent checklist. If there is no checklist date, then it will merely be blank (Null).

It's often difficult to write a query without the tables before me to test it.

Hope this hepps!