I have a query that I want to count the number of records where the value of
a field "txtFactClients " is not 0. I have set up an expression "CountFC:
txtFactClients" with Count selected in the Total line and [txtFactClients]>0
in the criteria line but when I run the query it counts every record whether
or not it is 0. I've looked at previous post but they all seem to involve
SQL, is there a way of doing this in the design mode of a query?
TIA
Tony 13 1491
Tony,
Try this before you do anything else: Look at your query in SQL view. The
Query By Design in Access allows you to switch easily between SQL, Design
and Datasheet view. Put it to it's full use. You can do anything in SQL
view that you can do in Design View, and vice-versa (although some things
are easier to do in one than the other).
If you can't work out how to do something, switch to SQL View, copy the SQL
to the ng, and say exactly the results that you want. When you get your
answer, copy the SQL back to a new query in SQL View. Then switch back to
Design View if you want. IMHO, switching back and forth between the two is
one of the best ways to learn. Not that the SQL that Access generates is
perfect, but it works.
Darryl Kerkeslager
"Tony Williams" <tw@tcpinvalid. com> wrote in message
news:cs******** **@titan.btinte rnet.com... I have a query that I want to count the number of records where the value
of a field "txtFactClients " is not 0. I have set up an expression "CountFC: txtFactClients" with Count selected in the Total line and
[txtFactClients]>0 in the criteria line but when I run the query it counts every record
whether or not it is 0. I've looked at previous post but they all seem to involve SQL, is there a way of doing this in the design mode of a query? TIA Tony
You need to remove [txtFactClients]>0 from the criteria line of your
field "CountFC:txtFac tClients" and add the field txtFactClients,
choosing 'where' in the total line and >0 in the criteria line.
Mike
txtFactClients is probably a TEXT field and as such has string values, NOT
NUMBERS.
Try:
[txtFactClients] <>"0"
as your criteria.
-Ed
"Darryl Kerkeslager" <Ke*********@co mcast.net> wrote in message
news:5v******** ************@co mcast.com... Tony,
Try this before you do anything else: Look at your query in SQL view. The Query By Design in Access allows you to switch easily between SQL, Design and Datasheet view. Put it to it's full use. You can do anything in SQL view that you can do in Design View, and vice-versa (although some things are easier to do in one than the other).
If you can't work out how to do something, switch to SQL View, copy the SQL to the ng, and say exactly the results that you want. When you get your answer, copy the SQL back to a new query in SQL View. Then switch back to Design View if you want. IMHO, switching back and forth between the two is one of the best ways to learn. Not that the SQL that Access generates is perfect, but it works.
Darryl Kerkeslager
"Tony Williams" <tw@tcpinvalid. com> wrote in message news:cs******** **@titan.btinte rnet.com... I have a query that I want to count the number of records where the value of a field "txtFactClients " is not 0. I have set up an expression "CountFC: txtFactClients" with Count selected in the Total line and [txtFactClients]>0 in the criteria line but when I run the query it counts every record whether or not it is 0. I've looked at previous post but they all seem to involve SQL, is there a way of doing this in the design mode of a query? TIA Tony
txtFactClients is probably a TEXT field and as such has string values, NOT
NUMBERS.
Try:
[txtFactClients] <>"0"
as your criteria.
-Ed
"Tony Williams" <tw@tcpinvalid. com> wrote in message
news:cs******** **@titan.btinte rnet.com... I have a query that I want to count the number of records where the value of a field "txtFactClients " is not 0. I have set up an expression "CountFC: txtFactClients" with Count selected in the Total line and [txtFactClients]>0 in the criteria line but when I run the query it counts every record whether or not it is 0. I've looked at previous post but they all seem to involve SQL, is there a way of doing this in the design mode of a query? TIA Tony
Mike wrote: You need to remove [txtFactClients]>0 from the criteria line of your field "CountFC:txtFac tClients" and add the field txtFactClients, choosing 'where' in the total line and >0 in the criteria line.
Mike
That should work. Or enter
TRUE
in the criteria for the expression
[txtFactClients]>0
While I fully agree with your advice to post the SQL of any problematic
query, I'd take a little issue with your "vice-versa" below.
I've had a number of times when I could manipulate joins in SQL view in ways
the Design View couldn't handle.
For example, suppose one table has a text field, and I want to link it to an
Integer field in another table. (Just for the sake of argument, let's say
these tables are linked from other sources, so I can't change the layout).
I can go into SQL View and write
SELECT * FROM Table1 INNER JOIN Table2 on
Table1.MyField= CStr(Table2.MyF ield)
This works great in SQL view, but refuses to display in Design View.
- Turtle
"Darryl Kerkeslager" <Ke*********@co mcast.net> wrote in message
news:5v******** ************@co mcast.com... Tony,
Try this before you do anything else: Look at your query in SQL view.
The Query By Design in Access allows you to switch easily between SQL, Design and Datasheet view. Put it to it's full use. You can do anything in SQL view that you can do in Design View, and vice-versa (although some things are easier to do in one than the other).
As well, you cannot do UNION queries in Design View
--
Doug Steele, Microsoft Access MVP http://I.Am/DougSteele
(no e-mails, please!)
"MacDermott " <ma********@nos pam.com> wrote in message
news:xs******** ********@newsre ad2.news.atl.ea rthlink.net... While I fully agree with your advice to post the SQL of any problematic query, I'd take a little issue with your "vice-versa" below.
I've had a number of times when I could manipulate joins in SQL view in ways the Design View couldn't handle.
For example, suppose one table has a text field, and I want to link it to an Integer field in another table. (Just for the sake of argument, let's say these tables are linked from other sources, so I can't change the layout). I can go into SQL View and write SELECT * FROM Table1 INNER JOIN Table2 on Table1.MyField= CStr(Table2.MyF ield)
This works great in SQL view, but refuses to display in Design View.
- Turtle
"Darryl Kerkeslager" <Ke*********@co mcast.net> wrote in message news:5v******** ************@co mcast.com... Tony,
Try this before you do anything else: Look at your query in SQL view. The Query By Design in Access allows you to switch easily between SQL, Design and Datasheet view. Put it to it's full use. You can do anything in SQL view that you can do in Design View, and vice-versa (although some things are easier to do in one than the other).
Both points well taken :)
I over-reached with "anything".
Darryl Kerekslager
"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote As well, you cannot do UNION queries in Design View "MacDermott " <ma********@nos pam.com> wrote
While I fully agree with your advice to post the SQL of any problematic query, I'd take a little issue with your "vice-versa" below.
I've had a number of times when I could manipulate joins in SQL view in ways the Design View couldn't handle.
For example, suppose one table has a text field, and I want to link it
to an Integer field in another table. (Just for the sake of argument, let's
say these tables are linked from other sources, so I can't change the
layout). I can go into SQL View and write SELECT * FROM Table1 INNER JOIN Table2 on Table1.MyField= CStr(Table2.MyF ield)
This works great in SQL view, but refuses to display in Design View.
"MacDermott " <ma********@nos pam.com> wrote in message
news:xs******** ********@newsre ad2.news.atl.ea rthlink.net... While I fully agree with your advice to post the SQL of any problematic query, I'd take a little issue with your "vice-versa" below.
I've had a number of times when I could manipulate joins in SQL view in ways the Design View couldn't handle.
For example, suppose one table has a text field, and I want to link it to an Integer field in another table. (Just for the sake of argument, let's say these tables are linked from other sources, so I can't change the layout). I can go into SQL View and write SELECT * FROM Table1 INNER JOIN Table2 on Table1.MyField= CStr(Table2.MyF ield)
This works great in SQL view, but refuses to display in Design View.
- Turtle
This is also true for non-visible SQL such as the following: This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: C White |
last post by:
A little while back I had a question about counting records and my
example was something like this:
in my database table i have the following records
john
ed
john
rick
tom
|
by: N. Graves |
last post by:
I have a table with records of games played with a field for players
name and another fields is a "WinorLose". One player may play several
games to complete a match. I want my report to tell me how many Wins
or Loses and Individual has in a match..
I'm not having a problem getting the reports and the grouping it by
person but I'm having a...
|
by: mjobrien |
last post by:
Thanks for the hint Allen (see below). But I am already doing that
count as total records read (5)in the report footer. That count is
unduplicated for the record not for the field - student ID as I've
indicated in my sample report. I really need an unduplicated count of
the student ID only. I guess my subject was not clear.
...
|
by: chrisc |
last post by:
Hello,
Hope this is the right place for this...
I am creating a testing database for components as they come off a
production line.
My reports need to select faults that are found, as well as pass or
fails dependent on a batch number.
|
by: ChadDiesel |
last post by:
I appreciate the help on this group. I know I've posted a lot here the last
couple of weeks, but I was thrown into a database project at my work with
very little Access experience. No other employee knows anything about
Access. I've searched Google Groups, and that has been a lot of help, but
there are some questions that I just can't find...
| |
by: johnds |
last post by:
This is my third question about eliminating accounting entries in a
clinical database, yet retaining the valid record. Latest wrinkle is
being able to sum the visits to the doctor, and the units of services
received. The units cannot be less than the visits, you get a least one
unit per visit. Here's the problem: If a visit is wrongly coded,...
|
by: Viktor |
last post by:
I have a problem with counting empty fields in table.
For example: I have the field in which i coulld write several solutions.
"done", "not done", "send", "not send" etc.
In report i want to count how many records in that filed is empty and the records which have written any solution in that filed.
I would be grateful for any solution.
|
by: Chia Lee Lee |
last post by:
Hello…
I have problem when counting the number of records, which is based on the start date and end date.
I have tried to use message box to prompt the result, but the result given is zero(0).Actually there have records in database which fulfill the “WHERE” clause condition in sql statement.
Thanks for yours help…
Carol,
/* Below is my...
|
by: mwang |
last post by:
I have sql like this:
select count(*) from TRANSACTION transaction0_ where transaction0_.CREATED > sysdate - 10 and exists (select lineItem.TRANSACTION_ID from LINEITEM lineItem where lineItem.EXTERNAL_ID like 'ext%' and lineItem.TRANSACTION_ID = transaction0_.TRANSACTION_ID)
The query takes more than 1 minute to complete.
One tricky thing is...
|
by: crassostrea |
last post by:
Hello and Happy New Year,
I have two tables in Access 2003 (Windows XP) with similar, but different, information. We’ll call them table A and table B. I want to count the number of records in each table and display how many of each there are, and the total, by year:
2008 A B A+B
2007 A B A+B
2006 A B A+B
Right now I...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |