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

Selasa, 09 April 2013

RE: [MS_AccessPros] query Count()

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;