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

Selasa, 09 April 2013

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, 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
> > 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, 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]
>