AnsweredAssumed Answered

Using count function with date restrictions

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


Using count function with date restrictions


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?).