Displaying the same data separately on a report

[Susan L]

My users want to see scheduled events for the current week and the next week
on the same report -- but to have the weeks separated, e.g., Current Week,
Next Week. I thought this would be simple with a report and subreport, but
the problem is that there is no parent/child relationship between this week's
and next week's data and The report/subreport jumbled the data strangely. I
have two queries that pull the correct data.

Here's what I want it to look like:
Cycle State Event Scheduled Date Time
Current Week
1 Virginia Teleconference 1/30/07 11 am
2 Missouri Shipment 1/31/07 n/a

Next Week
1 Louisiana Teleconference 2/6/07 2 pm
2 Arizona Installation 1/8/07 3 pm

I'm sure I'm just not seeing something. Any help would be appreciated.

 

[Marshall Barton]

My users want to see scheduled events for the current week and the next week
on the same report -- but to have the weeks separated, e.g., Current Week,
Next Week. I thought this would be simple with a report and subreport, but
the problem is that there is no parent/child relationship between this week's
and next week's data and The report/subreport jumbled the data strangely. I
have two queries that pull the correct data.

Here's what I want it to look like:
Cycle State Event Scheduled Date Time
Current Week
1 Virginia Teleconference 1/30/07 11 am
2 Missouri Shipment 1/31/07 n/a

Next Week
1 Louisiana Teleconference 2/6/07 2 pm
2 Arizona Installation 1/8/07 3 pm

I'm sure I'm just not seeing something. Any help would be appreciated.

If you can use a single query to get all the desired data,
then scrap the subreport and use Sorting and Grouping to
separate the weeks.

 

[Susan L]

Thanks. A colleague showed me how to do a union query to join the data from
my Current Week and Next Week queries. And I've created a report that groups
on the week/year generated by Format([DateFieldName],"wwyyyy"). So that part
works.

Now I wonder whether there's a function I could use to reformat the date
produced by the expression above that I'm grouping on, e.g., 52007 and 62007
(presumably for the 5th and 6th weeks of 2007). I tried Help but didn't find
anything.

Would appreciate any thoughts.

susan

 

[Marshall Barton]

Unless you have a ridculous table structure (such a separate
table for each week), there should be no need to use a union
query. Just set a date range criteria on the date field.
If you don't understand that, post a Copy/Paste of your
current query's SQL view.

Your grouping expression won't sort correctly for weeks 9
and 10 or when the weeks are in different years. Try this
expression instead:

Year(date) & Format(DatePart("ww", date), "00")

How would you like to format the date? There is no need to
display the date in a text box based on what you used to
sort/group, just format the date field. Try looking in VBA
Help for "Format Property" and follow the links to the type
of data you want to format.
--
Marsh
MVP [MS Access]

Thanks. A colleague showed me how to do a union query to join the data from
my Current Week and Next Week queries. And I've created a report that groups
on the week/year generated by Format([DateFieldName],"wwyyyy"). So that part
works.

Now I wonder whether there's a function I could use to reformat the date
produced by the expression above that I'm grouping on, e.g., 52007 and 62007
(presumably for the 5th and 6th weeks of 2007). I tried Help but didn't find
anything.

If you can use a single query to get all the desired data,
then scrap the subreport and use Sorting and Grouping to
separate the weeks.

 

[Susan L]

Sounds like I need to start over. I was trying to piece together bits and
pieces I'd found online, without a real understanding of what I needed to do.
In my two queries, I was using Date Format to find current week and next
week. Here's the SQL for the current week query. (I deleted 3 fields that are
irrelevant to the date issue).

SELECT tbl_Deployment_Record.State, tbl_Deployment_Details.CallDate,
Format([CallDate],"wwyyyy") AS CurrentWeek
FROM tbl_Deployment_Record INNER JOIN tbl_Deployment_Details ON
tbl_Deployment_Record.State = tbl_Deployment_Details.State
WHERE (((tbl_Deployment_Record.Status)="Open") AND
((tbl_Deployment_Details.CallDate) Is Not Null) AND
((Format([CallDate],"wwyyyy"))=Format(Date(),"wwyyyy")));

and no, I do not know how to write the criteria to pull current and next
week of data (other than create a parameter query, which I prefer not to do
for usability reasons).

I'm embarrassed to say that I don't know where to put the grouping criteria
you provided.

As for the date format on the report, I'd like the week beginning date of
Monday. I'll look in VBA help to see if I can make some headway.

--
susan

Unless you have a ridculous table structure (such a separate
table for each week), there should be no need to use a union
query. Just set a date range criteria on the date field.
If you don't understand that, post a Copy/Paste of your
current query's SQL view.

Your grouping expression won't sort correctly for weeks 9
and 10 or when the weeks are in different years. Try this
expression instead:

Year(date) & Format(DatePart("ww", date), "00")

How would you like to format the date? There is no need to
display the date in a text box based on what you used to
sort/group, just format the date field. Try looking in VBA
Help for "Format Property" and follow the links to the type
of data you want to format.
--
Marsh
MVP [MS Access]

Thanks. A colleague showed me how to do a union query to join the data from
my Current Week and Next Week queries. And I've created a report that groups
on the week/year generated by Format([DateFieldName],"wwyyyy"). So that part
works.

Now I wonder whether there's a function I could use to reformat the date
produced by the expression above that I'm grouping on, e.g., 52007 and 62007
(presumably for the 5th and 6th weeks of 2007). I tried Help but didn't find
anything.

Susan L wrote:

My users want to see scheduled events for the current week and the next week
on the same report -- but to have the weeks separated, e.g., Current Week,
Next Week. I thought this would be simple with a report and subreport, but
the problem is that there is no parent/child relationship between this week's
and next week's data and The report/subreport jumbled the data strangely. I
have two queries that pull the correct data.

