Count Unique Records

  • Thread starter Thread starter Pam
  • Start date Start date
P

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
 
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
'====================================================
 
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
 
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
 
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


Pam said:
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

John Spencer said:
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
'====================================================
Click to expand...
Click to expand...
 
Back
Top