Two criteria (1 Viewer)

Ravi Kumar

Registered User.
Local time
Today, 16:51
Joined
Aug 22, 2019
Messages
162
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 ??
 

GinaWhipp

AWF VIP
Local time
Today, 07:21
Joined
Jun 21, 2011
Messages
5,901
Are you using a Groups and Total query? Hmm, would help if you post the SQL of the query.
 

June7

AWF VIP
Local time
Today, 03:21
Joined
Mar 9, 2014
Messages
5,425
Show your query SQL. Maybe even some sample data.
 

Ravi Kumar

Registered User.
Local time
Today, 16:51
Joined
Aug 22, 2019
Messages
162
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];
 

GinaWhipp

AWF VIP
Local time
Today, 07:21
Joined
Jun 21, 2011
Messages
5,901
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?
 

plog

Banishment Pending
Local time
Today, 06:21
Joined
May 11, 2011
Messages
11,613
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".
 

Ravi Kumar

Registered User.
Local time
Today, 16:51
Joined
Aug 22, 2019
Messages
162
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 .
 

plog

Banishment Pending
Local time
Today, 06:21
Joined
May 11, 2011
Messages
11,613
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.
 

GinaWhipp

AWF VIP
Local time
Today, 07:21
Joined
Jun 21, 2011
Messages
5,901
What happens if you remove the below from the Group By clause?

Code:
[Instruments list].[Instrument status2]
 

Users who are viewing this thread

Top Bottom