Filtering report by multiple date fields (1 Viewer)

[kacey8]

Morning/Afternoon/Evening all.

Need a bit of advice on the best way to approach a report.

I have a report which gives a sum total of check boxes. The boxes are

Letter_1_sent [Yes/No]
Letter_2_sent [Yes/No]
Letter_3_sent [Yes/No]
Letter_4_sent [Yes/No]
Letter_5_sent [Yes/No]

Now this is fine and shows the results. However it is the filtering I am unsure about. Each letter has an associated field with the date the letter was sent

Letter_1_date [Date]
Letter_2_date [Date]
Letter_3_date [Date]
Letter_4_date [Date]
Letter_5_date [Date]

Now I have filtered before easily when running a report by date when using one column for the date but not five different columns.

So for example if people during period 01/01/16 - 30/01/16 15 people got sent letter 1 the total count is 15, if 20 got letter 2 then the count is 20 and so on.

However, a person receiving letter 2 would have received letter 1 (possibly before the date period) if it was before the date period then they need to not be counted.

Hope this makes sense?

 

[kacey8]

Sorry I know I explained it badly.

Okay. So the form to open the report has two textboxes

[txtfrom]
&
[txtto]

Normally it's not a problem as when I have filtered a report before I've used the following text boxes and the on click to load the report to show only data between two dates based on one field in the database, ie [date received]

What I am trying to do now is to count all the letters sent between the dates entered into txtfrom and txtto

I assume this means the SQL query for the report will need to filter the data Just not sure the best way to go about it

 

[kacey8]

Hopefully this makes it easier.


So I want to filter the report between two dates

[txtfrom] & [txtto] to find the letters sent during this period. In this example the period is 06/06/2016 - 12/07/2016

As we can see below I want to count all the letters in the green boxes for each letter seperated by letter number but exclude the ones outside the date.

Now filtering by one column with txtboxes on click to open is easy, but it's the filtering/counting by 5 that I am finding hard.

 

[sneuberg]

Then the result would only have the last six records shown in the image you posted? What records do you want in the result given the situation in your last post?

 

[kacey8]

Then the result would only have the last six records shown in the image you posted? What records do you want in the result given the situation in your last post?

I want to be able to count the letters sent for each letter (1-5) between each date.

So the report has a box that show

Letter 1 = XX
Letter 2 = XX
Letter 3 = XX
Letter 4 = XX
Letter 5 = XX

XX = Letters sent between txtfrom and txtto

That's as simple as I can explain it

 

[sneuberg]

Since you know how to do it for one maybe the easiest thing for you to do would be to just make five reports, one for each letter and put them together in your report as subreports.

 

[kacey8]

Since you know how to do it for one maybe the easiest thing for you to do would be to just make five reports, one for each letter and put them together in your report as subreports.

that is my last resort, If I can learn an easier and less convoluted way to do it in one report I'd much prefer this.

 

[sneuberg]

The less convoluted way would be to normalize your data structure and move these letter dates and yes/no fields to a separate related table. Then you could get these counts with a simple aggregate query. Also if there were any need for a letter 6 it would be easy to add.