ACCESS FEED: [MS_AccessPros] Re: Help with reports

Duane,

Thanks! I've got the report working, and I've got the "Find Record" form working. Question regarding the report. How do I make all the updates for each employee show up in one box rather than have each update date & update reason show up separately. Example: If you look at the report you'll see that there are 2 records for Diana Boatright. what I want is to have all of her employee information show up and then have the update dates and reasons show up in one box for her instead of multiple. I hope this makes sense.

Thanks for your help!

Katrina

--- In MS_Access_Professionals@yahoogroups.com, "Duane" <duanehookom@...> wrote:
>
> Katrina,
>
> You have to open the report in design view and then view the Group, Sort, and Total. You have one level at the bottom of the design view (Access 2007) that needs to be deleted.
>
> You would need to open the offending form in design view and check the form properties to find and delete the filter property.
>
> I found some of this by running the database documenter for all objects and most properties. I saved the results as RTF format. I opened the resulting file in Word and searched for the text "Employee ID".
>
> Duane Hookom
> MS Access MVP
>
> --- In MS_Access_Professionals@yahoogroups.com, ka0t1c_ang3l <no_reply@> wrote:
> >
> > Duane,
> >
> > I am a novice to Access. The projects I've worked on as of late I've kind of just been thrown into. It is comforting to know that when I don't know how to do something I can come here to ask questions, and I truly appreciate the help.
> >
> > With that said, I went through the database trying to find where to delete the "ID = 7" filter and was unable to find it, nor was I able to find where to fix or delete the group, sort, and total level based on the Employee ID.
> >
> > Thanks for your help!
> >
> > Katrina
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "Duane" <duanehookom@> wrote:
> > >
> > > Your report also contains a Group, Sort, and Total level based on Employee ID that should be fixed or deleted.
> > >
> > > Duane Hookom
> > > MS Access MVP
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, ka0t1c_ang3l <no_reply@> wrote:
> > > >
> > > > John -
> > > >
> > > > I have uploaded my database named "Test" under AssistanceNeeded. I created the tables, forms and subforms as you instructed. I also created my FMLAQuery and FMLA Report. I'm having some issues with one of the forms and my report. When I open the "Find Record" form and select a name I get a message asking me to enter a parameter value for "ID", and I'm getting the same message when I open the FMLA Report from the Reports Menu asking to enter a parameter value for "EmployeeID".
> > > >
> > > > Any help is greatly appreciated.
> > > >
> > > > Thanks!
> > > >
> > > > Katrina
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > > > >
> > > > > 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
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>