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

Selasa, 09 April 2013

RE: [MS_AccessPros] query Count()

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]