Getting desired results in a report

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

Getting desired results in a report

Post by petern »

I have attached an Excel spreadsheet with some sample data. From this data, I need to generate a report by worker that gives the desired information, namely the client's status vis a vis the worker in any given reporting period. Status is a calculated value based on what month meetings are held in. In the first month a client comes to the agency, they are NEW on the first visit, thereafter in the first month, NEW/RET. At any subsequent point, they are RET (returning). Right now I am using an IIF statement to return NEW or the empty string to Report Status.

Under the following circumstances, the dataset in the spreadsheet should return the following results:
Worker Kim: Month of April alone, client Peter is New, Report Status NEW
Worker Pat: Month of April alone, client Peter is New/Ret, Report Status nothing (Empty string)
Worker Kim: Month of May alone, client Peter is Ret, Report Status nothing
Worker Kim: Months of April & May combined, client Peter is New, New/Ret & Ret therefore Report Status NEW
Worker Pat: Months of April & May combined, client Peter is New/Ret & Ret therefore Report Status is nothing

Departmental report April or April & May client Peter is New.

In essence, New trumps the other two statuses in any given reporting period. If the client was new in that period then their status is New. However, if we are printing the worker's individual reports, the client MUST be New with that specific worker and a client can only have a first visit with one worker.

The problem I am running into is my totals query is giving me three lines for April and May combined for worker Kim since all three of the possible report statuses happen. I need to find a way to look for whether New is a status and have it trump the others and then count the number of visits in that period. Is there a way to rewrite my IIf statement to include worker values into the equation.

Right now, it looks like this:
NewNot: IIf([MinOfISAPMeetingDate] Between [Forms]![frmISAPDates]![txtStartDate] And [Forms]![frmISAPDates]![txtenddate],"NEW","")

MinOfISAPMeetingDate is the date of the first meeting. The [Forms], etc stuff is a parameter fed from a date-picking form.

Hopefully this makes some sense. :hairout:
You do not have the required permissions to view the files attached to this post.
Peter N

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Getting desired results in a report

Post by HansV »

You shouldn't include Status in the record source of the report, only Report Status. That way you should get only one record per worker/client combination.
Best wishes,
Hans

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

Re: Getting desired results in a report

Post by petern »

I'm not including status in the record source of the report. The problem I'm having right now is that I get the desired result for Kim in my spreadsheet, but because of the MinOfISAPMeetingDate I am getting NEW instead of an empty string for Pat in the month of April since it is true that the client was new in April, just not for Pat.
Peter N

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Getting desired results in a report

Post by HansV »

Could you post a stripped down and zipped copy of the database? Make sure to remove or alter sensitive information.
Best wishes,
Hans

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

Re: Getting desired results in a report

Post by petern »

I have a couple of ideas I will pursue first. These queries are so complicated that stripping it down will take more time than it will to figure out an answer myself. I was just hoping for an easy Hans-supplied-obvious-once-you-see-it answer so I wouldn't have to do my own work!
Peter N

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Getting desired results in a report

Post by HansV »

I'd have loved to provide an easy solution, but I simply have far too little information to go on. I hope you'll be able to figure it out yourself.
Best wishes,
Hans

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

Re: Getting desired results in a report

Post by petern »

I was able to tweak the query that provides MinOfISAPMeetingDate to also give me the worker who recorded that meeting then rewrite my IIf to be:
NewNot: IIf(([MinOfISAPMeetingDate] Between [Forms]![frmISAPDates]![txtStartDate] And [Forms]![frmISAPDates]![txtenddate]) And ([UserID] = [Worker]),"NEW","")

Worker is an alias for UserID. If the meeting in question was held by a worker other than the one who recorded the first meeting, NEW won't appear. This gives me the results I need. :clever: Thanks for looking at it anyways. You'll be earning more beer off of me sooner, I'm sure.
Peter N

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Getting desired results in a report

Post by HansV »

I'm glad you have worked it out! :thumbup:
Best wishes,
Hans