Here's a fun one.

[RTimberlake]

I am building a database in Access that will replace an Excell spreadsheet.
This database will be used to perform monthly evaluations on the boys that
are in our program. I am having trouble figuring out how to make access do
what our spreadsheet is doing.
I need to be able to identify the Month and Year that an evaluation was
done. (I have figured this part out.) I also need to be able to identify
Month1, Month2, etc. depeding on when a boy arrived in our program.
For example: Johnny arrived in March 2005. For his April evaluation I need
to be able to pull a report for April and a report that will compare him to
other boys in Month2.
I hope this makes sense. Any help would be greatly appreciated.

 

[Dirk Goldgar]

I am building a database in Access that will replace an Excell
spreadsheet. This database will be used to perform monthly
evaluations on the boys that are in our program. I am having trouble
figuring out how to make access do what our spreadsheet is doing.
I need to be able to identify the Month and Year that an evaluation
was done. (I have figured this part out.) I also need to be able to
identify Month1, Month2, etc. depeding on when a boy arrived in our
program.
For example: Johnny arrived in March 2005. For his April evaluation I
need to be able to pull a report for April and a report that will
compare him to other boys in Month2.
I hope this makes sense. Any help would be greatly appreciated.

Am I right in assuming you have an EvaluationDate field and an
ArrivalDate field, or something like that? Then the simplest way to
determine the number of months between ArrivalDate and EvaluationDate --
which appears to be what you need -- is to calculate the expression

DateDiff("m", [ArrivalDate], [EvaluationDate])

HOWEVER, it's vital that you realize that the above expression counts
only month *boundaries* between the dates. As far as it is concerned,
the one-day difference between 31-Jan-2005 and 1-Feb-2005 is still 1
"month". Depending on how you run your operations -- for example, if
you always do your evaluations spaced out by about a full month -- this
may work out okay for you.

On the other hand, if that simplistic result isn't good enough, you have
to decide what constitutes a "month" for your purposes. It would be
relatively simple if all months were the same length, but they aren't.
You could arbitrarily choose 30-day increments, in which case the
month-number for a given evaluation could be worked out as

DateDiff("d", [ArrivalDate], [EvaluationDate]) \ 30

Another, more complicated approach would be to use a rule that tests
both the number of month boundaries -- DateDiff("m", ...) -- and also
compares the day-numbers to see if something that looks like a full
month has elapsed. For example,

DateDiff("m", [ArrivalDate], [EvaluationDate]) +
(Day([EvaluationDate]) < Day([ArrivalDate]))

However, that alone won't deal properly with the different-length
months. For that, you need to make some sort of policy decision about
how to handle that -- maybe decide whether a date is a month-end or not,
and adjust for that. That would require a more complicated expression,
which might best be embodied in a function rather than placed inline.

 

[RTimberlake]

That is not what I need. Perhaps I wasn't clear in the desired outcome.
I need to be able to track a boys score for his first month (M1), second
month (M2), third month (M3), fourth month (M4), etc. as well as the actual
month, ie. Jan., Feb., March, etc.
For a boy that came in March, his March evaluation would be evaluation M1.
But, for a boy who came in February, his March evaluation would be evaluation
M2. The M1, M2, etc. depends on the arrival date of the boy in question.
Perhaps I need a table for each boy that specifies what M1, M2, etc. are.
Thoughts?

I am building a database in Access that will replace an Excell
spreadsheet. This database will be used to perform monthly
evaluations on the boys that are in our program. I am having trouble
figuring out how to make access do what our spreadsheet is doing.
I need to be able to identify the Month and Year that an evaluation
was done. (I have figured this part out.) I also need to be able to
identify Month1, Month2, etc. depeding on when a boy arrived in our
program.
For example: Johnny arrived in March 2005. For his April evaluation I
need to be able to pull a report for April and a report that will
compare him to other boys in Month2.
I hope this makes sense. Any help would be greatly appreciated.

