By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,764 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

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

P: 2
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"
1 Week Ago #1

✓ answered by twinnyfo

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

Expand|Select|Wrap|Line Numbers
  1. tbl_CHcontactlist
  2. CHcontactID
  3. ContactEMail
  4. ContactAddress
  5. etc.
and

Expand|Select|Wrap|Line Numbers
  1. tbl_CHchecklists
  2. CHcontactID
  3. ChecklistDate
  4. etc.
To find the most recent date for each Care Home:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     CHcontactID 
  3.     Max(ChecklistDate) AS LastDate 
  4. FROM 
  5.     tbl_CHchecklists 
  6. ORDER BY 
  7.     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:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     tbl_CHcontactlist.CHcontactID 
  3.     qsubChecklists.LastDate
  4.     etc. 
  5. FROM 
  6.     tbl_CHcontactlist 
  7. LEFT JOIN (
  8.     SELECT 
  9.         CHcontactID 
  10.         Max(ChecklistDate) AS LastDate 
  11.     FROM 
  12.         tbl_CHchecklists 
  13.     ORDER BY 
  14.         CHcontactID) 
  15.     AS qsubChecklists 
  16. ON 
  17.     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!

Share this Question
Share on Google+
3 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,257
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.
1 Week Ago #2

P: 2
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?
1 Week Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,257
First, let's make some assumptions on naming conventions. You will have to change the field names to make this work properly.

Expand|Select|Wrap|Line Numbers
  1. tbl_CHcontactlist
  2. CHcontactID
  3. ContactEMail
  4. ContactAddress
  5. etc.
and

Expand|Select|Wrap|Line Numbers
  1. tbl_CHchecklists
  2. CHcontactID
  3. ChecklistDate
  4. etc.
To find the most recent date for each Care Home:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     CHcontactID 
  3.     Max(ChecklistDate) AS LastDate 
  4. FROM 
  5.     tbl_CHchecklists 
  6. ORDER BY 
  7.     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:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     tbl_CHcontactlist.CHcontactID 
  3.     qsubChecklists.LastDate
  4.     etc. 
  5. FROM 
  6.     tbl_CHcontactlist 
  7. LEFT JOIN (
  8.     SELECT 
  9.         CHcontactID 
  10.         Max(ChecklistDate) AS LastDate 
  11.     FROM 
  12.         tbl_CHchecklists 
  13.     ORDER BY 
  14.         CHcontactID) 
  15.     AS qsubChecklists 
  16. ON 
  17.     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!
1 Week Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.