Solved

Query field syntax question

Posted on 2014-01-11
8
332 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 35

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access - need to reduce row size 25 52
Normalization of a table 19 69
User Level Security 6 38
Update a text value in another table 10 37
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

785 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