ACCESS FEED: RE: [MS_AccessPros] Re: Help with reports

Jumat, 11 Mei 2012

RE: [MS_AccessPros] Re: Help with reports

Katrina-

Hmmm. Now that I look at your table design, I see some problems. Your tblFMLA
contains a ton of repeating groups. Each set starting with Intermittent FMLA,
Intermittent FMLA Start Date, and Intermittent FMLA End Date repeats for each
"type" of activity – Occupational, Non Occupational, etc. It appears this
record records something about a person across a span of dates. Can a person
have more than one status at the same time? At the end of the record, you have
pairs of dates and description – eight of them. What happens when there's a
ninth update? Your table will break.

Please describe in more detail what this table contains and answer my question
above about multiple "activities." (I don't know what they are, so I'm just
guessing.)

When I look at each report, I see you are using the main Start Date and End Date
of the record, but you don't seem to include the start / end related to each
"activity." If you don't need those dates for the report, why are they in the
table.

But to answer your question about building one report – as long as you're not
using the individual start / end dates, you can get by with one report. Rather
than include the filter in the underlying query, just create a query that gets
all "active" records. You can apply a filter when you open the report, and you
can pass the "title" you want for the report in the OpenArgs parameter. Code in
the Load event of the report can set the report title and the caption of label
that display the name of the report in the Page Header secton.

The code to open your report filtered will looks something like:

' Intermittent report
DoCmd.OpenReport "FMLA Report", acViewPreview, _
WhereCondition:="[Intermittent FMLA] = True", OpenArgs:="Intermittent
FMLA"

' Limited Duty report
DoCmd.OpenReport "FMLA Report", acViewPreview, _
WhereCondition:="[Limited Duty] = True", OpenArgs:="Limited Duty FMLA"

… etc.

The code in the Open event of the report could be:

Private Sub Report_Load()
Me.Caption = Me.OpenArgs
Me.lblTitle.Caption = Me.OpenArgs
End Sub

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-------------------------------------

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
Sent: Friday, May 11, 2012 6:27 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Help with reports

John -

What I want to do is generate a report based on 1 query which includes all of
the information from tblFMLA. I want the report to display all active records,
and to sort those records based on which type of FMLA they are using. (i.e. all
intermittent fmla records would be grouped together and the report would display
the group heading. If you'll look at the reports I already have in the database;
I want a report just like that but just 1 report instead of a seperate report
for each group.

I hope this provides more clarification.

Thanks for your help!

Katrina

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Katrina-
>
> I have your database, but it's unclear to me which form you're talking about
and
> with report(s). Can you enlighten me?
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -------------------
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
> Sent: Friday, May 11, 2012 3:57 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Help with reports
>
>
> I have a query with several fields that have the criteria "true" so that if
the
> record has that field checked it will display on my report. My question is
when
> I create the report is it possible to have an automatic header displayed based
> on that checkbox field. Example: The fields are named intermittent fmla,
> occupational fmla, non occupational fmla....etc. I want my report to sort the
> records based on what field is checked, and assign a header to the report also
> based on that field. I'm not sure if I'm making any sense at all. I want to
> create 1 report that will display all the records sorted by the checkbox
field.
> Currently, I have a seperate report for each one. I have uploaded my database
> for review in the Assistance Needed folder.
>
> Any help is greatly appreciated!
>
> Katrina
>