Am I right in assuming you have an EvaluationDate field and an
ArrivalDate field, or something like that? Then the simplest way to
determine the number of months between ArrivalDate and EvaluationDate --
which appears to be what you need -- is to calculate the expression

DateDiff("m", [ArrivalDate], [EvaluationDate])

HOWEVER, it's vital that you realize that the above expression counts
only month *boundaries* between the dates. As far as it is concerned,
the one-day difference between 31-Jan-2005 and 1-Feb-2005 is still 1
"month". Depending on how you run your operations -- for example, if
you always do your evaluations spaced out by about a full month -- this
may work out okay for you.

On the other hand, if that simplistic result isn't good enough, you have
to decide what constitutes a "month" for your purposes. It would be
relatively simple if all months were the same length, but they aren't.
You could arbitrarily choose 30-day increments, in which case the
month-number for a given evaluation could be worked out as

DateDiff("d", [ArrivalDate], [EvaluationDate]) \ 30

Another, more complicated approach would be to use a rule that tests
both the number of month boundaries -- DateDiff("m", ...) -- and also
compares the day-numbers to see if something that looks like a full
month has elapsed. For example,

DateDiff("m", [ArrivalDate], [EvaluationDate]) +
(Day([EvaluationDate]) < Day([ArrivalDate]))

However, that alone won't deal properly with the different-length
months. For that, you need to make some sort of policy decision about
how to handle that -- maybe decide whether a date is a month-end or not,
and adjust for that. That would require a more complicated expression,
which might best be embodied in a function rather than placed inline.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

That is not what I need. Perhaps I wasn't clear in the desired
outcome.
I need to be able to track a boys score for his first month (M1),
second month (M2), third month (M3), fourth month (M4), etc. as well
as the actual month, ie. Jan., Feb., March, etc.
For a boy that came in March, his March evaluation would be
evaluation M1. But, for a boy who came in February, his March
evaluation would be evaluation M2. The M1, M2, etc. depends on the
arrival date of the boy in question. Perhaps I need a table for each
boy that specifies what M1, M2, etc. are. Thoughts?

From what you've said so far, I still think what I proposed is what you
need. It's not necessary, nor is it good design, to have a table with
repeating fields such as Month1, Month2, Month3, etc. And it's also not
necessary, nor is it (generally) good design, to store calculated data,
data that depends entirely on values that are already stored, as you say
the month number depends on the arrival date.

It's entirely possible that I am missing some basic information about
your problem domain, but as far as I can tell, given tables like these:

tblBoys
------------------
BoyID (primary key)
FirstName
LastName
MiddleName
ArrivalDate
(other fields describing the boy)

tblEvaluations
--------------
BoyID (foreign key to tblBoys, compound pk)
EvaluationDate (compound pk)
EvaluationResult

.... it is entirely possible to create queries that transform and present
the data in a structure like this:

BoyID
BoyLastName, etc.
M1_Score
M2_Score
M3_Score
M4_Score
M5_Score
... etc.

If you understand this, but still maintain that you need to physically
store your data in a spreadsheet-like fashion, we can pursue that
approach, but I think it will cause you problems in the long run.

 

[RTimberlake]

Thank you for your help. I am going to attempt what you have suggested. After
I looked at it again I think it will work. Unfortunately I am not an Access
guru. I will let you know how things go.

 

[Dirk Goldgar]

Thank you for your help. I am going to attempt what you have
suggested. After I looked at it again I think it will work.
Unfortunately I am not an Access guru. I will let you know how things
go.

Feel free to come back to the newsgroup for help. If you have questions
about how to make queries to transform the data, you may want to post
them in the Queries newsgroup. There are lots of people in these
newsgroups who can help you, but if you feel you need to talk to me
specifically, you can put my name in the subject line of your message
and I'll probably see it, as I have Outlook Express set up to highlight
such messages. (Of course, then you're subject to my availability,
which hasn't been that good lately.)

Anyway, good luck with your database!