Calculate if number of records is increasing or decreasing

[DanJames]

Hi,

I have a database with a table of every client (current and cancelled clients) and a query just showing the current clients.

I want to show if the number of clients is increasing or decreasing, but I'm unsure how?

I was thinking maybe calculating the average number of records for this month, and then seeing if the current client is above or below this average.

In VBA I have CRecords = ("*", "qCurrentClient")
TRecords = ("*", "tblClientDetails")

Thanks in advance - any ideas welcomed,
Dan

 

[plog]

For every client you are going to need a date they became a client and a date they stopped being a client. As long as you have those 2 fields, this is possible.

What you would then do is create a table of dates to do reporting on. This table would contain one date field which would hold the last day of the month of a month you would like to report on (i.e. 1/31/2011, 2/28/2011, 3/31/2011...12/31/2011).

You would then need to create a function that takes the report date from that table, the clients begin and end dates and determines if the client was a client on that report date.

To get it to all work together in a query you would bring in both the report period table and client table in a cartesian join (that means both tables are in the query, but not linked to another.) One field would be the report date the other field would be the result of the function you created.

That's an overview if you get stuck along the way, let me know what specific questions you have.

 

[MarkK]

This I would never do...

What you would then do is create a table of dates to do reporting on. This table would contain one date field which would hold the last day of the month of a month you would like to report on (i.e. 1/31/2011, 2/28/2011, 3/31/2011...12/31/2011).

A date is a measure of time like dollars is a measure of money and inches is a measure of length. In the case of dollars and inches you'd never make a table with a series of threshholds and then link to it. It may seem like a day or a month is a discrete object, but the representation in data is linear just like any other measure of quantity. Check out the VBA.Partition() function if you want to group data in arbitrary but regular chunks of a particular quantity.

To count if a client was current during a period of time:

WasClientDuringPeriod = DateStartAsClient < EndOfPeriod AND DateEndAsClient > StartOfPeriod

To count if a client was current at a single point in time:

WasClientAtTime = DateStartAsClient < PointInTime AND DateEndAsClient > PointInTime

To determine the change in a value over time you need to calculate the difference between the value at TimeA and the value at TimeB. Then you can determine, for instance, that you have 3 more clients this month than you had last month, but that doesn't contribute to, "seeing if the current client is above or below this average."

To determine the client's placement in respect to a group (average) of values you need to compare to another dimension of your data, for instance, you could see how a client's height compares to an average of the heights of other clients. It doesn't seem meaningful though, to compare a count of a single client to a count of other clients. That will just tell you that if you have ten clients, then this one client will be 1/10 of your client load.

Hope this helps,
Mark

 

[smig]

WasClientDuringPeriod = DateStartAsClient [COLOR=#8b0000]=<[/COLOR] EndOfPeriod AND DateEndAsClient [COLOR=darkred]>=[/COLOR] StartOfPeriod

WasClientAtTime = DateStartAsClient [COLOR=darkred]=<[/COLOR] PointInTime AND DateEndAsClient [COLOR=darkred]>=[/COLOR] PointInTime

small fix to the code