Here's what I want it to look like:
Cycle State Event Scheduled Date Time
Current Week
1 Virginia Teleconference 1/30/07 11 am
2 Missouri Shipment 1/31/07 n/a

Next Week
1 Louisiana Teleconference 2/6/07 2 pm
2 Arizona Installation 1/8/07 3 pm

I'm sure I'm just not seeing something. Any help would be appreciated.


If you can use a single query to get all the desired data,
then scrap the subreport and use Sorting and Grouping to
separate the weeks.

 

[Marshall Barton]

Sounds like I need to start over. I was trying to piece together bits and
pieces I'd found online, without a real understanding of what I needed to do.
In my two queries, I was using Date Format to find current week and next
week. Here's the SQL for the current week query. (I deleted 3 fields that are
irrelevant to the date issue).

SELECT tbl_Deployment_Record.State, tbl_Deployment_Details.CallDate,
Format([CallDate],"wwyyyy") AS CurrentWeek
FROM tbl_Deployment_Record INNER JOIN tbl_Deployment_Details ON
tbl_Deployment_Record.State = tbl_Deployment_Details.State
WHERE (((tbl_Deployment_Record.Status)="Open") AND
((tbl_Deployment_Details.CallDate) Is Not Null) AND
((Format([CallDate],"wwyyyy"))=Format(Date(),"wwyyyy")));

and no, I do not know how to write the criteria to pull current and next
week of data (other than create a parameter query, which I prefer not to do
for usability reasons).

I'm embarrassed to say that I don't know where to put the grouping criteria
you provided.

As for the date format on the report, I'd like the week beginning date of
Monday. I'll look in VBA help to see if I can make some headway.

Let's try this query:

SELECT R.State, D.CallDate, . . .
FROM tbl_Deployment_Record As R
INNER JOIN tbl_Deployment_Details As D
ON R.State = D.State
WHERE R.Status="Open"
AND DatePart("ww", D.CallDate, 2)
Between DatePart("ww", Date(), 2)
And DatePart("ww", Date(), 2) +1

The Grouping expression goes in the Field/Expression column
in the Sorting and Grouping window (View menu when the
report is open in design view).

To get any date to display as the monday of the week, you
need to use an expression to calculate the date of Monday
and then you can format it as any other date. Put this
expression in a group header text box:
=DateAdd("d", -DatePart("w", CallDate, 2) + 1, CallDate)
Then set the text box's Format property to your favorite
date format (e.g. mmmm d, yyyy)

There's probably some things in there that you are not
familiar with, so check VBA Help in the VIsual Basic Editor
window (open the window by using Ctrl+g in the access
window).

 

[Susan L]

Marsh: Thank you so much. Of course, everything works! I looked up the Date
Format/Date Part functions in VB Help and will study them. I see that
functions are very powerful -- and I need to learn more. A colleague showed
me the logic of the -(DatePart segment of the expression you provided. Very
interesting way to step back and find Monday's date regardless of the
CallDate.

I really appreciate all the time your spent to help me -- and your patience
as well. Thanks again!
--
susan

Sounds like I need to start over. I was trying to piece together bits and
pieces I'd found online, without a real understanding of what I needed to do.
In my two queries, I was using Date Format to find current week and next
week. Here's the SQL for the current week query. (I deleted 3 fields that are
irrelevant to the date issue).

SELECT tbl_Deployment_Record.State, tbl_Deployment_Details.CallDate,
Format([CallDate],"wwyyyy") AS CurrentWeek
FROM tbl_Deployment_Record INNER JOIN tbl_Deployment_Details ON
tbl_Deployment_Record.State = tbl_Deployment_Details.State
WHERE (((tbl_Deployment_Record.Status)="Open") AND
((tbl_Deployment_Details.CallDate) Is Not Null) AND
((Format([CallDate],"wwyyyy"))=Format(Date(),"wwyyyy")));

and no, I do not know how to write the criteria to pull current and next
week of data (other than create a parameter query, which I prefer not to do
for usability reasons).

I'm embarrassed to say that I don't know where to put the grouping criteria
you provided.

As for the date format on the report, I'd like the week beginning date of
Monday. I'll look in VBA help to see if I can make some headway.

Let's try this query:

SELECT R.State, D.CallDate, . . .
FROM tbl_Deployment_Record As R
INNER JOIN tbl_Deployment_Details As D
ON R.State = D.State
WHERE R.Status="Open"
AND DatePart("ww", D.CallDate, 2)
Between DatePart("ww", Date(), 2)
And DatePart("ww", Date(), 2) +1

The Grouping expression goes in the Field/Expression column
in the Sorting and Grouping window (View menu when the
report is open in design view).

To get any date to display as the monday of the week, you
need to use an expression to calculate the date of Monday
and then you can format it as any other date. Put this
expression in a group header text box:
=DateAdd("d", -DatePart("w", CallDate, 2) + 1, CallDate)
Then set the text box's Format property to your favorite
date format (e.g. mmmm d, yyyy)

There's probably some things in there that you are not
familiar with, so check VBA Help in the VIsual Basic Editor
window (open the window by using Ctrl+g in the access
window).

 

[Marshall Barton]

Marsh: Thank you so much. Of course, everything works! I looked up the Date
Format/Date Part functions in VB Help and will study them. I see that
functions are very powerful -- and I need to learn more. A colleague showed
me the logic of the -(DatePart segment of the expression you provided. Very
interesting way to step back and find Monday's date regardless of the
CallDate.

I really appreciate all the time your spent to help me -- and your patience
as well. Thanks again!

You're welcome.

Come on back any time, it's what we do here ;-)