How to run multiple expressions on the same data field in a single query

[Damob9K]

Hello,

I am trying, and failing quite spectacularly to create a single query that will contain multiple expressions on the same field.

Please see attached jpg for example.
SQL: SELECT Count(StockData.status) AS FaultyPCs
FROM StockData
HAVING (([StockData].[Status] In ("Waiting Repair") And [StockData].[DeviceType] In ("Thick PC")));

What I want to be able to do is then add additional expressions to filter and count in the same way for "Thin PC" "Thin Laptops" "Thick Laptops" and so on.

But when I add one of these expressions to the next column in the builder, it seems to apply itself to the expression to it's left, causing that one to now give a result of 0.

Is there a way of separating one from another or do I have to create a separate query for each expression and then use another query to pull them all together (as I have done in another situations - but this will involve creating probably 20 separate queries.

Hope I've explained myself well enough there !!

Many thanks

Damob

 

[plog]

You should bring the [DeviceType] field into the SELECT portion of the query and GROUP BY it:

SELECT DeviceType, Count(status) AS FaultyTotal
FROM StockData
WHERE Status="Waiting Repair"
GROUP BY DeviceType;

 

[Brianwarnock]

To me this sounds like the type of problem where you use sum(iif(

Select sum(iif(devicestype="thick pc",1,0)) as countthick, sum(iif(devicetype= etc etc
From stockdata
Where status ="waiting repair"


Brian

 

[Damob9K]

Thanks plog ... the answer looks so simple when you see it in black and white !!

In attempting to learn Access, VBA and SQL it does seem that there can be quite a few ways of getting similar results, Because the query I built worked for one lookup (and I tend to start with the simple solution and then attempt to make it better once I have learnt a bit more) I was thinking that I needed to replicate that same formula, when I should of been looking for a different method.

Thanks also Brianwarnock, but as plog's solution worked for what I need, I have used that... yours may work better in some circumstances but I don't understand it quite as well as I think I understand plog's

Thanks to you both . . I can now get rid of the multitude of individual queries I had made and narrow them down to 4 possibly

Cheers

Damob

 

[Brianwarnock]

No problem, plog's solution gives the count in a vertical pre notation, mine horizontally, ie all in one record which is what I thought you wanted, but yes plog's solution is far simpler if it gives you what you want.

Brian

 

[Damob9K]

Ah I see, yes that would be better for what I intended, OK I will try both methods side by side.

Thanks Brian

 

[Damob9K]

Hi Brian,

Just got a query on the syntax:

Select sum(iif(devicestype="thick pc",1,0)) as countthick, sum(iif(devicetype= etc etc
From stockdata
Where status ="waiting repair"

Should the bit in bold be Count as "thick" ?
Also what does the ..,1,0 part do ?

Many thanks for your help

Damob

 

[Damob9K]

OK sorry Brian, disregard the first part of my last question, I think I was the thick client
For some reason I couldn't get it to work on the first attempt, but I re did it and now it's working.

just adding some extra WHERE clauses to refine things a bit more.

One last question, is it better to do simple calculations to deduce other info like the total of all - in another query or just do it as part of the form or report ?

Many thanks again

Damob