Grouping intervals in report

[Susan]

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!

 

[Evi]

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:

SortYearYear([BookDate])+1)-((Year([BookDate])+1) 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

 

[Evi]

Darn. Just change that first query to
SortYear: (Year([BookDate])+1)-((Year([BookDate])+1) Mod 2)

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:

SortYearYear([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

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!

 

[Evi]

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
SortYearYear([BookDate])+1)-((Year([BookDate])+1) Mod 2)

After Year([BookDate] it should say + 1 both times.

Evi

Darn. Just change that first query to
SortYear: (Year([BookDate]))-((Year([BookDate])) Mod 2)

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:

SortYearYear([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

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!

 

[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

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
SortYearYear([BookDate]))-((Year([BookDate])) Mod 2)

After Year([BookDate] it should say 1 both times.

Evi

Darn. Just change that first query to
SortYear: (Year([BookDate]))-((Year([BookDate])) Mod 2)

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:

SortYearYear([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



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!

 

[Evi]

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

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
SortYearYear([BookDate]))-((Year([BookDate])) Mod 2)

After Year([BookDate] it should say 1 both times.

Evi

Darn. Just change that first query to
SortYear: (Year([BookDate]))-((Year([BookDate])) Mod 2)


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:

SortYearYear([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



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!

 

[Susan]

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

Darn late nights! that's twice I thought I'd pasted

in the correct
answer one. It should
read

SortYearYear([BookDate]))-((Year([BookDate])) Mod 2)

After Year([BookDate] it should say 1 both times.

Evi

Darn. Just change that first query to
SortYear: (Year([BookDate]))-((Year([BookDate])) Mod 2)


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:

SortYearYear([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



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!

.

 

[Evi]

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


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

SortYearYear([BookDate]))-((Year([BookDate])) Mod 2)

After Year([BookDate] it should say 1 both times.

Evi

Darn. Just change that first query to
SortYear: (Year([BookDate]))-((Year([BookDate])) Mod 2)


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:

SortYearYear([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



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!

.

 

[Susan]

Thanks again...it worked like a champ!

Susan

-----Original Message-----
I've put this into a custom function which I've called PeriodGroup. The way
to use it, is to paste it into a module in your database.

In any query, form or report you can now type

PeriodGroup(YourHireDateField,YourEventDateField) and you will get a number
you can group by.

So in your query you would put, depending on your actual field names:

MyGrp: PeriodGroup(HireDate,TrainingDate)

Here's the function:

Function PeriodGroup(dtStartDate As Date, dtEventDate As Date) As Integer
'Put Event Dates into 2 year blocks starting with the dtStartDate
Dim PeriodYr As Integer
Dim PeriodEnd As Date

PeriodYr = Year(dtEventDate) - Year(dtStartDate)

PeriodYr = PeriodYr + (PeriodYr Mod 2)
PeriodYr = Year(dtStartDate) + PeriodYr

PeriodEnd = DateSerial(PeriodYr, Month(dtStartDate), Day (dtStartDate) - 1)
If PeriodEnd < dtEventDate Then
'ie the anniversary of the event date
'is after the anniversary of the dtStartDate
'so it belongs to the next block of 2
PeriodYr = PeriodYr + 2
End If

PeriodGroup = (PeriodYr - Year(dtStartDate)) / 2

End Function


Let's hope this does it.

Evi

Whoops...one thing I just noticed. It groups by YEAR, not
the DATE. In other words, it doesn't cut off at the proper
month and date. If the hire date is 1/18/1993, the cutoff
date for the 2 year block should be 1/17/1995. If I have
a training on 1/23/1995, it puts it in the first two year
block, whereas it should actually go into the NEW 2 year
block. I guess there's a tweak required somewhere...I
tried all sorts of things, but being an Access dummy, I
came up empty! Otherwise, it works great!

-----Original Message-----
Phew Thanks for letting me know.
Evi

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


berlin.de...
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
SortYearYear([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)


"Evi" <[email protected]>

wrote
in

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:

SortYearYear([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
news:04af01c3c5a7$d3343450

[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!










.



.



.

.