Regular Periodic Reports

[JMDennis7]

I have a Db that records the various projects that I work on throughout my
workweek and I want to be able to report to my boss a summary of the types of
activities I've done over the course of various lengths of time.

Without reverting to a parameter query and typing in date ranges all the
time, how can I generate a weekly reporting tool that will cover only the
current week, with a heading saying something to the effect of "…for the Week
Ending February 2, 2008" as an example?

I would like to be able to do the same for the previous week, the current
month, the previous month, the current quarter, the previous quarter the
current year and the previous year.

Would this be best done in a query or in a report? I would rather have an
abundance of reports to choose from on my switchboard than to do a lot of
typing at the end of a busy week.

Thank you for your help

 

[Allen Browne]

So your table has fields like this:
ProjectID relates to primary key of Projects table.
StaffID relates to primary key of Staff table.
StartDateTime date and time when this staff member started a session on
a project.
Duration Number (how many minutes they worked on it this time.)

It that's the idea, you can just create a report, and in the Sorting And
Grouping box create a grouping on the StartDateTime field. You can there
specify you want to group by the week, and Access will give you a report
with weekly grouping.

You can filter the report to exclude weeks before the current week, by
entering critera to your query such as this:

= Date() - Weekday(Date()) + 1

There's quite a bit more to it if you are actually recording records where
the ending date/time could be days or weeks after the starting date/time.

 

[John W. Vinson]

I have a Db that records the various projects that I work on throughout my
workweek and I want to be able to report to my boss a summary of the types of
activities I've done over the course of various lengths of time.

Without reverting to a parameter query and typing in date ranges all the
time, how can I generate a weekly reporting tool that will cover only the
current week, with a heading saying something to the effect of "…for the Week
Ending February 2, 2008" as an example?

I would like to be able to do the same for the previous week, the current
month, the previous month, the current quarter, the previous quarter the
current year and the previous year.

Would this be best done in a query or in a report? I would rather have an
abundance of reports to choose from on my switchboard than to do a lot of
typing at the end of a busy week.

Thank you for your help

A single report would do (depending on the data you want reported); you could
use a parameter query using two controls on your switchboard form. One might
be an unbound combo box cboDateRange with a RowSourceType of ValueList,
ColumnCount 3, and values

"Last Week"; DateAdd("d", -8-Weekday(Date()), Date());
DateAdd("d", -1-Weekday(Date()), Date());
"Last Month"; DateSerial(Year(Date()), Month(Date()) - 1, 1);
DateSerial(Year(Date()), Month(Date()), 0);
"Year to date"; DateSerial(Year(Date()), 1, 1); Date();
"Last Year"; DateSerial(Year(Date()) -1, 1, 1);
DateSerial(Year(Date()), 1, 0)

and so on. Use a criterion on the datefield of your query

= Forms!frmSwitchboard!cboDateRange.Column(1) AND <= Forms!frmSwitchboard!cboDateRange.Column(2)

John W. Vinson [MVP]

 

[Wayne-I-M]

Hi

To follow on from John's and Allen's posts which tell you how to make the
report. If you set up a macro to run the reports for you (there are other
methods but this is the simlest) you can send these to your boss weekly using
scheduler.

See this post for details
http://groups.google.com/group/micr...roup:microsoft.public.access#0ad542bc771f5144

--
Wayne
Manchester, England.

 

[John]

John,

A single report would do (depending on the data you want reported); you
could
use a parameter query using two controls on your switchboard form. One
might
be an unbound combo box cboDateRange with a RowSourceType of ValueList,
ColumnCount 3, and values

"Last Week"; DateAdd("d", -8-Weekday(Date()), Date());
DateAdd("d", -1-Weekday(Date()), Date());
"Last Month"; DateSerial(Year(Date()), Month(Date()) - 1, 1);
DateSerial(Year(Date()), Month(Date()), 0);
"Year to date"; DateSerial(Year(Date()), 1, 1); Date();
"Last Year"; DateSerial(Year(Date()) -1, 1, 1);
DateSerial(Year(Date()), 1, 0)

In Access2003 when I try to do this the combo doesn't show the calculated
dates. It only shows the actual text of the code. Do I need to set some
special setting to make this work?

Thanks,
john

 

[John W. Vinson]

John,


In Access2003 when I try to do this the combo doesn't show the calculated
dates. It only shows the actual text of the code. Do I need to set some
special setting to make this work?

Thanks,
john

Are you putting the semicolon delimited string in the RowSource property? This
might actually not work - you may need to set up a little four row table with
the "Last Week" etc. labels and create a Query using the date functions as
calculated fields, and base the combo on that Query.

John W. Vinson [MVP]

 

[JMDennis7]

I'm sorry but I'm a little lost here. Although I've been dabbling with Access
for a few years now, i'm still considered somwhat of a beginner -- perhaps an
intermediate beginner to be more precise, especially when I'm attempting some
of these more advanced operations.

Could you walk me through this Switchboard form operation? i really only
will have a couple of actual reports to generate. One will show the projects,
the activities/tasks and the total number of hours and minutes for each
activity/task and overall project work. This will probably be the more common
of the two. For this i would need for the current week, last week, the
current month (if I generate it on the last day of the month), the previous
month, the YTD, and last year (if I don't get to it before the holidays). The
other report will use only shorter periods of time and will include more of
the details of the work that I've done (the Record (memo) field).

As I'm still building the Db and it has been a few years since I've created
a switchboard, could you have mercy on my soul and walk me through this? I
have a switchboard with the following sub-menus: Forms (where i will be
adding most of the data), Reports (where we, I think will be concentrating
our efforts) and Utilities (where I will be editing what data has already
been entered).

So what do I do now?

 

[John W. Vinson]

As I'm still building the Db and it has been a few years since I've created
a switchboard, could you have mercy on my soul and walk me through this? I
have a switchboard with the following sub-menus: Forms (where i will be
adding most of the data), Reports (where we, I think will be concentrating
our efforts) and Utilities (where I will be editing what data has already
been entered).

So what do I do now?

Well... sorry, but I don't use the Switchboard Wizard (roll my own
switchboards by creating a Form with a listbox displaying the forms/reports to
be launched).

What you'll need to do is determine the name of the wizard-generated form that
launches the reports, and put a combo box on it, and use that formname and
control name as the search criterion in the query. Or, instead of launching
the Report, launch a little form named frmCrit, and put the query criteria
controls on THAT form, along with a command button to launch the report (the
wizard will do this for you).

John W. Vinson [MVP]

 

[John]

Thanks. I managed to put the whole text into a string and set the rowsource
to that string at form load.
John

 

[José Ángel]

Hi. This is a test.

Thanks. I managed to put the whole text into a string and set the
rowsource to that string at form load.
John