Count Unique Records

[Pam]

I've read several of Allen Browne's solutions to count unique records in a
report by creating text boxes and placing in group footers with control
source =1 and running sum over group. I've made this work for a total of
invoices in my report, but I need to have the invoices grouped by three
month segments (not the usual quarterly months) in the following layout:

FirstQtr SecondQtr ThirdQtr FourthQtr

FirstQtr count would be based on InvDate (which is a field in detail section
of report) between 11/1/07 and 1/1/08. I'm not sure how to get the report
broken down as needed.

Any help is greatly appreciated.
Thanks in advance.
Pam

 

[John Spencer]

You can use
Format(DateAdd("m",-10,InvDate),"q") to return the quarter
Or
DatePart("q",DateAdd("m",-10,InvDate))

or go the other way and add 2 to the date instead of subtracting 10.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Pam]

John,

Thanks for the reply, but I'm not sure I made myself clear in first message.
I have a report grouped by salespersons and in that grouping I have a text
box that totals [sales] based on months using this equation that works great
thanks to Duane Hookom:
=Sum(Abs([InvDate] Between #11/1/2007# And #1/31/2008#)*[sales])
I have four of these text boxes spread across the salesperson grouping for
each of the quarters I'm dealing with.

Now, I need a count of invoices to meet each date criteria for each
salesperson. When I use the same equation as above, I get a large number.
For example, one salesperson should have the following invoice counts for
each quarter for a grand total of 319.
FirstQtr SecQtr ThirdQtr FourthQtr
61 88 76 94

I can get the 319 using Allen Browne's grouping on [inv] with running sum
textbox, but I can't get a total for each quarter as needed above unless I
use a series of grouping queries. If I use
=Sum(Abs([InvDate] Between #11/1/2007# And #1/31/2008#)*[inv]) it returns
100781514. I've tried count in place of sum and changing =Abs(sum(... and
cannot get anything to work. I really would like to keep the calculations
on the report and have date criteria in the calculations.

If you provide any help with this, I would geatly appreciate it.
Thanks again,
Pam

 

[Pam]

John,
Please disregard earlier message. I now have it working as needed. I used
the following in the text box in the inv footer
=IIf([invdate] Between #11/1/2007# And #1/31/2008#,1,0). I then made text
boxes across report of date criteria and all worked well.
Thanks again for your time and help.
Pam

 

[Duane Hookom]

Or use the expression from total sales without the sales:

=Sum(Abs([invdate] Between #11/1/2007# And #1/31/2008#))
--
Duane Hookom
Microsoft Access MVP

John,
Please disregard earlier message. I now have it working as needed. I used
the following in the text box in the inv footer
=IIf([invdate] Between #11/1/2007# And #1/31/2008#,1,0). I then made text
boxes across report of date criteria and all worked well.
Thanks again for your time and help.
Pam

You can use
Format(DateAdd("m",-10,InvDate),"q") to return the quarter
Or
DatePart("q",DateAdd("m",-10,InvDate))

or go the other way and add 2 to the date instead of subtracting 10.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================