In my database, I am trying to count records from a [Stratum] field that meet the criteria of "[Days}<42". Below is my SQL query code. - SELECT DISTINCTROW WARes016.Stratum, Count(([WA Res 016 Final Selection].[Days])) AS CountOfDays
-
FROM WARes016 INNER JOIN [WA Res 016 Final Selection] ON WARes016.LRS = [WA Res 016 Final Selection].LRS
-
GROUP BY WARes016.Stratum, [Days]<42
-
ORDER BY WARes016.Stratum;
This code when run breaks the count into two groups:a count of [Days] per Stratum that meet my criteria and a count of records that don't.
How do I alter the code to only show the count of records that meet my criteria? Or am I going about this all wrong? I am really new at this.
I would appreciate any feedback!
Thank you!
10 2995
I'm going to move this to the Access forum where you'll find much more experience in this area. However, I think that the HAVING clause may be what you need to use.
ADezii 8,834
Recognized Expert Expert
In my database, I am trying to count records from a [Stratum] field that meet the criteria of "[Days}<42". Below is my SQL query code. - SELECT DISTINCTROW WARes016.Stratum, Count(([WA Res 016 Final Selection].[Days])) AS CountOfDays
-
FROM WARes016 INNER JOIN [WA Res 016 Final Selection] ON WARes016.LRS = [WA Res 016 Final Selection].LRS
-
GROUP BY WARes016.Stratum, [Days]<42
-
ORDER BY WARes016.Stratum;
This code when run breaks the count into two groups:a count of [Days] per Stratum that meet my criteria and a count of records that don't.
How do I alter the code to only show the count of records that meet my criteria? Or am I going about this all wrong? I am really new at this.
I would appreciate any feedback!
Thank you!
I assuming that your Days criteria is not applied to the overall Groupings. If it were, you would need a HAVING Clause. Let me know how this works - SQL is not my strong point. - SELECT DISTINCTROW WARes016.Stratum, Count(([WA Res 016 Final Selection].[Days])) AS CountOfDays
-
FROM WARes016 INNER JOIN [WA Res 016 Final Selection] ON WARes016.LRS = [WA Res 016 Final Selection].LRS
-
WHERE [Days]<42
-
GROUP BY WARes016.Stratum HAVING
-
ORDER BY WARes016.Stratum;
- ...
-
GROUP BY WARes016.Stratum HAVING
-
ORDER BY WARes016.Stratum;
HAVING what???
NeoPa 32,569
Recognized Expert Moderator MVP
SQL is a fairly strong area of mine.
You probably don't want to use the DISTINCTROW predicate AS WELL AS the GROUP BY clause. In fact, unless I'm mistaken as to your requirements, the DISTINCTROW predicate will cause your query to give incorrect results under certain (rare) circumstances.
What you are missing is the WHERE clause. Try : - SELECT WARes016.Stratum, Count(([WA Res 016 Final Selection].[Days])) AS CountOfDays
-
FROM WARes016 INNER JOIN [WA Res 016 Final Selection] ON WARes016.LRS = [WA Res 016 Final Selection].LRS
-
WHERE [WA Res 016 Final Selection].[Days]<42
-
GROUP BY WARes016.Stratum
-
ORDER BY WARes016.Stratum;
NeoPa 32,569
Recognized Expert Moderator MVP
Please let us know if this resolves your issues.
ADezii 8,834
Recognized Expert Expert
HAVING what???
Thanks Killer, just left a 'Dangling HAVING'.
NeoPa - that fixed it and it now counts correctly! Yay! Thank you and everyone else so much for your help!
ADezii 8,834
Recognized Expert Expert
NeoPa - that fixed it and it now counts correctly! Yay! Thank you and everyone else so much for your help!
That NeoPa is something, isn't he?
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Brenda J. |
last post by:
Hello all
I'm sure this is a relatively simple query to create, but I can't seem
to get my head around it.
Here is an example of the two tables I am using:
tblAccountTypes
AccountTypeID AccountType AccountNumberRange
|
by: Tony Williams |
last post by:
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 >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
|
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:
Hello Again,
I want to assign a number to each record that will be part of a shipping
number. I want the number value to count up until the contract number
changes. Then, I want the number to go back to 1 and start counting up again
until the next contract change.
For example
Contract 1111111 Box 1 of 2 Number Value: 1
|
by: teddyparnell |
last post by:
I have a query which selects a number of names from a table, which
abide with the criteria. I then want another query to select where the
names appear more than three times when the previous query is run,
would i use the count function and how would i go about it? Cheers for
any suggestions
| |
by: geronimo_me |
last post by:
Hi,
I have a query that has the following fields:
Business:
ID Number:
LastName:
EmpNo:
Hours1:
Hours2:
|
by: Joseph Berry |
last post by:
I have created a report drawing from one table. What I would like to is, grouping by one field, count the number of records within the group that meet two selection criteria (i.e., Field1="A" and Field2="1"). How do I go about doing that?
Many thanks!
|
by: jennwilson |
last post by:
Ok - So, I am back.
I would like to count the number of times a specific record appears in a field from one table in my query and then use that value in the same query to calculate an average. I am using Access 2000 and am still learning.
Background info:
Overall, I am creating a database to track and calculate data regarding employee productivity. For each scheduled day, employees have a set number of points to earn. Employees...
|
by: RussCRM |
last post by:
I need some help getting unique records from our database! I work for a small non-profit homeless shelter. We keep track of guest information as well as what services we have offered for statistical purposes.
I've been using
Here’s the situation:
I have two main tables:
Guest (stores data such as GuestID, First Name, Last Name, etc.) and
Services (stores data such as the type of service the guest used (Shelter Bed, Lunch, Dinner,...
|
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
| |
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |