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

Selasa, 09 April 2013

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;
>