Report/Return Yes if Customer has services in specific month

[claysea]

I have a table called "Services" where clients monthly services are listed. Clients will sometimes have 4-5 services in one month. I created a report and I want it to return a "Yes" in a field if a particular client had any service (whether it was 1 or 5) during a certain month. I'm stuck. Also, dont' know VBA...so that's not an option. Thanks!

[JoeM]

You shouldn't need VBA. You should be able to do it through queries, using Aggregate Queries.

What is the structure of your Table?
Maybe you could post a small data sample?

[claysea]

You shouldn't need VBA. You should be able to do it through queries, using Aggregate Queries.

What is the structure of your Table?
Maybe you could post a small data sample?

Local ID:	Date of Service:	Type of Service:	Service Provider:	Time:	Total Charges:	Amount Billed:	Amount Paid:	Date Paid:	30 days	60 days	90 days	Date:	Due Date:	Invoice Date:	Insurance Filed:	Insurance Outcome:	Bad Debt Write-Off	Bad Debt Write-Off Date
10683	8/14/2012	Speech Therapy	Elizabeth	100	$210.00										Yes	Paid	No
10683	8/14/2012	SST	Connie	100	$126.52												No
10683	8/10/2012	Speech Therapy	Elizabeth	100	$210.00												No
10683	8/7/2012	SST	Connie	100	$126.52												No
10683	7/20/2012	Speech Therapy	Elizabeth	100	$210.00										Yes	Paid	No
10683	7/17/2012	SST	Connie	100	$126.52												No

[JoeM]

One easy way would be to create a query and add "Local ID" and "Date of Service" to it.
Uncheck the "Show" box under the "Date of Service" field, you just want this for your criteria. Placed your date criteria under this field, i.e.

BETWEEN #8/1/2012# AND #8/31/2012#

or whatever your date criteria is.

Then click on the Totals (Aggregate) button in the Query Builder (looks like a Sigma). This will add a "Totals" row in the query builder with the phrase "Group By" under your two fields.
Under the "Date of Service" field, change the value of that Totals row to "WHERE".

Now, if you view your results, it will just return the Local IDs that have had at least one service during that month. If you want to include a count of the number of services, just go back to the Query Builder, and add any field to the query, and change the Totals row value to "COUNT".

You can then join this to your Local IDs table (assuming that you have one), performing a Left Join from your Local IDs table to your Service Count query.
Then you can add a calculated field that will return a "Yes" if the Count for that ID is greater than zero, otherwise return a "No", i.e.

ServiceCheck: IIf([Service_Counts_Query]![CountOfDate of Service]>0,"Yes","No")

[claysea]

One easy way would be to create a query and add "Local ID" and "Date of Service" to it.
Uncheck the "Show" box under the "Date of Service" field, you just want this for your criteria. Placed your date criteria under this field, i.e.

BETWEEN #8/1/2012# AND #8/31/2012#

or whatever your date criteria is.

Then click on the Totals (Aggregate) button in the Query Builder (looks like a Sigma). This will add a "Totals" row in the query builder with the phrase "Group By" under your two fields.
Under the "Date of Service" field, change the value of that Totals row to "WHERE".

Now, if you view your results, it will just return the Local IDs that have had at least one service during that month. If you want to include a count of the number of services, just go back to the Query Builder, and add any field to the query, and change the Totals row value to "COUNT".

You can then join this to your Local IDs table (assuming that you have one), performing a Left Join from your Local IDs table to your Service Count query.
Then you can add a calculated field that will return a "Yes" if the Count for that ID is greater than zero, otherwise return a "No", i.e.

ServiceCheck: IIf([Service_Counts_Query]![CountOfDate of Service]>0,"Yes","No")

I am sooooo sorry JoeM, but I don't understand a word you said. Do you have a more dummy proof idea for me??? This is the latest formula I tried and it returned "yes" and "no", but it wasn't accurate.

=IIf(Month([Date of Service:])=Month(Date()-3),"Yes","No")

[JoeM]

I would recommend reading up on Aggregate (Totals) Queries. There is information on it in Access's built-in help files, as well as information and on-line tutorials that can be found on-line. If you are going to be doing this sort of stuff in Access, you are really going to need to have a good understanding of these types of queries. If you do that, what I said should make more sense.

IIf(Month([Date of Service:])=Month(Date()-3),"Yes","No")

What are you trying to do with the "-3"? That will subtract three days, not three months, as you are substracting it from Date() before taking the month. If you are trying to get the month from three months ago, you would need to change it like this:

IIf(Month([Date of Service:])=Month(Date())-3,"Yes","No")