Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-27-2019, 09:27 PM   #1
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 86
Thanks: 106
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Two criteria

I have a query with 3 columns,
there are 5 types of filters , A,B,C,D,E
I want my query to show Total of only A,B and not Others.
But when I use OR criteria it is showing 2 rows (for each one of them).
How to get total in only one row for both ??

Ravi Kumar is offline   Reply With Quote
Old 10-27-2019, 09:55 PM   #2
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,386
Thanks: 22
Thanked 900 Times in 884 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: Two criteria

Are you using a Groups and Total query? Hmm, would help if you post the SQL of the query.
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
The Following User Says Thank You to GinaWhipp For This Useful Post:
Ravi Kumar (10-27-2019)
Old 10-27-2019, 09:57 PM   #3
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,455
Thanks: 0
Thanked 573 Times in 569 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Two criteria

Show your query SQL. Maybe even some sample data.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Ravi Kumar (10-27-2019)
Old 10-27-2019, 10:27 PM   #4
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 86
Thanks: 106
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Re: Two criteria

Thank you for your reply .
Here is my SQL.
Code:
TRANSFORM Count([Instruments list].[MASTER List serial number]) AS [CountOfMASTER List serial number]
SELECT [Instruments list].[EQUIPMENT NAME], Count([Instruments list].[MASTER List serial number]) AS [Total Of MASTER List serial number]
FROM [Instruments list]
WHERE ((([Instruments list].[Instrument status2])="In-Use" Or ([Instruments list].[Instrument status2])="Spare"))
GROUP BY [Instruments list].[EQUIPMENT NAME], [Instruments list].[Instrument status2]
PIVOT [Instruments list].[Calibrating agency];
Ravi Kumar is offline   Reply With Quote
Old 10-28-2019, 06:27 AM   #5
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,386
Thanks: 22
Thanked 900 Times in 884 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: Two criteria

Hmm, well it looks like Instrument Status2 might be preventing it from totaling on one line. What happens if you take that out of the GROUP BY? Oh and just noticed Calibrating Agency? Do those instruments use the same Agency?
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
The Following User Says Thank You to GinaWhipp For This Useful Post:
Ravi Kumar (11-02-2019)
Old 10-28-2019, 07:50 AM   #6
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,419
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Two criteria

Come back 1 step. Do not make a cross-tab query yet, so run just this SQL:

Code:
SELECT [Instruments list].[EQUIPMENT NAME], Count([Instruments list].[MASTER List serial number]) AS [Total Of MASTER List serial number]
FROM [Instruments list]
WHERE ((([Instruments list].[Instrument status2])="In-Use" Or ([Instruments list].[Instrument status2])="Spare"))
GROUP BY [Instruments list].[EQUIPMENT NAME], [Instruments list].[Instrument status2]
That's going to produce the exact same data as the query you posted. It's going to have the "dupelicate" rows. Except, they aren't duplicate--they just seem that way to you.

Each row is going to be distinct--meaning some value in some field is different from every other row. You need to find what you call "duplicate" rows and determine which one you want to show up.

I suggest you run the SQL I posted and post back here some sample data to demonstrate what you are calling "duplicates".
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
Ravi Kumar (11-02-2019)
Old 11-02-2019, 12:07 AM   #7
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 86
Thanks: 106
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Re: Two criteria

Dear sir ,
Thank you for your reply.
At first I made a query with exactly the SQL you gave to me , And the result were same as you described.
But I need Only one row for each equipment name, But Total should be from both "in-Use" and "spare".
Please tell me how to achieve this .

Ravi Kumar is offline   Reply With Quote
Old 11-02-2019, 05:08 AM   #8
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,419
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Two criteria

If that's in response to my post, please do what I requested: Post back here sample data of what the queyr returned and tell me how it is incorrect.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
Ravi Kumar (11-02-2019)
Old 11-02-2019, 09:58 AM   #9
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,386
Thanks: 22
Thanked 900 Times in 884 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: Two criteria

What happens if you remove the below from the Group By clause?

Code:
[Instruments list].[Instrument status2]

__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
The Following User Says Thank You to GinaWhipp For This Useful Post:
Ravi Kumar (11-02-2019)
Reply

Tags
criteria , query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query based on multiple criteria, show only results that match both criteria Scaniafan Queries 11 08-25-2017 05:08 AM
[SOLVED] Sort Order based on specific criteria when 3 criteria are provided Rx_ Queries 2 04-07-2016 08:16 AM
Setting query criteria to be 'blank' depending on the criteria of a combo box whitty15 Queries 5 10-22-2006 10:35 PM
Is it possible to choose no criteria on a user-defined criteria form? HeatherMichelle Forms 1 09-15-2005 10:57 AM
enter multi criteria to display report if blank criteria select all records! frangipani Forms 1 02-11-2003 06:40 AM




All times are GMT -8. The time now is 07:12 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World