number of active customers per period

[van_haan]

My database of customers has customers with begin_date and end_date.
Each year has 13 periods (each 4 weeks begins a new period).
I have a problem with calculating number of active customers per
periods, 13 of them.
For example:
If begin_date of customer A is 02-01-07 and end_date is not filled in
the customer should be counted as active till today (19-03-2007) which
means that he should be counted as active in the periods 01, 02, 03 if
I run a query today. When eind_date is filled in the customer should
not appear as active in a period of eind_date.

I hope that somebody can help me with this one?!!!
Any help will be highly appreciated. Thanks in advance!

Haan

[engles]

On Mar 19, 2:52 pm, "van_haan" <[e-mail]:

My database of customers has customers with begin_date and end_date.
Each year has 13 periods (each 4 weeks begins a new period).
I have a problem with calculating number of active customers per
periods, 13 of them.
For example:
If begin_date of customer A is 02-01-07 and end_date is not filled in
the customer should be counted as active till today (19-03-2007) which
means that he should be counted as active in the periods 01, 02, 03 if
I run a query today. When eind_date is filled in the customer should
not appear as active in a period of eind_date.

I hope that somebody can help me with this one?!!!
Any help will be highly appreciated. Thanks in advance!

Haan

One way to do this is to create a table with the first and last dates
for the 13 periods. Then create a query and have the two tables (your
orginal and the new one with the period range). For the end date,
there are several ways to handle, but you can specify the end date
exactly as you stated above via the nz function (e.g.
LastDate:nz([End_Date],Date())). With that you can check that the
customer begin and end dates are in the range.

Hope this is enough for you.

-- Larry

[van_haan]

On Mar 20, 3:28 am, [e-mail] wrote:

On Mar 19, 2:52 pm, "van_haan" <[e-mail]:

My database of customers has customers with begin_date and end_date.
Each year has 13 periods (each 4 weeks begins a new period).
I have a problem with calculating number of active customers per
periods, 13 of them.
For example:
If begin_date of customer A is 02-01-07 and end_date is not filled in
the customer should be counted as active till today (19-03-2007) which
means that he should be counted as active in the periods 01, 02, 03 if
I run a query today. When eind_date is filled in the customer should
not appear as active in a period of eind_date.

I hope that somebody can help me with this one?!!!
Any help will be highly appreciated. Thanks in advance!

Haan

One way to do this is to create a table with the first and last dates
for the 13 periods. Then create a query and have the two tables (your
orginal and the new one with the period range). For the end date,
there are several ways to handle, but you can specify the end date
exactly as you stated above via the nz function (e.g.
LastDate:nz([End_Date],Date())). With that you can check that the
customer begin and end dates are in the range.

Hope this is enough for you.

-- Larry

Thanks Larry!