Displaying Counts from Multiple fields 1

[buecker]

I hate to ask for help but I'm running out of time to research this.

I have a report that needs to show records that were entered for the past 14 days grouped by individual. That's the easy part. The sticking part for me is counting each date entered that is within the past 14 days.

There are 4 columns that I need to look at. I need to count each column that is within the past 14 days for each individual.

This is what I have been playing with just to get a grand total of all touchpoints for everyone. I need to fix this and then add something similar to also display a total next to each person.

=DCount("[ID]", "Outreach Records", "[Initialcontactdate]&[followupdate1]&[followupdate2]&[followupdate3] between date() and date()-14")

ID would be the key column for the "outreach records" table

Thanks in advance.

Ben

 

[dhookom]

If your table was normalized, this would be much easier. You shouldn't have to calculated across fields. A normalized table structure would allow you to count records.

My suggestion is to create a union query like:

[blue]quniContactDates[/blue]

SELECT ID, InitialContactDate as TheDate, "Init Contact" as DateType
FROM [Outreach Records]
UNION ALL
SELECT ID, FollowupDate, "Followup 1"
FROM [Outreach Records]
UNION ALL
SELECT ID, FollowupDate2, "Followup 2"
FROM [Outreach Records]
UNION ALL
SELECT ID, FollowupDate3, "Followup 3"
FROM [Outreach Records];

Your query to count contact dates is now:

SELECT Count(ID) As NumOf
FROM quniContactDates
WHERE TheDate Between Date()-14 And Date();

Duane
Hook'D on Access
MS Access MVP

 

[buecker]

Wow. Thanks for the info. I will try it out tomorrow.