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

Selasa, 09 April 2013

RE: [MS_AccessPros] query Count()

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
> From: dyspoz2@cox.net
> 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, 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@yahoogroups.com] On Behalf Of ghsclass65
> > Sent: Tuesday, April 09, 2013 2:44 PM
> > To: MS_Access_Professionals@yahoogroups.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]