Solved

Query field syntax question

Posted on 2014-01-11
8
334 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 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 37

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

752 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