Bingo! Your latest SortYear formula worked! You're
right, I already had it set so that you can click a button
on a person's form to filter out all training records
except for that person's. Thanks much!
-----Original Message-----
I did assume that each person would have a different hire
date.
Sortyear would group those who were hired in 1993 with
those who were hired
in 1994. Then it would group those hired in 1995 with
those hired in 1996.
But are you saying that the same person has more than one
hire date?
It sounds more as if you need to know when each person's
current 2 year
allowance ends rather than grouping the dates of several
people together.
So if he was hired in 1993, one block would end in 1995,
the next would end
in 1997 right up to the present day. Is that right.
I may need to know more about your database structure. It
sounds as if it
needs a Staff Table with the staff details including hire
date.
It would also have a TrainingBlock table which lists all
the TrainingBlocks
available to staff.
They are both linked to a Training table and, I assume,
that this will have
the block of training the staff member received with some
kind of date
(TrainingDate or TrainingYear) to let you know when it
happened.
All you need to know now is if his hire date year is an
odd or even number
and then perform the grouping using a SortYear field on
the TrainingDate
field.
So you add all the fields from TblTraining to the query
and Staff name
including hiredate, you will also want to add the
TrainingBlock from the
TrainingBlock table.
Now you could do SortYear as follows
SortYear: IIF(Year([HireDate]) Mod 2 = 0,
(Year([TrainingDate]))-((Year([TrainingDate])) Mod 2),
(Year([TrainingDate])+1)-((Year([TrainingDate])+1) Mod 2))
Group your report first by a Staff table field eg StaffID
then by SortYear
then by TrainingDate
You don't need lots of different reports do you, don't
you just need one
report which you could filter from a button in your staff
form to show the
records for one particular staff member.
Evi
Don't worry...your "+1"s were showing up in my messages.
I added the SortYear field to my query. Now when I use
that field as the Group field, it lumps everything
together. Perhaps I should also explain that the date
field not a fixed date (it is the date a person is
hired). So for one report, the hire date may be
1/18/1993, while for another report the date may be
6/7/2002. What I am trying to do is to make individual
reports showing the training that our staff members have
received. We are given a monetary training allowance
that
is good for two years, starting with the hire date.
After
the two years, you start over with a new training
allowance. We want to be able to see how much an
individual has used for each of his/her two-year
training "blocks", and how much is available in the
current "block" . I used my hire date (1/18/1993) as the
example, and it groups all my training over the years
under one group: "1994". I didn't put anything in the
criteria row of the query because I didn't know how to
change it to fit a non-fixed date. Now what?
-----Original Message-----
Grrr! I've just realised that each time I try to read
my
own message it
converts back to Unicode 7. Heaven knows where I've set
that up. Well, in
case you can't see it there should be a
plus and a one after each (Year([BookDate])
Evi
I've just realised what is happening. My message was
formatted as Unicode
7
which simply takes out the plus sign. And it only
takes
it out after I've
posted it! Wow! I'll have to watch out for that one.
Right here I am again with Western European encoding:
(Year([BookDate])+1)-((Year([BookDate])+1) Mod 2)
Evi
message
berlin.de...
Darn late nights! that's twice I thought I'd
pasted
in the correct
answer
and my clipboard has ignored me and pasted in the
old
one. It should
read
SortYear
Year([BookDate]))-((Year([BookDate]))
Mod 2)
After Year([BookDate] it should say 1 both times.
Evi
message
berlin.de...
Darn. Just change that first query to
SortYear: (Year([BookDate]))-((Year ([BookDate]))
Mod 2)
message
berlin.de...
What a great bedtime puzzle!
In a blank column, in the Field row, in Design
view, in the query on
which
your report is based, put this:
SortYear
Year([BookDate]))-((Year ([BookDate]))
Mod 2)
(use the real name of your date field instead
of
YourDate).
In the criteria row of your query, under the
date
field have
= DateSerial(1995,1,1) to filter for dates
after
and on the 1st of
1995.
In your report make SortYear your Group field.
Evi
in
message
[email protected]...
I want to make a report which groups on two
year intervals
minus one day, starting with a specific date.
For
example, every two years starting with
1/1/1995, which
would be 1/1/1995 -12/31/1996, 1/1/1997 -
12/31/1998,
etc. Is this possible? I am a novice at
Access and don't
know any coding, etc.
Thanks!
.
.
