ACCESS FEED: Re: [MS_AccessPros] Report grouping and summing

Senin, 24 Desember 2012

Re: [MS_AccessPros] Report grouping and summing

The Michie numbers are different, but the customer (NH Advance) and employee (Gieson) are the same. There is no reason to duplicate the customer and employee name each time a Michie number within a particular customer and employee changes. Compare screen shots 1 and 3 to see what I mean. I changed the "Hide Duplicate" property to Yes for all the fields, but the customer and employee still duplicate for some reason.

I created the new report using the wizard and only used qryTeamOR as the input. I don't understand why the record source includes the employee table. It must have something to do with the way the tables are connected. Last year I had help from you folks to set up the tables. They were a mess before then. I'm not sure how to correct that issue (or if it needs to be corrected). Let me know what you think.

--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
> Why is it duplicate data when the Michie numbers are different?
>
> BTW: there is no reason to have the employee table in the report's record source. The record source should simply be [qryTeamOR].
>
>
> Duane Hookom MVP
> MS Access
>
> ----------------------------------------
> > From: bradgriffis@...
> >
> > Your suggestions worked. I created a new report that totals the way I want and omits the functions that are not needed. However, there is duplicate data on the report that I do not know how to remove. I added a screen shot 3 to my folder that has the duplicate data highlighted. I also put a corrected version of the database in my folder. Please let me know how to remove the duplicate data on the report (rptTeamOR). Thanks.
> >
> > --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> > >
> > > Either use the indirect field in tblFunction to filter eliminate the "Scheduling" and "Documentation" functions or add another field you can use. Then add another grouping level by MichieNumber to display a total.
> > >
> > >
> > > Duane Hookom MVP
> > > MS Access
> > >
> > > ----------------------------------------
> > > > From: bradgriffis@
> > > >
> > > > Duane, I understand what you are saying about summing the function minutes. Let's forget about that change for now. I can live without it. There are two more important changes that I would like to make if possible: 1) For each employee, I want to sum all the function times for each Michie number (or product, which is the same thing). It must be easy to see how much total function time was spent on each Michie number for each employee; 2) I also need to eliminate certain functions from this report only. There are functions that my boss does not need to see in this report. For the sake of testing, let's eliminate the "Scheduling" and "Documentation" functions. Those functions still must be present in the records to appear on other reports. If you can help me make these two changes, that would be great. I will continue to work on these changes also.
> > > >
> > > > I will look into the issue of the duplicate employee table. I am not sure why I did that. Sometimes I am unsure about what needs to be brought into a query or report and tend to bring in more than I need (better safe than sorry). Thanks.
> > > >
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> > > > >
> > > > > I'm not sure it is possible to summarize the minutes and keep the comments since these are both from the same table. If you really want to sum the minutes, what do you want to do with the comments?
> > > > >
> > > > > Also, there doesn't seem to be any reason to have the employee table in the report's record source since it is already included in qryTeamOR.
> > > > >
> > > > > Duane Hookom
> > > > > MS Access MVP
> > > > >
> > > > > ----------------------------------------
> > > > > > From: bradgriffis@
> > > > > >
> > > > > > The numbers in my second screen shot were not in hours/minutes format. I just replaced it with a corrected version. Sorry about that.
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> > > > > > >
> > > > > > > The screen shots suggest data for 12/18/2012 but there is no data matching that date. You sample screen shots should match with the data and you need to provide the filter values.
> > > > > > >
> > > > > > > Duane Hookom MVP
> > > > > > > MS Access
> > > > > > >
> > > > > > > ----------------------------------------
> > > > > > > > From: bradgriffis@
> > > > > > > >
> > > > > > > > Hello, I need help grouping and summing records in a report. I created a "Time Tool" folder in the "Assistance Needed" folder to place my files. It contains a zipped copy of my db and two screen shots. The problem report is named rptTeamOR. The first screen shot (capture1) is how the report appears now. The second screen shot (capture2) is the same data keyed into Excel as I want it to appear. There are two differences: 1) all similar function times are summed onto one number on a detail record; 2) there is a total function time for each Michie number. Keep in mind that each product has a unique Michie number, so the two terms can be used interchangeably. My boss wants to view the report and quickly see how much time was spent on each Michie number (each product). I use Access 2007 and my experience level is intermediate. Thanks for your help.
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
>