By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

number of active customers per period

P: n/a
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

Mar 19 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Mar 19, 2:52 pm, "van_haan" <vanH...@gmail.comwrote:
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

Mar 20 '07 #2

P: n/a
On Mar 20, 3:28 am, eng...@ridesoft.com wrote:
On Mar 19, 2:52 pm, "van_haan" <vanH...@gmail.comwrote:
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!

Mar 20 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.