Cross Tab query date format and sort order

[Guest]

Hi, I have a crosstab query which has the date as column headings. If in
short date format the order is displayed correctly. The problem is I want it
to also show the day of the week and when I tried adding this the sort order
was incorrect. I used Format([ReviewDate],"d mmm"", ""dddd") but Access
sorts the date 10 before 06. Is it possible to have the day displayed without
affecting the date sort order?

Thanks in advance for any help.
Sue

 

[Duane Hookom]

Did you try Format([ReviewDate],"dd mmm"", ""dddd") ?
The only way you can set the column order other than alphabetic is to enter
values into the column headings property.

Normally records are displayed in forms or reports where you can move
columns as you like.

 

[Guest]

Thanks that worked, should have thought to try that - doh!

Actually, your 2nd point brings me on to my next problem. I want to be able
to display this in a report rather than have to run as a query, but the date
range will differ each time. The user is prompted to enter a start and end
date, if I create a report based on the crosstab query it fixes the column
heading with the dates in question but then the next time it is run with
different dates it will not work or am I doing something wrong?

Did you try Format([ReviewDate],"dd mmm"", ""dddd") ?
The only way you can set the column order other than alphabetic is to enter
values into the column headings property.

Normally records are displayed in forms or reports where you can move
columns as you like.

--
Duane Hookom
MS Access MVP
--

Hi, I have a crosstab query which has the date as column headings. If in
short date format the order is displayed correctly. The problem is I want
it
to also show the day of the week and when I tried adding this the sort
order
was incorrect. I used Format([ReviewDate],"d mmm"", ""dddd") but Access
sorts the date 10 before 06. Is it possible to have the day displayed
without
affecting the date sort order?

Thanks in advance for any help.
Sue

 

[Duane Hookom]

I would use relative dates and set up the report for a fixed number of
dates. The users would need to only enter the beginning or ending date.
Search Google or GoogleGroups on:
"relative dates" hookom crosstab Mth0
for some solutions. Come back if you have questions.

--
Duane Hookom
MS Access MVP
--

Thanks that worked, should have thought to try that - doh!

Actually, your 2nd point brings me on to my next problem. I want to be
able
to display this in a report rather than have to run as a query, but the
date
range will differ each time. The user is prompted to enter a start and end
date, if I create a report based on the crosstab query it fixes the column
heading with the dates in question but then the next time it is run with
different dates it will not work or am I doing something wrong?

Did you try Format([ReviewDate],"dd mmm"", ""dddd") ?
The only way you can set the column order other than alphabetic is to
enter
values into the column headings property.

Normally records are displayed in forms or reports where you can move
columns as you like.

--
Duane Hookom
MS Access MVP
--

Hi, I have a crosstab query which has the date as column headings. If
in
short date format the order is displayed correctly. The problem is I
want
it
to also show the day of the week and when I tried adding this the sort
order
was incorrect. I used Format([ReviewDate],"d mmm"", ""dddd") but
Access
sorts the date 10 before 06. Is it possible to have the day displayed
without
affecting the date sort order?

Thanks in advance for any help.
Sue

 

[Guest]

Thanks Duane this worked great! I have managed to get a report with relative
dates for a 5 day period using just a start date. I also used Stephen Lebans
PrintLines code to make the report easier to read - thanks a million guys!!!

