Solved

Query field syntax question

Posted on 2014-01-11
8
329 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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 84
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 34

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
 
LVL 30

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 61

Accepted Solution

by:
mbizup earned 500 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 84
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now