Help! Newbie seeks help on date issues

[cheerfulskeptic]

Hi all,
I have a database with a table warranty, with rows for warranties, and these rows contain warranty ID, Install Date and Inactive Date.
Each warranty entry is linked to an account ID. an account can have more than 1 warranty (each entry for the warranty is on a separate row)
Here is the problem.
The report should show the count of occurences per month (columns are Jan - Dec) of a warranty for an account.
so if a warranty for Account A has 3 install dates in Jan, the Jan col should show 3. This 3 is propagated in Feb (if the inactive date is NOT in feb), Mar, etc. so on
until if the inactive date is in July, the count for AUGUST (ie next month) gets reduced by 1, and this new value (2) shows throughout.
I have everything working out, except the inactive date part. ie, it does not show up in the next month, it shows up in the month the inactive date is in.
how can i fix this? thanks!

 

[synapsevampire]

Your formula for counting in a month is based on a given month being >= install date, and <= Inactive Date, is that correct?

It sounds like you group by account, then by warranty, then in a formulas count for the month of report >= max(install) and either isnull(inactive) or the month of report < max(inactive) (assuming that an inactive date may be written in advance).

-k