Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query field syntax question

Posted on 2014-01-11
8
Medium Priority
?
337 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 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 40

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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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.
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…
Suggested Courses

971 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