Two criteria

[Ravi Kumar]

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]

Are you using a Groups and Total query? Hmm, would help if you post the SQL of the query.

[June7]

Show your query SQL. Maybe even some sample data.

[Ravi Kumar]

Thank you for your reply .
Here is my SQL.

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]

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]

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

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]

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]

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]

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

[Instruments list].[Instrument status2]