counting problem

  • Thread starter Thread starter JBoulton
  • Start date Start date
J

JBoulton

Hi, All!

My data originates in an Oracle database and is accessible to me vi
Access. The three fields I'm interested in are IdNo, Inception (date
and Termination (date).

What's the best way to count the IdNos that existed at the end of 199
and 2000? I need to do this for termination dates 2000 - 200
(Current.)

I have used Query to get the year end list of IdNo, Inception an
Termination for 1999 - 2004 and I can filter 1999 against 2000 and not
the number of matches, but there seems like there should be a bette
way.

TIA,

Ji
 
I have used Query to get the year end list of IdNo, Inception and
Termination for 1999 - 2004 and I can filter 1999 against 2000 and note
the number of matches, but there seems like there should be a better
way.
Click to expand...

try something like
=sumproduct(--(inception1:inception100=1999),--(termination1:termination100
=2000)

and replace "inception" with the column of the inception dates, and
"termination" with the column of termination dates, and "100" with how many
rows there are.
 
Jon,

Thanks for the idea. The following produces 0.00.

=SUMPRODUCT(--('All Data'!C1:C4152=1999),--('All Data'!D1:D4152=2000))

Col C is inception date and col D is termination date. The data i
these fields is standard mm/dd/yyyy.

I don't understand:

1) how this could catch the idno in col B
2) how this could get from mm/dd/yyyy to 1999 or 200
 
Col C is inception date and col D is termination date. The data in
these fields is standard mm/dd/yyyy.
Click to expand...

Okay, I didn't know that when I wrote it.
I don't understand:

1) how this could catch the idno in col B
Click to expand...

I looked at your message and the only thing I could find you asking for
was a straight count. What do you want to do with column B, the ID
numbers? Do you want a count, or do you want the actual list? If you
want the actual list, then Query, or Data/Filter is the way to go.
2) how this could get from mm/dd/yyyy to 1999 or 2000
Click to expand...

You didn't say how your dates were stored. I assumed you just had the
year. So change the formula to say:

=sumproduct(--(year('All Data'!C1:C4152)=1999),--(year('All Data'!
D1:D4152)=2000)
 
Jon,

I have already tried changing the formula to year() within th
=sumproduct() function. That produces the # VALUE! error.

I've also tried <1999 and >2000 and get the same error.

This does produce a correct result for the number of IdNos that wer
still active at the end of 2000.

=SUMPRODUCT(--(('All Data'!B1:B4158)<DATE(1999,12,31)),--(('Al
Data'!C1:C4158)>DATE(2000,12,31)))

I changed the access query to deliver only IdNo (Col A), inception dat
(Col B) and termination date (Col C)

I'm trying to count all the IdNos that were active at the end of 199
and still active (not terminated) at the end of 2000.

What function would you recommend for getting the total active at th
end of 1999?

TIA
Ji
 
Back
Top