Rabu, 10 April 2013

RE: [MS_AccessPros] query Count()

 

Russ-

It wasn't working because I ignored your two yes/no filters. First, I had
to create a query:

qryActiveNamesRoles:

SELECT tblAssignedRoles.AssRoleID, Names.Active, tblAssignedRoles.[Yes-No]

FROM [Names] INNER JOIN tblAssignedRoles ON Names.NameID =
tblAssignedRoles.AssNameID

WHERE (((Names.Active)=True) AND ((tblAssignedRoles.[Yes-No])=True));

Then, I fixed the DCount to use that:

SELECT tblAssignedRoles.AssRoleID, tblRoleList.RoleName, [FirstName] & " " &
[LastName] AS [=FullName], Names.LastName, Names.FirstName,
tblRoleList.RoleID, DCount("[AssRoleID]","qryActiveNamesRoles","[AssRoleID]
=" & [RoleID]) AS Expr1

FROM tblRoleList INNER JOIN ([Names] INNER JOIN tblAssignedRoles ON
Names.NameID = tblAssignedRoles.AssNameID) ON tblRoleList.RoleID =
tblAssignedRoles.AssRoleID

WHERE (((Names.Active)=Yes) AND ((tblAssignedRoles.[Yes-No])=Yes))

ORDER BY tblAssignedRoles.AssRoleID, Names.LastName;

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: Wednesday, April 10, 2013 2:35 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] query Count()

John,

Although I used Duane's suggestion and it worked out, I would like to try
and find out why your DCount solution didn't pan out. I'll send a brief
sample db with the relevant tables, queries and reports. The reports name
ending with "test" is using your suggested DCount query. And the query
ending with "test" is that query. The sample db will be named Roster.zip

Russ

--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas
<JohnV@...> wrote:
>
> 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%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of ghsclass65
> Sent: Tuesday, April 09, 2013 3:39 PM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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>
> <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>
> <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>
> <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%40yahoogroups.com>
> > > > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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>
> <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]
>

[Non-text portions of this message have been removed]

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (9)
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar