Calculate number of items in any given period?

[Henro]

Hello all,

I have 2 situations which I do not know how to handle and I cannot find
any solution:

Problem 1:
I need to calculate the number of active memberships at any given moment.

Background on the data:

* Period = a month
* I know the amount of active memberships at start (users should be
preferably be able to correct this amount)
* From that moment I am told how many memberships in a period are
activated or closed (the date is in the record)

What happens:

I know e.g. that at 1 october I have 2000 active memberships. Then I
will get daily reports of how many memberships are activated and how
many are stopped. I want to know how many memberships are active at the
end of the month

Example:

three variables:

A= number of memberships at the beginning of the month
B= number of activated memberships
C= number of closed memberships


at 30st june I have 2000 (A) active memberships. In the month july 126
(B) are activated and 67 (C) have been stopped.
So 31st of july I have 2000 + 126 - 67 active memberships (totals 2059
memberships at 31 july).

Now I need to report two things about july:
2000 + 126 = 2126
2000 + 126 - 67 = 2059

And this is where it gets tricky for me:
at 31st july I have 2059 active memberships. In the month august 172 are
activated and 48 have been stopped.
So 31st of august I have 2059 + 172 - 48 active memberships (totals 2183
memberships at 31 august).

I need to report
2059 + 172 = 2231
2059 + 172 - 48 = 2183

As you will understand I have to start the next month with the value
2183, then add and substract the activated and closed memberships

I think this should be done with some loop functionality but I do not
have a clue as how to do this or how to start.



Problem 2:

I have a daily made report. In this report a value is calculated that
needs to be added to an account. I know the starting value of this account.

Example:
1st october amount of money in the account (X) is € 115.000
I make a report and in this report is calculated (Y) that € 10.000
should be added to the account. (X+Y) So next month I have to start with
€ 125.000.
I think that I should add Y to a table (with the date it was calculated)
and then I can easily calculate the new value I should use for X.....

How do I do this?

Any suggestions are very appreciated.!

TIA,

Henro

 

[Douglas J. Steele]

I won't be greedy, and will just answer your first question <g>

How are your tables modelled?

