Calculate a year to date

[mccallen60]

Hello,
Here’s my problem. I receive data on number of prescriptions on a monthly
basis. It is sent to me with 24 columns which represent a rolling 24 month
time period. They are labeled month01 (oldest) to month24 (newest). So for
instance, if the current month is February, the data represents a time period
of February 07 (month01) to January 09 (month24). I need to be able to
calculate a fiscal year to date number of prescription. Our FY runs from July
to June. I want to be able to use the same database each month without having
to make a lot of modifications to accommodate this calculation. Any
suggestions will be appreciated. Fyi, I have never used visual basic. If your
suggestion is a VB method, please keep this in mind. Thank you. Kevin

 

[Jack Cannon]

Kevin,

While you could use Access to meet your objective, it appears to me that it
would be more appropriate to use a summation function on the spreadsheet that
you are receiving. Just identify the column that corresponds to the last
July and then sum all data from that column through column 24.

Jack Cannon

 

[mccallen60]

Jack,
Thanks for your response. I believe I will go with your suggestion and
handle this outside of Access. Just out of curiosity, how would you handle
this issue in Access?

 

[John W. Vinson]

Jack,
Thanks for your response. I believe I will go with your suggestion and
handle this outside of Access. Just out of curiosity, how would you handle
this issue in Access?

I don't want to speak for Jack, but let me make a suggestion. You say:

Here’s my problem. I receive data on number of prescriptions on a monthly
basis. It is sent to me with 24 columns which represent a rolling 24 month
time period. They are labeled month01 (oldest) to month24 (newest). So for
instance, if the current month is February, the data represents a time period
of February 07 (month01) to January 09 (month24).

This is reasonable spreadsheet data, but it sort of stinks from a database
perspective! What you might want to do is a "Normalizing Union Query" to
massage this data into a tall-thin data structure which you can use
productively in Access. You could import or link to this spreadsheet, and
create a query in the SQL window

SELECT <identifier fields>, DateSerial(Year(Date()), Month(Date() - 24) AS
ScripDate, Month01 AS TheValue FROM spreadsheet
WHERE Month01 IS NOT NULL
UNION ALL
SELECT <identifier fields>, DateSerial(Year(Date()), Month(Date() - 23) AS
ScripDate, Month02 AS TheValue FROM spreadsheet
WHERE Month02 IS NOT NULL
UNION ALL

<etc through all 24 fields>

You don't say what else is in your table or even what's in the Month01 field
but this query would at least give you the source for appending into a
normalized table, from which you could then run whatever queries you like.

 

[Jack Cannon]

I don't want to speak for Jack, but let me make a suggestion.
That is fine, John.
You always make very good suggestions.
And yes, I will admit it, your suggestions are often better than mine anyway.
So don't ever worry about butting in if you feel it is justified.

Jack Cannon

 

[titemimi]

That is fine, John.
You always make very good suggestions.
And yes, I will admit it, your suggestions are often better than mine
anyway.
So don't ever worry about butting in if you feel it is justified.

Jack Cannon

 

[mccallen60]

Gentlemen, thank you very much. This information has been very helpful.