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

Katrina-

tblFMLA should have a "LeaveType" field that indicates what type of leave. It
could be a numeric code with the value provided from another table you might
call tlkpLeaveTypes:
LeaveTypeID
LeaveTypeDesc

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: Tuesday, May 15, 2012 3:42 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Help with reports

John -

I'm just now getting a chance to work on this project again. Reading over your
response, I want to be sure I'm interpreting what you're saying correctly. I
would create a table(tblFMLA) for each leave type? Or, tblFMLA would include
each leave type as a checkbox?

Thanks!

Katrina

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Katrina-
>
> Each type of leave should have its own record, especially since they can't be
on
> more than one type of leave at a time. The table should look like:
>
> tblFMLA:
> FMLAID – Pkey
> EmployeeID
> Org
> LeaveType
> StartDate
> EndDate
>
> tblFMLAInfo
> ID – Pkey
> FMLAID – pointer to the leave record
> UpdateInfoDate
> UpdateInfo
>
> You would edit this in a form with two embedded subforms. The outer form would
> hold the employee record. The first subform would let you enter Leave records
> for the employee, and the subform within that would allow for an infinite
number
> of updates.
>
> Your reports as they stand now display the main start and end dates, not the
> dates for the each leave. If you get rid of the main dates, you'll need one
> report for each type of leave unless you also change the Control Source of
those
> two dates on the fly.
>
> 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 8:15 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Help with reports
>
>
>
> John -
>
> The start and end date for the employee indidates when they started fmla and
> estimates when they expect to end fmla leave. The individual start and end
dates
> for each category will very depending on when they started on that type of
> leave. When an employee moves from one type of leave to another the checkbox
> will be updated and so will the dates, and the dates for the leave they were
in
> prior will remain. They will not have more than 1 status at a time. As for the
> updates, it is very rare that we ever get any employee who goes above a 5th
> update, but I added in a few extras just in case. I will take out the main
start
> and end date they aren't needed in the report.
>
> Please let me know if you have any other questions, or if I didn't answer a
> question.
>
> Thanks!
>
> Katrina
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> >
> > 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
> > >
> >
>