Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query field syntax question

Posted on 2014-01-11
8
Medium Priority
?
336 Views
Last Modified: 2014-01-16
Using query designer for a field  I'm trying to count the number of records (ID) where another field is true.

Here's what I have but it doesn't work:

CountOfListings: [ID] WHERE [Pending] = True
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 85
ID: 39773592
Here's the SQL for it:

SELECT COUNT(*) AS CountOfListings FROM [YourTable] WHERE Pending=True

Obviously you'd have to replace 'YourTable' with the name of your Table.

To get to the SQL view, open the query in Design view and select View - SQL from the Ribbon.
0
 

Author Comment

by:SteveL13
ID: 39773623
I have to add a lot of fields.  This first one was just a start.  Since I don't understand SQL I'm trying to do it with the query designer.  I've attached a screen shot which may help show what I'm trying to do.

The first two fields work just fine until I add the 3rd field.  The syntax is wrong I guess.

???
query-example.jpg
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 39773685
The Count() function is counting the number of rows that satisfy the selection criteria.  No matter how many count(*)s you put in the query the results will be the same.  Using Count(somefieldname) will give you a count of the non-null values in the selected domain so you could get different values for each field.

We would need a more detailed explanation of what you are trying to count along with a definition of the table in order to suggest a query (or multiple queries) that will work.

Based on what you showed in your picture, your table is not normalized so be prepared to have to create multiple queries.  If your table was something like:

ListingID (primary key)
ListingDate
ListingStatus (taken, approved, ....)

You would use a query like:

Select Format(ListingDate, "yyyy/mm") as ListingMonth, ListingStatus, Count(*) as ListingCount
From YourTable
Group By Format(ListingDate, "yyyy/mm") as ListingMonth, ListingStatus;

This would result in a list like:
2013/01 Approved 35
2013/01 Pending 12
2013/01 Taken 40
2013/02 Approved 20
2013/02 Pending 5
2013/02 Taken 25
.....

You would add selection criteria if you wanted only a specific year or month or Status.
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 31

Expert Comment

by:hnasr
ID: 39773696
Count(*) by itself works fine, but selecting other fields with count, you need to group on these fields.

Select Count(*), field1
From tbl Where ...
Group By field1;

Adding 2 fields:
Select count(*) , field1, field2
From tbl Where ...
Group By field1, field2;

You may select the count(*) using a subquery, where you may not need to group by other selected fields.

Select field1, field2, (Select count(*) from tbl As b Where b.id=tbl.id)
From tbl Where ...;
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39774173
--->>> I have to add a lot of fields.  

You may need to be more specific than that.  Are these fields going to be Aggregate functions like the first?  ( ie: Count, Sum, etc over the records in your query?)

For the first field you are describing, I believe this will work:

CountOfListings: Abs( SUM([Pending] = True))

Open in new window


The expression "[Pending] = True" will return either 0 or -1 depending on whether the criteria is met or not.  Taking a sum of that effectively gives you a count (with a negative sign since True in Access is -1).  The Abs turns that into a positive count.

You can similarly use sums of other criteria to get different counts for additional fields.  Depending on the data, you may need to include some form of Null handling.
0
 
LVL 85
ID: 39774788
Since I don't understand SQL I'm trying to do it with the query designer
If you paste that into the query designer's SQL view and change the TableNames, then switch to Design view, you'll see the Access Query Designer with the fields and such filled in.

However, as others have said you need to provide more details of what you're trying to do.
0
 

Author Comment

by:SteveL13
ID: 39774868
Regarding: "Are these fields going to be Aggregate functions like the first?"... Yes.  The other field are going to be Sum or Count.

What I'm trying to do is develop one query which will include fields to be used on a report.

I suppose the other way to do this would be to use VBA code for the onprint event of the detail section of the report.  There are going to be 11 Aggregate fields in total.

--Steve
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39774968
It is probably doable in a query...

If the criteria are going to be the same throughout the query, syntax such as in LSMConsulting's first comment should be fine, and as he mentioned can easily be shown in design view as well.

You can add more fields in design (or SQL) view, you just need to make sure that all field are included either in an aggregate function or in the group by clause.

IF you are applying different  criteria to the aggregate funtions in the various columns, then this syntax should work:

CountOfListings: Abs( SUM([Pending] = True))

Open in new window


It allows you to base your count on specific criteria for a single column, independently of and without affecting criteria in other columns.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question