biweekly groupings in access report

[Guest]

I have a table that is generated from a make table query that queries client
stay history to calculate daily occupancy. I need to generate a report that
will group these daily occupancy figures into biweekly totals and averages. I
have not yet found a method that will effectively create this type of
grouping. Please advise.

 

[Guest]

This may not be the best way but here is what I did.
Create a table
Date
Week identifyer
ex.
Date Week_Identifyer
1/1/06 1
1/2/06 1
1/3/06 1
1/4/06 1
.......
1/15/06 2
1/16/06 2
......
1/29/06 3
1/30/06 3

add a relationship between the date filed in your client table and the date
in this new table.
In your report group by week identifyer

Hint: make the date table in excel. It is quicker.

 

[Duane Hookom]

It isn't real clear what you want but you should be able to group two weeks
together using
(DatePart("ww",[DateField]) + 1)\2

 

[Guest]

I think this may be a start but let me attempt to better explain. From my
make table query I created a table that calculated daily occupancy from
01/01/2002 through current date. (The table has two fields a sequential date
field and the corresponding occupancy data) I need to report the average
daily occupancy in two week intervals.

It isn't real clear what you want but you should be able to group two weeks
together using
(DatePart("ww",[DateField]) + 1)\2

--
Duane Hookom
MS Access MVP

I have a table that is generated from a make table query that queries
client
stay history to calculate daily occupancy. I need to generate a report
that
will group these daily occupancy figures into biweekly totals and
averages. I
have not yet found a method that will effectively create this type of
grouping. Please advise.

 

[Guest]

Thanks Eric that could work and I may attempt to use it but I will need to
generate this report periodically so it may prove to be a bit troublesome;
however it will at least provide a short term solution while a more permanent
solution is researched.

Thanks much

 

[Duane Hookom]

Could you provide some field and table names? How about a few sample records
and desired output?

--
Duane Hookom
MS Access MVP

I think this may be a start but let me attempt to better explain. From my
make table query I created a table that calculated daily occupancy from
01/01/2002 through current date. (The table has two fields a sequential
date
field and the corresponding occupancy data) I need to report the average
daily occupancy in two week intervals.

It isn't real clear what you want but you should be able to group two
weeks
together using
(DatePart("ww",[DateField]) + 1)\2

--
Duane Hookom
MS Access MVP

I have a table that is generated from a make table query that queries
client
stay history to calculate daily occupancy. I need to generate a report
that
will group these daily occupancy figures into biweekly totals and
averages. I
have not yet found a method that will effectively create this type of
grouping. Please advise.

 

[Guest]

Below is a list of the first 17 days in the table. the fields are
[Census].[Days] and [Census].[DateValue]. The table has an entry for each day
from 01/01/2002 thru 05/31/2006. I am actually attempting to produce a chart
that will graph average days grouped in two week intervals 1 thru 26 with
each year being a different series. This is to compare occupancy year to year
on these two week intervals. The field [Census].[Days] is defined as total
occupancy for each date value in the table.

Days DateValue
32 1/1/2002
33 1/2/2002
29 1/3/2002
27 1/4/2002
26 1/5/2002
24 1/6/2002
23 1/7/2002
23 1/8/2002
25 1/9/2002
31 1/10/2002
32 1/11/2002
32 1/12/2002
32 1/13/2002
30 1/14/2002
32 1/15/2002
35 1/16/2002
38 1/17/2002

For instance 01/01/2002 thru 01/14/2002 aveage daily occupancy is 28.5.

I hope I have done a better job of explaining the solution I am looking for.

Could you provide some field and table names? How about a few sample records
and desired output?

--
Duane Hookom
MS Access MVP

I think this may be a start but let me attempt to better explain. From my
make table query I created a table that calculated daily occupancy from
01/01/2002 through current date. (The table has two fields a sequential
date
field and the corresponding occupancy data) I need to report the average
daily occupancy in two week intervals.

It isn't real clear what you want but you should be able to group two
weeks
together using
(DatePart("ww",[DateField]) + 1)\2

--
Duane Hookom
MS Access MVP

I have a table that is generated from a make table query that queries
client
stay history to calculate daily occupancy. I need to generate a report
that
will group these daily occupancy figures into biweekly totals and
averages. I
have not yet found a method that will effectively create this type of
grouping. Please advise.

 

[Duane Hookom]

Based on 1/1/2002 being the beginning of the year and the beginning of a
2-week period

SELECT (DatePart("ww",[DateValue],3,2)+1)\2 AS BiWeekOf,
Avg(Census.Days) AS AvgOfDays
FROM Census
GROUP BY (DatePart("ww",[DateValue],3,2)+1)\2;

BTW: DateValue is a function name so you should avoid using it as a field
name.
--
Duane Hookom
MS Access MVP

Below is a list of the first 17 days in the table. the fields are
[Census].[Days] and [Census].[DateValue]. The table has an entry for each
day
from 01/01/2002 thru 05/31/2006. I am actually attempting to produce a
chart
that will graph average days grouped in two week intervals 1 thru 26 with
each year being a different series. This is to compare occupancy year to
year
on these two week intervals. The field [Census].[Days] is defined as total
occupancy for each date value in the table.

Days DateValue
32 1/1/2002
33 1/2/2002
29 1/3/2002
27 1/4/2002
26 1/5/2002
24 1/6/2002
23 1/7/2002
23 1/8/2002
25 1/9/2002
31 1/10/2002
32 1/11/2002
32 1/12/2002
32 1/13/2002
30 1/14/2002
32 1/15/2002
35 1/16/2002
38 1/17/2002

