6 Replies Latest reply on Jan 14, 2015 5:56 AM by JanetLui

    Using count function with date restrictions

    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

        • 1. Re: Using count function with date restrictions
          philmodjunk

          I would look at the values specified for this part of the relationship:

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

          This is the unique identifier for records in which table?

          Your last screen shot does not show the value of this field so the first thing to check is whether the values of this field in Table1 match to the correct records of Table2.

          • 2. Re: Using count function with date restrictions
            JanetLui

            The PIN is for the records/calls in Report 1-- Table 2 (mislabeled as Table 1 in the picture) are aggregate numbers for the project as a whole (wouldn't have individual PINs listed in that Table). I can remove that relationship from the two if that would solve the problem... but I think I may have tried that previously with no change in what the tables showed.

            • 3. Re: Using count function with date restrictions
              philmodjunk

              That's not the point. The question is do the values in the PIN fields of the two tables match like you expect them to? Your results suggest that the values in this field are incorrect for the table that's on the "many" side of this relationship.

              I'd temporarily remove the date field pairs from this relationship and set up a portal to DatesWindows on a StudySummary layout. This will list all records in DatesWindows that have the same PIN value as the current record in StudySummary. If you do not see the correct list of records in this portal, the PIN values are wrong in DatesWindows.

              If you see the expected values, the next thing to confirm is that your date fields are all defined in Manage | Database | Fields as either date fields or calculation fields that specify a date result type.

              • 4. Re: Using count function with date restrictions
                JanetLui

                Sorry, I don't think I understand what you mean. The PIN values would not be wrong in DatesWindows because the data is entered through the DatesWindows table-- that's the source. I added the DatesWindows portal to the Study Summary to show PINs and no PINs show up-- which is where you are saying that the PIN values are then wrong in DatesWindows? I don't understand how that is possible... ?

                • 5. Re: Using count function with date restrictions
                  philmodjunk

                  Keep in mind that I don't know all the details of your database design.

                  I'm not sure what you mean by

                  because the data is entered through the DatesWindows table

                  Entering the data in one table does not automatically enter identical information into a field of another table--regardless of the relationship.

                  If you modified your relationship so that the PIN fields are the only match fields and a portal fails to show any records, that proves that you do not have any related records where the value in the PIN field matches to the value of the PIN field in the layout's current record.

                  Here's another way to test the same thing. In your DatesWindows based layout, I see a PIN field with a value of 1111. Go to a layout based on the other table, enter find mode and perform a find by specifying 1111 in the PIN field defined in that other layout's table. Do you find any records?

                  Note that problems with PIN fields are not the only possibility that I mentioned.

                   

                  • 6. Re: Using count function with date restrictions
                    JanetLui

                    I don't find records when I do the search, but the way I set up the Study Summary table, I wouldn't have expected to find discrete PIN records (I'm guessing here is where the issue is). For this summary table, I don't have the PIN field because each row is supposed to just show the total numbers for that week (e.g. 10 PINs had completed calls--I don't necessarily care/want it to visibly show which PINs those are).

                    From the attached picture of the Recruit Table, I was able to link it based on dates to my study summary table to perform the counts/sums I needed. I'm guessing the difference is that this recruit table has data entered as numbers whereas I want to perform counts/calculations with my DatesWindows/Calls table. Would it be easier to make an additional layout to pull the data from DateWindows into a format like my Recruit table? Is there a way to do that easily, or would it essentially be what I am trying to accomplish with my first inquiry on this post?

                    Sorry if this all seems confusing... I've been able to figure out most things on this program (I've only been working with it for a month now), but I've been getting stuck with these reports that pull from tables that have calculations, etc.