AnsweredAssumed Answered

Using count function with date restrictions

Question asked by JanetLui on Jan 13, 2015
Latest reply on Jan 14, 2015 by JanetLui

Title

Using count function with date restrictions

Post

I am attempting to use the Summary Count function, but I would like to restrict the dates. Here is the setup (I've attached images of what I described below)--

Report 1 that pulls data from Table named DatesWindows: contains records of when phone calls need to be made- when the call is completed, there is a checkbox field (named B1Complete) that should be checked off and a date field (named B1Date) that should be filled in for the date the call is completed. I have a summary count of field (named tB1Completed) that counts the checked B1Complete checkboxes.

Table 2 (named Study Summary) is used for summarizing the entirety of the project, both weekly and a grand sum: has a week beginning field (named WeekStart) and a week ending field (named WeekEnd).

I would like to be able to pull the summary count of completed calls (tB1Completed) based on the dates I input for WeekStart and WeekEnd on Table 2. I set up relationships between the two tables as follows:

1) B1Date > WeekStart

2) B1Date < WeekEnd

3) PIN = PIN (this is the unique identifier for each record)

Then, I placed the tB1Complete (the summary count of field) from DatesWindows into my Table 2/Study Summary layout, but it doesn't seem to be working. I would GREATLY appreciate any ideas/tips/suggestions as I have tried several different things that haven't not produced the desired results. I was able to make this work with a different table (perhaps because that table just had numbers, instead of summary/count fields?).

FM_Upload.PNG

Outcomes