For instance 01/01/2002 thru 01/14/2002 aveage daily occupancy is 28.5.

I hope I have done a better job of explaining the solution I am looking
for.

Could you provide some field and table names? How about a few sample
records
and desired output?

--
Duane Hookom
MS Access MVP

I think this may be a start but let me attempt to better explain. From
my
make table query I created a table that calculated daily occupancy from
01/01/2002 through current date. (The table has two fields a sequential
date
field and the corresponding occupancy data) I need to report the
average
daily occupancy in two week intervals.

:

It isn't real clear what you want but you should be able to group two
weeks
together using
(DatePart("ww",[DateField]) + 1)\2

--
Duane Hookom
MS Access MVP

I have a table that is generated from a make table query that queries
client
stay history to calculate daily occupancy. I need to generate a
report
that
will group these daily occupancy figures into biweekly totals and
averages. I
have not yet found a method that will effectively create this type
of
grouping. Please advise.

 

[Guest]

Thanks perfect

Based on 1/1/2002 being the beginning of the year and the beginning of a
2-week period

SELECT (DatePart("ww",[DateValue],3,2)+1)\2 AS BiWeekOf,
Avg(Census.Days) AS AvgOfDays
FROM Census
GROUP BY (DatePart("ww",[DateValue],3,2)+1)\2;

BTW: DateValue is a function name so you should avoid using it as a field
name.
--
Duane Hookom
MS Access MVP

Below is a list of the first 17 days in the table. the fields are
[Census].[Days] and [Census].[DateValue]. The table has an entry for each
day
from 01/01/2002 thru 05/31/2006. I am actually attempting to produce a
chart
that will graph average days grouped in two week intervals 1 thru 26 with
each year being a different series. This is to compare occupancy year to
year
on these two week intervals. The field [Census].[Days] is defined as total
occupancy for each date value in the table.

Days DateValue
32 1/1/2002
33 1/2/2002
29 1/3/2002
27 1/4/2002
26 1/5/2002
24 1/6/2002
23 1/7/2002
23 1/8/2002
25 1/9/2002
31 1/10/2002
32 1/11/2002
32 1/12/2002
32 1/13/2002
30 1/14/2002
32 1/15/2002
35 1/16/2002
38 1/17/2002

For instance 01/01/2002 thru 01/14/2002 aveage daily occupancy is 28.5.

I hope I have done a better job of explaining the solution I am looking
for.

Could you provide some field and table names? How about a few sample
records
and desired output?

--
Duane Hookom
MS Access MVP

I think this may be a start but let me attempt to better explain. From
my
make table query I created a table that calculated daily occupancy from
01/01/2002 through current date. (The table has two fields a sequential
date
field and the corresponding occupancy data) I need to report the
average
daily occupancy in two week intervals.

:

It isn't real clear what you want but you should be able to group two
weeks
together using
(DatePart("ww",[DateField]) + 1)\2

--
Duane Hookom
MS Access MVP

I have a table that is generated from a make table query that queries
client
stay history to calculate daily occupancy. I need to generate a
report
that
will group these daily occupancy figures into biweekly totals and
averages. I
have not yet found a method that will effectively create this type
of
grouping. Please advise.

 

[Guest]

Oops forgot about the issue with [DateValue] thanks for the heads up

Based on 1/1/2002 being the beginning of the year and the beginning of a
2-week period

SELECT (DatePart("ww",[DateValue],3,2)+1)\2 AS BiWeekOf,
Avg(Census.Days) AS AvgOfDays
FROM Census
GROUP BY (DatePart("ww",[DateValue],3,2)+1)\2;

BTW: DateValue is a function name so you should avoid using it as a field
name.
--
Duane Hookom
MS Access MVP

Below is a list of the first 17 days in the table. the fields are
[Census].[Days] and [Census].[DateValue]. The table has an entry for each
day
from 01/01/2002 thru 05/31/2006. I am actually attempting to produce a
chart
that will graph average days grouped in two week intervals 1 thru 26 with
each year being a different series. This is to compare occupancy year to
year
on these two week intervals. The field [Census].[Days] is defined as total
occupancy for each date value in the table.

Days DateValue
32 1/1/2002
33 1/2/2002
29 1/3/2002
27 1/4/2002
26 1/5/2002
24 1/6/2002
23 1/7/2002
23 1/8/2002
25 1/9/2002
31 1/10/2002
32 1/11/2002
32 1/12/2002
32 1/13/2002
30 1/14/2002
32 1/15/2002
35 1/16/2002
38 1/17/2002

For instance 01/01/2002 thru 01/14/2002 aveage daily occupancy is 28.5.

I hope I have done a better job of explaining the solution I am looking
for.

Could you provide some field and table names? How about a few sample
records
and desired output?

--
Duane Hookom
MS Access MVP

I think this may be a start but let me attempt to better explain. From
my
make table query I created a table that calculated daily occupancy from
01/01/2002 through current date. (The table has two fields a sequential
date
field and the corresponding occupancy data) I need to report the
average
daily occupancy in two week intervals.

:

It isn't real clear what you want but you should be able to group two
weeks
together using
(DatePart("ww",[DateField]) + 1)\2

--
Duane Hookom
MS Access MVP

I have a table that is generated from a make table query that queries
client
stay history to calculate daily occupancy. I need to generate a
report
that
will group these daily occupancy figures into biweekly totals and
averages. I
have not yet found a method that will effectively create this type
of
grouping. Please advise.