Query multiple records between "Weeks" where no record in child table (1 Viewer)

[rfaircloth]

I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:

clientID fName lName admissionDate dischargeDate
1 John Doe 05/06/2014 06/27/2014
2 Jane Doe 04/24/2014 05/15/2014
3 Steven Smith 05/15/2014 NULL/Empty
4 Chris Davis 06/12/2014 NULL/Empty

Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:

noteID completed dateCompleted clientID
1 yes 05/08/2014 1
2 yes 05/14/2014 1
3 yes 04/25/2014 2

I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:

fName lName week completed date clientID(hidden)
John Doe 5/19/14-5/25/14 Checkbox Null 1
John Doe 5/26/14-6/1/14 Checkbox Null 1
John Doe 6/2/14-6/8/14 Checkbox Null 1
John Doe 6/9/14-6/15/14 Checkbox Null 1
John Doe 6/16/14-6/22/14 Checkbox Null 1
John Doe 6/23/14-6/29/14 Checkbox Null 1
Jane Doe 4/28/14-5/4/14 Checkbox Null 2
and so on.......


I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.

 

[vbaInet]

I'm not sure I'm following. What is your question? Could you please elaborate?

Show us your desired output based on the two tables and tell us what criteria it should qualify.

 

[rfaircloth]

The third example is what I am looking for as the output.

fName lName week completed date clientID(hidden)
John Doe 5/19/14-5/25/14 Checkbox Null 1
John Doe 5/26/14-6/1/14 Checkbox Null 1
John Doe 6/2/14-6/8/14 Checkbox Null 1
John Doe 6/9/14-6/15/14 Checkbox Null 1
John Doe 6/16/14-6/22/14 Checkbox Null 1
John Doe 6/23/14-6/29/14 Checkbox Null 1
Jane Doe 4/28/14-5/4/14 Checkbox Null 2
and so on.......

Basically, the "week" runs Mon - Sun. Each client is required to have a weekly progress note completed sometime during that week. I need to first determine the dates of the very first week they are there by getting the Weekday() value for the date they were admitted and comparing it to Monday. Then once I calculate Monday's date that first week, I calculate the date for the following Sunday to get the first week date span. Then that process of getting the dates should continue all the way to the current date or to the date of discharge. At that point, I need to compare the Weeks with the progress notes table to determine which clients DO NOT have an entry in the progress notes during specified weeks when they were at the treatment facility. If they do not have an entry in the weekly progress notes table, I need it to be displayed out like it is above with the clients name, the date span for the week in question, a checkbox to mark it has been completed, and a textbox to put in the date that it was completed.

It was suggested to me that I create a temporary table to hold the dates of the weeks and the client info to then compare to the notes table. I have been trying to accomplish this and I think that I have created the code to successfully create the temporary table, I'm just not sure how to get the temporary table values to correspond with my form fields as when I go to Control Source for each field the table fields for the temporary table are not there since they are not actually present in the database at that point in time. Am I on the right track though? If so, could someone recommend a solution for getting the temporary table to communicate with my form? I'll attach my source code

 

[vbaInet]

Basically you need to create the missing records in your WeeklyProgressNotes table. So don't worry about the temp table, you need to write a function that will insert into the table weekly records for each client until the DischargeDate OR one or two years from the AdmissionDate (where the client hasn't been discharged).

The second task will be to delete anomalies. That is, for those patients that are discharged but didn't have a discharge date when the weekly records were created, delete the ones that are beyond the discharge date.

Am I making sense?

 

[rfaircloth]

Yes, I had inserted records automatically in other tables for this project to make the queries easier to deal with. I should have done the same here. Thank you for the help, I think that should probably work!

 

[vbaInet]

No problem. Let us know if you need further assistance.