ACCESS FEED: Re: [MS_AccessPros] query Count()

Selasa, 09 April 2013

Re: [MS_AccessPros] query Count()

John,

I got it after some report header adding and position switching.

Thank you both.

Russ

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Russ-
>
>
>
> As Duane suggested, build the Totals query and save it. Then open your
> report Record Source query in Design view and add the query you just
> created, joined on RoleID.
>
>
>
> The DCount should have worked - it should count the number of rows in
> tblAssignedRoles that match the current RoleID of each record.
>
>
>
> John Viescas, Author
>
> Microsoft Access 2010 Inside Out
>
> Microsoft Access 2007 Inside Out
>
> Microsoft Access 2003 Inside Out
>
> Building Microsoft Access Applications
>
> 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 ghsclass65
> Sent: Tuesday, April 09, 2013 3:39 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] query Count()
>
>
>
>
>
> Duane,
>
> Yes RoleID is the same as task. Isn't a report limited to one record source?
> You mentioned "adding" this query into the report's record source, however I
> thought that it could only contain one record source.
>
> Russ
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , Duane Hookom
> <duanehookom@> wrote:
> >
> > Assuming RoleID is the same as "task" you need to create a totals query
> that counts the number: SELECT tblRoleList.RoleID, Count(*) as NumOf
> > FROM [Names] INNER JOIN (tblRoleList INNER JOIN tblAssignedRoles ON
> tblRoleList.RoleID = tblAssignedRoles.AssRoleID) ON Names.NameID =
> tblAssignedRoles.AssNameID
> > WHERE (((Names.Active)=Yes) AND ((tblAssignedRoles.[Yes-No])=Yes))
> > GROUP BY tblRoleList.RoleID ;
> > Then add this query into your report's record source and join the RoleID
> fields. You can now add [NumOf] to the record source to sort on. Duane
> Hookom MVPMS Access > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > From: dyspoz2@
> > > Date: Tue, 9 Apr 2013 13:15:22 +0000
> > > Subject: Re: [MS_AccessPros] query Count()
> > >
> > > That DCount returs the total number of volunteers and I need the total
> number of volunteers per task.
> > >
> > > Russ
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
> wrote:
> > > >
> > > > Maybe add a DCount to the SQL:
> > > >
> > > > SELECT tblAssignedRoles.AssRoleID, tblRoleList.RoleName, [FirstName] &
> " " &
> > > > [LastName] AS [=FullName], Names.LastName, Names.FirstName,
> > > > tblRoleList.RoleID, DCount("[AssNameID]", "tblAssignedRoles",
> "[AssRoleID] =
> > > > " & [RoleID])
> > > > FROM [Names] INNER JOIN (tblRoleList INNER JOIN tblAssignedRoles ON
> > > > tblRoleList.RoleID = tblAssignedRoles.AssRoleID) ON Names.NameID =
> > > > tblAssignedRoles.AssNameID
> > > > WHERE (((Names.Active)=Yes) AND ((tblAssignedRoles.[Yes-No])=Yes))
> > > > ORDER BY Names.LastName;
> > > >
> > > > .. and ask the report to sort on that.
> > > >
> > > > John Viescas, Author
> > > > Microsoft Access 2010 Inside Out
> > > > Microsoft Access 2007 Inside Out
> > > > Microsoft Access 2003 Inside Out
> > > > Building Microsoft Access Applications
> > > > SQL Queries for Mere Mortals
> > > > http://www.viescas.com/
> > > > (Paris, France)
> > > >
> > > > ---------------------------------------
> > > >
> > > > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of ghsclass65
> > > > Sent: Tuesday, April 09, 2013 2:44 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > Subject: [MS_AccessPros] query Count()
> > > >
> > > >
> > > > I've got a query that displays the people who are assigned for each
> certain
> > > > duty. In the report based on that query, I have a text box =Count(*)
> which
> > > > lists the total number of people who have each task. I would like to
> sort
> > > > these task groupings in the report by the number of people who do
> these
> > > > jobs, but in the sort/group section there is no field that can allow
> me to
> > > > do that. Can I add an expression to the query so it will appear in the
> > > > report? SQL follows:
> > > >
> > > > SELECT tblAssignedRoles.AssRoleID, tblRoleList.RoleName, [FirstName] &
> " " &
> > > > [LastName] AS [=FullName], Names.LastName, Names.FirstName,
> > > > tblRoleList.RoleID
> > > > FROM [Names] INNER JOIN (tblRoleList INNER JOIN tblAssignedRoles ON
> > > > tblRoleList.RoleID = tblAssignedRoles.AssRoleID) ON Names.NameID =
> > > > tblAssignedRoles.AssNameID
> > > > WHERE (((Names.Active)=Yes) AND ((tblAssignedRoles.[Yes-No])=Yes))
> > > > ORDER BY Names.LastName;
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> > >
> > >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>