Rabu, 10 April 2013

Re: [MS_AccessPros] query Count()

 

John,

The sort in the report is sorting the field as a text field instead of a number field. 12 first, then 14, then 2, then 22, then 24, then 31 etc.

Russ

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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 (10)
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar