Calculate rolling 12 mo period

[Leah]

Hi - I'm using Access 97. My VBA experience is very
limited.

I need to calculate existing customers spending activity
from contract start date to contract start date - 12
months. I also need to calculate new customers spend from
contract start date to start date + 24 months.

I was hoping to do this with two additional fields in my
qryCustomerSpend but as you can see, the # of chars in the
field can become quite large.

Existing customer example:
IIf ([CustomerStartDate] = #1/1/2003# , [jan02$]+[feb02
$}...+[dec02$],iif[CustomerStartDate] = #2/1/2003#,[feb02$]
+[mar02$].....+ [jan03$] etc....)

I would like to write VBA functions that use the contract
start date as a parameter. The functions should add the
query fields to obtain a "previous 12 month from contract
date total" and a "contract date + 24 month total" but am
unsure about how to define the query fields so that VBA
will recognize them. Is a recordset required?

Thanks for helping a newbie!!

 

[Van T. Dinh]

I have a feeling that the Table Structure has not been properly designed and
implemented.

If your Field names are actually [jan02$], [feb02$] , ... then you are
storing data in the Field names which is prohibited by the Relational
Database Design Principles. Data should always be stored in the Field
Values, NOT the Field names.

Also the problem with the current structure is that you need to add extra
Fields as time go by. Properly-designed databases should be able to store
additional data by adding additional Records, NOT Fields since adding Fields
will require major changes to other database objects like Queries / Forms /
Reports / etc ... (in fact everything in Access because Tables are the basis
for everything else). No one would want a database application that needs
major changes every month!

Note also that proper Access (or any other RDMS) Tables tend to have small
number of Columns (Fields) and large number of Rows (Records). Your Table
seems to go the other way (large number of Fields).

Your idea of adding 2 extra Fields also goes against the Relational Database
Design Principles as they are *calculated* values and calculated values
should not be stored in the Table(s) in general.

Since Access (and other RDBMSs) is designed to work with properly-structured
and normalised Tables (your Table is not normalised), you will have lots of
other problems in later stages of the database implementation.

I would suggest the best way to go forward is to take a few steps backwards,
read up on the Relational Database Design Theory / Principles and Database
Normalisation and then re-structure your Table(s) according to these.

Once you have the correct Table Structure, later development stages will be
a lot easier to design and to implement.

--
HTH
Van T. Dinh
MVP (Access)

Hi - I'm using Access 97. My VBA experience is very
limited.

I need to calculate existing customers spending activity
from contract start date to contract start date - 12
months. I also need to calculate new customers spend from
contract start date to start date + 24 months.

I was hoping to do this with two additional fields in my
qryCustomerSpend but as you can see, the # of chars in the
field can become quite large.

Existing customer example:
IIf ([CustomerStartDate] = #1/1/2003# , [jan02$]+[feb02
$}...+[dec02$],iif[CustomerStartDate] = #2/1/2003#,[feb02$]
+[mar02$].....+ [jan03$] etc....)

I would like to write VBA functions that use the contract
start date as a parameter. The functions should add the
query fields to obtain a "previous 12 month from contract
date total" and a "contract date + 24 month total" but am
unsure about how to define the query fields so that VBA
will recognize them. Is a recordset required?

Thanks for helping a newbie!!

 

[Jen]

Hi Leah,

I would have to completely agree with Van. I actually did
a redesign of a database that was structured similar to
what you've got and in that process saved a sizable amount
of $/maintenance for the company due to the issues that
Van addressed about having to change fields and objects
over time. Another advantage of taking a couple steps
back and actually getting the proper table structure is
that it will give your applications a whole lot more power.

Regards,
Jen