One other query you may be able to answer for me please? -
Currently some of the dates are blank where there is no entry for a person,
this could be because an activity lasts for up to five days and the record is
only entered once against the start date on the system. I do not know how to
get the activity to show on all dates - I have asked this in a seperate post
(twice now) with no replies as yet :-(.

In the meantime, I am thinking of exporting the report to Word so our cleric
can fill in the blanks manually. If I do this using the transfer to word
command the data is transferred but obviously no lines and then it is hard to
read and edit the document. I then thought of creating a template and using
mail merge from access but I'm not sure if this can be done from using a
crosstab query? Can you help with this at all????

Thanks in advance for any help.
Sue

I would use relative dates and set up the report for a fixed number of
dates. The users would need to only enter the beginning or ending date.
Search Google or GoogleGroups on:
"relative dates" hookom crosstab Mth0
for some solutions. Come back if you have questions.

--
Duane Hookom
MS Access MVP
--

Thanks that worked, should have thought to try that - doh!

Actually, your 2nd point brings me on to my next problem. I want to be
able
to display this in a report rather than have to run as a query, but the
date
range will differ each time. The user is prompted to enter a start and end
date, if I create a report based on the crosstab query it fixes the column
heading with the dates in question but then the next time it is run with
different dates it will not work or am I doing something wrong?

Did you try Format([ReviewDate],"dd mmm"", ""dddd") ?
The only way you can set the column order other than alphabetic is to
enter
values into the column headings property.

Normally records are displayed in forms or reports where you can move
columns as you like.

--
Duane Hookom
MS Access MVP
--

Hi, I have a crosstab query which has the date as column headings. If
in
short date format the order is displayed correctly. The problem is I
want
it
to also show the day of the week and when I tried adding this the sort
order
was incorrect. I used Format([ReviewDate],"d mmm"", ""dddd") but
Access
sorts the date 10 before 06. Is it possible to have the day displayed
without
affecting the date sort order?

Thanks in advance for any help.
Sue

 

[Duane Hookom]

To get a record to show for every date consider creating a table of dates
[tblDates] and a field [TheDate] and records for all possible dates in your
ranges. You can build this table easily by creating in Excel and pasting
into tblDates.

Then set up a query with your current table and tblDates. Don't join these
tables. Add a criteria under the TheDate field to:
Between [StartDateField] And [EndDateField]

This will create five virtual records for a single record in your original
table where the date range spans five days.

--
Duane Hookom
MS Access MVP

Thanks Duane this worked great! I have managed to get a report with
relative
dates for a 5 day period using just a start date. I also used Stephen
Lebans
PrintLines code to make the report easier to read - thanks a million
guys!!!

One other query you may be able to answer for me please? -
Currently some of the dates are blank where there is no entry for a
person,
this could be because an activity lasts for up to five days and the record
is
only entered once against the start date on the system. I do not know how
to
get the activity to show on all dates - I have asked this in a seperate
post
(twice now) with no replies as yet :-(.

In the meantime, I am thinking of exporting the report to Word so our
cleric
can fill in the blanks manually. If I do this using the transfer to word
command the data is transferred but obviously no lines and then it is hard
to
read and edit the document. I then thought of creating a template and
using
mail merge from access but I'm not sure if this can be done from using a
crosstab query? Can you help with this at all????

Thanks in advance for any help.
Sue

I would use relative dates and set up the report for a fixed number of
dates. The users would need to only enter the beginning or ending date.
Search Google or GoogleGroups on:
"relative dates" hookom crosstab Mth0
for some solutions. Come back if you have questions.

--
Duane Hookom
MS Access MVP
--

Thanks that worked, should have thought to try that - doh!

Actually, your 2nd point brings me on to my next problem. I want to be
able
to display this in a report rather than have to run as a query, but the
date
range will differ each time. The user is prompted to enter a start and
end
date, if I create a report based on the crosstab query it fixes the
column
heading with the dates in question but then the next time it is run
with
different dates it will not work or am I doing something wrong?

:

Did you try Format([ReviewDate],"dd mmm"", ""dddd") ?
The only way you can set the column order other than alphabetic is to
enter
values into the column headings property.

Normally records are displayed in forms or reports where you can move
columns as you like.

--
Duane Hookom
MS Access MVP
--

Hi, I have a crosstab query which has the date as column headings.
If
in
short date format the order is displayed correctly. The problem is I
want
it
to also show the day of the week and when I tried adding this the
sort
order
was incorrect. I used Format([ReviewDate],"d mmm"", ""dddd") but
Access
sorts the date 10 before 06. Is it possible to have the day
displayed
without
affecting the date sort order?

Thanks in advance for any help.
Sue

 

[Guest]

Thanks for the reply Duane. Could you clarify this a little more please,
sorry! Do you mean create this table on the fly just for the one week period
(ie 5 date records at a time) when I run the itinerary report OR create it to
hold all dates say in a year?
Is the temp table [tblDates] just to have one field [TheDate] ?

Not sure how this would work really - I only have a [ReviewDate] in my
original table and not start and end dates although I could calculate the end
date from the field [ReviewDays]. This holds the number of days the activity
lasts for (usually between 1 and 5). I will try this and come back if I have
further questions... thanks again.

Sue

To get a record to show for every date consider creating a table of dates
[tblDates] and a field [TheDate] and records for all possible dates in your
ranges. You can build this table easily by creating in Excel and pasting
into tblDates.

Then set up a query with your current table and tblDates. Don't join these
tables. Add a criteria under the TheDate field to:
Between [StartDateField] And [EndDateField]

This will create five virtual records for a single record in your original
table where the date range spans five days.

--
Duane Hookom
MS Access MVP

Thanks Duane this worked great! I have managed to get a report with
relative
dates for a 5 day period using just a start date. I also used Stephen
Lebans
PrintLines code to make the report easier to read - thanks a million
guys!!!

One other query you may be able to answer for me please? -
Currently some of the dates are blank where there is no entry for a
person,
this could be because an activity lasts for up to five days and the record
is
only entered once against the start date on the system. I do not know how
to
get the activity to show on all dates - I have asked this in a seperate
post
(twice now) with no replies as yet :-(.

In the meantime, I am thinking of exporting the report to Word so our
cleric
can fill in the blanks manually. If I do this using the transfer to word
command the data is transferred but obviously no lines and then it is hard
to
read and edit the document. I then thought of creating a template and
using
mail merge from access but I'm not sure if this can be done from using a
crosstab query? Can you help with this at all????

Thanks in advance for any help.
Sue

I would use relative dates and set up the report for a fixed number of
dates. The users would need to only enter the beginning or ending date.
Search Google or GoogleGroups on:
"relative dates" hookom crosstab Mth0
for some solutions. Come back if you have questions.

--
Duane Hookom
MS Access MVP
--

Thanks that worked, should have thought to try that - doh!

Actually, your 2nd point brings me on to my next problem. I want to be
able
to display this in a report rather than have to run as a query, but the
date
range will differ each time. The user is prompted to enter a start and
end
date, if I create a report based on the crosstab query it fixes the
column
heading with the dates in question but then the next time it is run
with
different dates it will not work or am I doing something wrong?

:

Did you try Format([ReviewDate],"dd mmm"", ""dddd") ?
The only way you can set the column order other than alphabetic is to
enter
values into the column headings property.

Normally records are displayed in forms or reports where you can move
columns as you like.

--
Duane Hookom
MS Access MVP
--

Hi, I have a crosstab query which has the date as column headings.
If
in
short date format the order is displayed correctly. The problem is I
want
it
to also show the day of the week and when I tried adding this the
sort
order
was incorrect. I used Format([ReviewDate],"d mmm"", ""dddd") but
Access
sorts the date 10 before 06. Is it possible to have the day
displayed
without
affecting the date sort order?

Thanks in advance for any help.
Sue

 

[Duane Hookom]

Create a permanent lookup table [tblDates] with a single date field
[TheDate] and all one record for each date.
Your criteria would be something like:
TheDate Between ReviewDate and ReviewDate + ReviewDays

--
Duane Hookom
MS Access MVP

Thanks for the reply Duane. Could you clarify this a little more please,
sorry! Do you mean create this table on the fly just for the one week
period
(ie 5 date records at a time) when I run the itinerary report OR create it
to
hold all dates say in a year?
Is the temp table [tblDates] just to have one field [TheDate] ?

Not sure how this would work really - I only have a [ReviewDate] in my
original table and not start and end dates although I could calculate the
end
date from the field [ReviewDays]. This holds the number of days the
activity
lasts for (usually between 1 and 5). I will try this and come back if I
have
further questions... thanks again.

Sue

To get a record to show for every date consider creating a table of dates
[tblDates] and a field [TheDate] and records for all possible dates in
your
ranges. You can build this table easily by creating in Excel and pasting
into tblDates.

Then set up a query with your current table and tblDates. Don't join
these
tables. Add a criteria under the TheDate field to:
Between [StartDateField] And [EndDateField]

This will create five virtual records for a single record in your
original
table where the date range spans five days.

--
Duane Hookom
MS Access MVP

Thanks Duane this worked great! I have managed to get a report with
relative
dates for a 5 day period using just a start date. I also used Stephen
Lebans
PrintLines code to make the report easier to read - thanks a million
guys!!!

One other query you may be able to answer for me please? -
Currently some of the dates are blank where there is no entry for a
person,
this could be because an activity lasts for up to five days and the
record
is
only entered once against the start date on the system. I do not know
how
to
get the activity to show on all dates - I have asked this in a seperate
post
(twice now) with no replies as yet :-(.

In the meantime, I am thinking of exporting the report to Word so our
cleric
can fill in the blanks manually. If I do this using the transfer to
word
command the data is transferred but obviously no lines and then it is
hard
to
read and edit the document. I then thought of creating a template and
using
mail merge from access but I'm not sure if this can be done from using
a
crosstab query? Can you help with this at all????

Thanks in advance for any help.
Sue


:

I would use relative dates and set up the report for a fixed number of
dates. The users would need to only enter the beginning or ending
date.
Search Google or GoogleGroups on:
"relative dates" hookom crosstab Mth0
for some solutions. Come back if you have questions.

--
Duane Hookom
MS Access MVP
--

Thanks that worked, should have thought to try that - doh!

Actually, your 2nd point brings me on to my next problem. I want to
be
able
to display this in a report rather than have to run as a query, but
the
date
range will differ each time. The user is prompted to enter a start
and
end
date, if I create a report based on the crosstab query it fixes the
column
heading with the dates in question but then the next time it is run
with
different dates it will not work or am I doing something wrong?

:

Did you try Format([ReviewDate],"dd mmm"", ""dddd") ?
The only way you can set the column order other than alphabetic is
to
enter
values into the column headings property.

Normally records are displayed in forms or reports where you can
move
columns as you like.

--
Duane Hookom
MS Access MVP
--

Hi, I have a crosstab query which has the date as column
headings.
If
in
short date format the order is displayed correctly. The problem
is I
want
it
to also show the day of the week and when I tried adding this the
sort
order
was incorrect. I used Format([ReviewDate],"d mmm"", ""dddd") but
Access
sorts the date 10 before 06. Is it possible to have the day
displayed
without
affecting the date sort order?

Thanks in advance for any help.
Sue

 

[Guest]

I created a Dates table and query with criteria as you instructed. I then
used this query as the basis of my crosstab query with relative dates using
startdate, but it seems to only produce the data where the activity lasts
more than 1 day? One other problem I have is that audits can start and finish
midweek but I still need to see the activity on the weekly itinerary. Also,
occassionally they will do Mon+Tues+Thurs+Frid audit with a different
activity on the wed so the system can not calculate this simply using review
days.

I am beginning to think I need to seperate the itinerary data from the audit
data somehow and have the activity repeated in data entry against each date
but not sure how I will manage the split. Concerned about normalisation etc
too.

Is there any other way you can think of before I try this - the data is
generated in the system via a rather complicated procedure. Using an append
query from a table so I am not sure how I would get it to write to two
different relational tables. It would also be a shame when I have got so
close to have to redesign it....

Sue

Create a permanent lookup table [tblDates] with a single date field
[TheDate] and all one record for each date.
Your criteria would be something like:
TheDate Between ReviewDate and ReviewDate + ReviewDays

--
Duane Hookom
MS Access MVP

Thanks for the reply Duane. Could you clarify this a little more please,
sorry! Do you mean create this table on the fly just for the one week
period
(ie 5 date records at a time) when I run the itinerary report OR create it
to
hold all dates say in a year?
Is the temp table [tblDates] just to have one field [TheDate] ?

Not sure how this would work really - I only have a [ReviewDate] in my
original table and not start and end dates although I could calculate the
end
date from the field [ReviewDays]. This holds the number of days the
activity
lasts for (usually between 1 and 5). I will try this and come back if I
have
further questions... thanks again.

Sue

To get a record to show for every date consider creating a table of dates
[tblDates] and a field [TheDate] and records for all possible dates in
your
ranges. You can build this table easily by creating in Excel and pasting
into tblDates.

Then set up a query with your current table and tblDates. Don't join
these
tables. Add a criteria under the TheDate field to:
Between [StartDateField] And [EndDateField]

This will create five virtual records for a single record in your
original
table where the date range spans five days.

--
Duane Hookom
MS Access MVP


Thanks Duane this worked great! I have managed to get a report with
relative
dates for a 5 day period using just a start date. I also used Stephen
Lebans
PrintLines code to make the report easier to read - thanks a million
guys!!!

One other query you may be able to answer for me please? -
Currently some of the dates are blank where there is no entry for a
person,
this could be because an activity lasts for up to five days and the
record
is
only entered once against the start date on the system. I do not know
how
to
get the activity to show on all dates - I have asked this in a seperate
post
(twice now) with no replies as yet :-(.

In the meantime, I am thinking of exporting the report to Word so our
cleric
can fill in the blanks manually. If I do this using the transfer to
word
command the data is transferred but obviously no lines and then it is
hard
to
read and edit the document. I then thought of creating a template and
using
mail merge from access but I'm not sure if this can be done from using
a
crosstab query? Can you help with this at all????

Thanks in advance for any help.
Sue


:

I would use relative dates and set up the report for a fixed number of
dates. The users would need to only enter the beginning or ending
date.
Search Google or GoogleGroups on:
"relative dates" hookom crosstab Mth0
for some solutions. Come back if you have questions.

--
Duane Hookom
MS Access MVP
--

Thanks that worked, should have thought to try that - doh!

Actually, your 2nd point brings me on to my next problem. I want to
be
able
to display this in a report rather than have to run as a query, but
the
date
range will differ each time. The user is prompted to enter a start
and
end
date, if I create a report based on the crosstab query it fixes the
column
heading with the dates in question but then the next time it is run
with
different dates it will not work or am I doing something wrong?

:

Did you try Format([ReviewDate],"dd mmm"", ""dddd") ?
The only way you can set the column order other than alphabetic is
to
enter
values into the column headings property.

Normally records are displayed in forms or reports where you can
move
columns as you like.

--
Duane Hookom
MS Access MVP
--

Hi, I have a crosstab query which has the date as column
headings.
If
in
short date format the order is displayed correctly. The problem
is I
want
it
to also show the day of the week and when I tried adding this the
sort
order
was incorrect. I used Format([ReviewDate],"d mmm"", ""dddd") but
Access
sorts the date 10 before 06. Is it possible to have the day
displayed
without
affecting the date sort order?

Thanks in advance for any help.
Sue

 

[Guest]

Worked out why other activities eg holidays etc were not showing, they didn't
have a value in reviewdays, only audit records used this field. Defaulted all
to '1' and now the crosstab report shows all activities .

The outstanding problems I have are :-
1. A mid week split audit, review days does not exclude saturday and sunday
so no results is displayed on the monday etc.
2. A split audit where the days are not consecutive,

In other words I need to skip over any day where an activity does actually
exist in table or it is a Sat/Sunday. I assume this can't be achieved without
code?

Thanks
Sue


--
Thanks in advance for any help.
Sue

I created a Dates table and query with criteria as you instructed. I then
used this query as the basis of my crosstab query with relative dates using
startdate, but it seems to only produce the data where the activity lasts
more than 1 day? One other problem I have is that audits can start and finish
midweek but I still need to see the activity on the weekly itinerary. Also,
occassionally they will do Mon+Tues+Thurs+Frid audit with a different
activity on the wed so the system can not calculate this simply using review
days.

I am beginning to think I need to seperate the itinerary data from the audit
data somehow and have the activity repeated in data entry against each date
but not sure how I will manage the split. Concerned about normalisation etc
too.

Is there any other way you can think of before I try this - the data is
generated in the system via a rather complicated procedure. Using an append
query from a table so I am not sure how I would get it to write to two
different relational tables. It would also be a shame when I have got so
close to have to redesign it....

Sue

Create a permanent lookup table [tblDates] with a single date field
[TheDate] and all one record for each date.
Your criteria would be something like:
TheDate Between ReviewDate and ReviewDate + ReviewDays

--
Duane Hookom
MS Access MVP

Thanks for the reply Duane. Could you clarify this a little more please,
sorry! Do you mean create this table on the fly just for the one week
period
(ie 5 date records at a time) when I run the itinerary report OR create it
to
hold all dates say in a year?
Is the temp table [tblDates] just to have one field [TheDate] ?

Not sure how this would work really - I only have a [ReviewDate] in my
original table and not start and end dates although I could calculate the
end
date from the field [ReviewDays]. This holds the number of days the
activity
lasts for (usually between 1 and 5). I will try this and come back if I
have
further questions... thanks again.

Sue

:

To get a record to show for every date consider creating a table of dates
[tblDates] and a field [TheDate] and records for all possible dates in
your
ranges. You can build this table easily by creating in Excel and pasting
into tblDates.

Then set up a query with your current table and tblDates. Don't join
these
tables. Add a criteria under the TheDate field to:
Between [StartDateField] And [EndDateField]

This will create five virtual records for a single record in your
original
table where the date range spans five days.

--
Duane Hookom
MS Access MVP


Thanks Duane this worked great! I have managed to get a report with
relative
dates for a 5 day period using just a start date. I also used Stephen
Lebans
PrintLines code to make the report easier to read - thanks a million
guys!!!

One other query you may be able to answer for me please? -
Currently some of the dates are blank where there is no entry for a
person,
this could be because an activity lasts for up to five days and the
record
is
only entered once against the start date on the system. I do not know
how
to
get the activity to show on all dates - I have asked this in a seperate
post
(twice now) with no replies as yet :-(.

In the meantime, I am thinking of exporting the report to Word so our
cleric
can fill in the blanks manually. If I do this using the transfer to
word
command the data is transferred but obviously no lines and then it is
hard
to
read and edit the document. I then thought of creating a template and
using
mail merge from access but I'm not sure if this can be done from using
a
crosstab query? Can you help with this at all????

Thanks in advance for any help.
Sue


:

I would use relative dates and set up the report for a fixed number of
dates. The users would need to only enter the beginning or ending
date.
Search Google or GoogleGroups on:
"relative dates" hookom crosstab Mth0
for some solutions. Come back if you have questions.

--
Duane Hookom
MS Access MVP
--

Thanks that worked, should have thought to try that - doh!

Actually, your 2nd point brings me on to my next problem. I want to
be
able
to display this in a report rather than have to run as a query, but
the
date
range will differ each time. The user is prompted to enter a start
and
end
date, if I create a report based on the crosstab query it fixes the
column
heading with the dates in question but then the next time it is run
with
different dates it will not work or am I doing something wrong?

:

Did you try Format([ReviewDate],"dd mmm"", ""dddd") ?
The only way you can set the column order other than alphabetic is
to
enter
values into the column headings property.

Normally records are displayed in forms or reports where you can
move
columns as you like.

--
Duane Hookom
MS Access MVP
--

Hi, I have a crosstab query which has the date as column
headings.
If
in
short date format the order is displayed correctly. The problem
is I
want
it
to also show the day of the week and when I tried adding this the
sort
order
was incorrect. I used Format([ReviewDate],"d mmm"", ""dddd") but
Access
sorts the date 10 before 06. Is it possible to have the day
displayed
without
affecting the date sort order?

Thanks in advance for any help.
Sue

 

[Duane Hookom]

There have been some threads in the past that handle "workdays". You might
want to search google groups.

I'm not sure how you handle split audits.

--
Duane Hookom
MS Access MVP
--

Worked out why other activities eg holidays etc were not showing, they
didn't
have a value in reviewdays, only audit records used this field. Defaulted
all
to '1' and now the crosstab report shows all activities .

The outstanding problems I have are :-
1. A mid week split audit, review days does not exclude saturday and
sunday
so no results is displayed on the monday etc.
2. A split audit where the days are not consecutive,

In other words I need to skip over any day where an activity does actually
exist in table or it is a Sat/Sunday. I assume this can't be achieved
without
code?

Thanks
Sue


--
Thanks in advance for any help.
Sue

I created a Dates table and query with criteria as you instructed. I then
used this query as the basis of my crosstab query with relative dates
using
startdate, but it seems to only produce the data where the activity lasts
more than 1 day? One other problem I have is that audits can start and
finish
midweek but I still need to see the activity on the weekly itinerary.
Also,
occassionally they will do Mon+Tues+Thurs+Frid audit with a different
activity on the wed so the system can not calculate this simply using
review
days.

I am beginning to think I need to seperate the itinerary data from the
audit
data somehow and have the activity repeated in data entry against each
date
but not sure how I will manage the split. Concerned about normalisation
etc
too.

Is there any other way you can think of before I try this - the data is
generated in the system via a rather complicated procedure. Using an
append
query from a table so I am not sure how I would get it to write to two
different relational tables. It would also be a shame when I have got so
close to have to redesign it....

Sue

Create a permanent lookup table [tblDates] with a single date field
[TheDate] and all one record for each date.
Your criteria would be something like:
TheDate Between ReviewDate and ReviewDate + ReviewDays

--
Duane Hookom
MS Access MVP


Thanks for the reply Duane. Could you clarify this a little more
please,
sorry! Do you mean create this table on the fly just for the one week
period
(ie 5 date records at a time) when I run the itinerary report OR
create it
to
hold all dates say in a year?
Is the temp table [tblDates] just to have one field [TheDate] ?

Not sure how this would work really - I only have a [ReviewDate] in
my
original table and not start and end dates although I could calculate
the
end
date from the field [ReviewDays]. This holds the number of days the
activity
lasts for (usually between 1 and 5). I will try this and come back if
I
have
further questions... thanks again.

Sue

:

To get a record to show for every date consider creating a table of
dates
[tblDates] and a field [TheDate] and records for all possible dates
in
your
ranges. You can build this table easily by creating in Excel and
pasting
into tblDates.

Then set up a query with your current table and tblDates. Don't join
these
tables. Add a criteria under the TheDate field to:
Between [StartDateField] And [EndDateField]

This will create five virtual records for a single record in your
original
table where the date range spans five days.

--
Duane Hookom
MS Access MVP


Thanks Duane this worked great! I have managed to get a report
with
relative
dates for a 5 day period using just a start date. I also used
Stephen
Lebans
PrintLines code to make the report easier to read - thanks a
million
guys!!!

One other query you may be able to answer for me please? -
Currently some of the dates are blank where there is no entry for
a
person,
this could be because an activity lasts for up to five days and
the
record
is
only entered once against the start date on the system. I do not
know
how
to
get the activity to show on all dates - I have asked this in a
seperate
post
(twice now) with no replies as yet :-(.

In the meantime, I am thinking of exporting the report to Word so
our
cleric
can fill in the blanks manually. If I do this using the transfer
to
word
command the data is transferred but obviously no lines and then it
is
hard
to
read and edit the document. I then thought of creating a template
and
using
mail merge from access but I'm not sure if this can be done from
using
a
crosstab query? Can you help with this at all????

Thanks in advance for any help.
Sue


:

I would use relative dates and set up the report for a fixed
number of
dates. The users would need to only enter the beginning or ending
date.
Search Google or GoogleGroups on:
"relative dates" hookom crosstab Mth0
for some solutions. Come back if you have questions.

--
Duane Hookom
MS Access MVP
--

Thanks that worked, should have thought to try that - doh!

Actually, your 2nd point brings me on to my next problem. I
want to
be
able
to display this in a report rather than have to run as a query,
but
the
date
range will differ each time. The user is prompted to enter a
start
and
end
date, if I create a report based on the crosstab query it fixes
the
column
heading with the dates in question but then the next time it is
run
with
different dates it will not work or am I doing something wrong?

:

Did you try Format([ReviewDate],"dd mmm"", ""dddd") ?
The only way you can set the column order other than
alphabetic is
to
enter
values into the column headings property.

Normally records are displayed in forms or reports where you
can
move
columns as you like.

--
Duane Hookom
MS Access MVP
--

message
Hi, I have a crosstab query which has the date as column
headings.
If
in
short date format the order is displayed correctly. The
problem
is I
want
it
to also show the day of the week and when I tried adding
this the
sort
order
was incorrect. I used Format([ReviewDate],"d mmm"", ""dddd")
but
Access
sorts the date 10 before 06. Is it possible to have the day
displayed
without
affecting the date sort order?

Thanks in advance for any help.
Sue

 

[Guest]

Thanks, I have used 'work days' code before after searching on the newsgroups
etc - my problem is how to do this from code with a cross tab type of query
though. When I have used it before it has been from opening a recordset based
on a table and stepping through one record at a time. My coding is not up to
this, I will have another search but doubt I can find anything to help. I
think I will end up using what I have, writing the results to a temporary
table and editing this for the weekly itinerary to correct any discrepancies
caused by split audits.

Thanks anyway...

There have been some threads in the past that handle "workdays". You might
want to search google groups.

I'm not sure how you handle split audits.

--
Duane Hookom
MS Access MVP
--

Worked out why other activities eg holidays etc were not showing, they
didn't
have a value in reviewdays, only audit records used this field. Defaulted
all
to '1' and now the crosstab report shows all activities .

The outstanding problems I have are :-
1. A mid week split audit, review days does not exclude saturday and
sunday
so no results is displayed on the monday etc.
2. A split audit where the days are not consecutive,

In other words I need to skip over any day where an activity does actually
exist in table or it is a Sat/Sunday. I assume this can't be achieved
without
code?

Thanks
Sue


--
Thanks in advance for any help.
Sue

I created a Dates table and query with criteria as you instructed. I then
used this query as the basis of my crosstab query with relative dates
using
startdate, but it seems to only produce the data where the activity lasts
more than 1 day? One other problem I have is that audits can start and
finish
midweek but I still need to see the activity on the weekly itinerary.
Also,
occassionally they will do Mon+Tues+Thurs+Frid audit with a different
activity on the wed so the system can not calculate this simply using
review
days.

I am beginning to think I need to seperate the itinerary data from the
audit
data somehow and have the activity repeated in data entry against each
date
but not sure how I will manage the split. Concerned about normalisation
etc
too.

Is there any other way you can think of before I try this - the data is
generated in the system via a rather complicated procedure. Using an
append
query from a table so I am not sure how I would get it to write to two
different relational tables. It would also be a shame when I have got so
close to have to redesign it....

Sue

Diane Hookom" wrote:

Create a permanent lookup table [tblDates] with a single date field
[TheDate] and all one record for each date.
Your criteria would be something like:
TheDate Between ReviewDate and ReviewDate + ReviewDays

--
Duane Hookom
MS Access MVP


Thanks for the reply Duane. Could you clarify this a little more
please,
sorry! Do you mean create this table on the fly just for the one week
period
(ie 5 date records at a time) when I run the itinerary report OR
create it
to
hold all dates say in a year?
Is the temp table [tblDates] just to have one field [TheDate] ?

Not sure how this would work really - I only have a [ReviewDate] in
my
original table and not start and end dates although I could calculate
the
end
date from the field [ReviewDays]. This holds the number of days the
activity
lasts for (usually between 1 and 5). I will try this and come back if
I
have
further questions... thanks again.

Sue

:

To get a record to show for every date consider creating a table of
dates
[tblDates] and a field [TheDate] and records for all possible dates
in
your
ranges. You can build this table easily by creating in Excel and
pasting
into tblDates.

Then set up a query with your current table and tblDates. Don't join
these
tables. Add a criteria under the TheDate field to:
Between [StartDateField] And [EndDateField]

This will create five virtual records for a single record in your
original
table where the date range spans five days.

--
Duane Hookom
MS Access MVP


Thanks Duane this worked great! I have managed to get a report
with
relative
dates for a 5 day period using just a start date. I also used
Stephen
Lebans
PrintLines code to make the report easier to read - thanks a
million
guys!!!

One other query you may be able to answer for me please? -
Currently some of the dates are blank where there is no entry for
a
person,
this could be because an activity lasts for up to five days and
the
record
is
only entered once against the start date on the system. I do not
know
how
to
get the activity to show on all dates - I have asked this in a
seperate
post
(twice now) with no replies as yet :-(.

In the meantime, I am thinking of exporting the report to Word so
our
cleric
can fill in the blanks manually. If I do this using the transfer
to
word
command the data is transferred but obviously no lines and then it
is
hard
to
read and edit the document. I then thought of creating a template
and
using
mail merge from access but I'm not sure if this can be done from
using
a
crosstab query? Can you help with this at all????

Thanks in advance for any help.
Sue


:

I would use relative dates and set up the report for a fixed
number of
dates. The users would need to only enter the beginning or ending
date.
Search Google or GoogleGroups on:
"relative dates" hookom crosstab Mth0
for some solutions. Come back if you have questions.

--
Duane Hookom
MS Access MVP
--

Thanks that worked, should have thought to try that - doh!

Actually, your 2nd point brings me on to my next problem. I
want to
be
able
to display this in a report rather than have to run as a query,
but
the
date
range will differ each time. The user is prompted to enter a
start
and
end
date, if I create a report based on the crosstab query it fixes
the
column
heading with the dates in question but then the next time it is
run
with
different dates it will not work or am I doing something wrong?

:

Did you try Format([ReviewDate],"dd mmm"", ""dddd") ?
The only way you can set the column order other than
alphabetic is
to
enter
values into the column headings property.

Normally records are displayed in forms or reports where you
can
move
columns as you like.

--
Duane Hookom
MS Access MVP
--

message
Hi, I have a crosstab query which has the date as column
headings.
If
in
short date format the order is displayed correctly. The
problem
is I
want
it
to also show the day of the week and when I tried adding
this the
sort
order
was incorrect. I used Format([ReviewDate],"d mmm"", ""dddd")
but
Access
sorts the date 10 before 06. Is it possible to have the day
displayed
without
affecting the date sort order?

Thanks in advance for any help.
Sue