Let's assume you've got an EffectiveDt and ExpiryDt field (with ExpiryDt
being Null until they're no longer a member)

To find all members at a particular time, use:

DCount("*", "Members", _
"EffectiveDt <= " & Format(Date(), "\#mm\/dd\/yyyy\#") & _
" And Nz(ExpiryDt, Date()) >= " & Format(Date(), "\#mm\/dd\/yyyy\#"))

To know how many members were current at the beginning of this month, you
can use:

DCount("*", "Members", _
"EffectiveDt <= " & Format(DateSerial(Year(Date()), Month(Date()), 1),
"\#mm\/dd\/yyyy\#") & _
" And Nz(ExpiryDt, Date()) >= " & Format(DateSerial(Year(Date()),
Month(Date()), 1), "\#mm\/dd\/yyyy\#"))

To know how many became members in the current month, you can use:

DCount("*", "Members", _
"Format([EffectiveDt], 'yyyymm') = " & Format(Date(), "yyyymm") & _
" And ExpiryDt Is Not Null")


To know how many stopped being members in the current month, you can use:

DCount("*", "Members", _
"Format([ExpiryDt], 'yyyymm') = " & Format(Date(), "yyyymm"))

If you don't have Effective and Expiry dates, it's going to be more
difficult (and I won't be able to help you without knowing your table
design!)

 

[Henro]

Thank you Douglas!

As for my table (there is only one involved here)

The first field indicates a membership [Recordtype]
Another field tells me whether it is activated or closed [ResponseCode]
And I have a datefield [MBMDate]

If the recordtype = 9999 and responsecode = 01 then it is an activation
at [MBMDate]
If responsecode = 02 then it is an closure at [MBMDate]

I think this will influence your code?
And a question; is it wise to use Domain functions? I heard they are
pretty slow and should be avoided? Would it be wise to use a transaction
table to store values?

Thanks for thinking and bearing with me!

Henro




Douglas J. Steele schreef:

 

[Douglas J. Steele]

Storring the results in a table doesn't really make sense. You should never
store computed values: you recalculated them when you need them.

Your table really doesn't seem well designed: it sounds more like a
transaction table than a table designed as part of a relational model.

And obviously there has to be more to your table than what you've described:
you haven't mentioned any way of knowing which member has activated or
closed.

Assuming you've got a MembershipNumber, you can create a query that
simulates the design I mentioned in my first post. This can be done in a
single query in Access 2000 or higher:

Select Joining.MemberNumber,
Joining.MBMDate As EffectiveDt,
Leaving.MBMDate As ExpiryDt
FROM
(SELECT MemberNumber, MBMDate
FROM Membership
WHERE RecordType=9999
AND ResponseCode=1) AS Joining
LEFT JOIN
(SELECT MemberNumber, MBMDate
FROM Membership
WHERE RecordType=9999
AND ResponseCode=2) AS Leaving
ON Joining.MemberNumber = Leaving.MemberNumber

Of course, if a single member can have joined, left then rejoined and kept
the same MemberNumber, you'll have to do some more fancy manipulations to
ensure that you're lining up the appropriate Joining and Leaving records
with one another.

No offense, but worrying about whether or not to use Domain functions seems
rather immaterial: your database doesn't sound to me as though it was
designed for its intended task.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you Douglas!

As for my table (there is only one involved here)

The first field indicates a membership [Recordtype]
Another field tells me whether it is activated or closed [ResponseCode]
And I have a datefield [MBMDate]

If the recordtype = 9999 and responsecode = 01 then it is an activation at
[MBMDate]
If responsecode = 02 then it is an closure at [MBMDate]

I think this will influence your code?
And a question; is it wise to use Domain functions? I heard they are
pretty slow and should be avoided? Would it be wise to use a transaction
table to store values?

Thanks for thinking and bearing with me!

Henro




Douglas J. Steele schreef:

I won't be greedy, and will just answer your first question <g>

How are your tables modelled?

Let's assume you've got an EffectiveDt and ExpiryDt field (with ExpiryDt
being Null until they're no longer a member)

To find all members at a particular time, use:

DCount("*", "Members", _
"EffectiveDt <= " & Format(Date(), "\#mm\/dd\/yyyy\#") & _
" And Nz(ExpiryDt, Date()) >= " & Format(Date(), "\#mm\/dd\/yyyy\#"))

To know how many members were current at the beginning of this month, you
can use:

DCount("*", "Members", _
"EffectiveDt <= " & Format(DateSerial(Year(Date()), Month(Date()), 1),
"\#mm\/dd\/yyyy\#") & _
" And Nz(ExpiryDt, Date()) >= " & Format(DateSerial(Year(Date()),
Month(Date()), 1), "\#mm\/dd\/yyyy\#"))

To know how many became members in the current month, you can use:

DCount("*", "Members", _
"Format([EffectiveDt], 'yyyymm') = " & Format(Date(), "yyyymm") & _
" And ExpiryDt Is Not Null")


To know how many stopped being members in the current month, you can use:

DCount("*", "Members", _
"Format([ExpiryDt], 'yyyymm') = " & Format(Date(), "yyyymm"))

If you don't have Effective and Expiry dates, it's going to be more
difficult (and I won't be able to help you without knowing your table
design!)

 

[Henro]

No offence is taken at all, I am not a professional developer and I am
happy with any help or suggestion offered.

I do not need to know WHICH member activated or closed membership, just
the number of activation and closures. To be more precise: I need to
count the number records that fit the

WHERE RecordType=9999 AND ResponseCode=1
and
WHERE RecordType=9999 AND ResponseCode=2

statements. Using those I need to calculate the number of active
memberships at the end of the month using a startposition. Each period
the number of active memberships will be billed, therefore I need to
know the startingposition of each month + the activated memberships.
Next month the startingposition is decreased by the number of closed
memberships of the month before.

So actually (I am thinking aloud now) it should be:

Startposition + the activated memberships - closed memberships of the
month before.

And you are right about the table, it actually IS a transaction table.
That is: I receive a (daily) ASCII file with information in it, I only
need to count certain items in that file. The two problems I mentioned
in my first post are the only ones that I am having trouble with since I
need to compare numbers of two different periods. Not all of these
periods will be at all times be available in this database. That is why
I was thinking about saving only the necessary values in a table (those
would be the startposition of each month and the amount of money in the
account I was speaking of in problem 2 together with the period they
belong to).

You know, you really made me think of this.....

Thank you!





Douglas J. Steele schreef: