Struggling to Count data

[kevnaff]

Hi All.

I have the below query which selects data and then counts the number of records that fits the criteria. The criteria is that the Equipment's Owner Department is 'Equipment Pool' and location is 'Equipment Pool'.

Working query

I now want to be able to count the records where the criteria is Owner Department is 'Equipment Pool' but the location is NOT LIKE 'Equipment Pool'. When I enter this criteria, I get the below results, where the Devtype for each location shows. I don't need to know the location, only the count of devtypes that are NOT LIKE 'Equipment Pool'.

So for Example, the result I want for the first line is...

Devtype CountofLocation
BED ELECTRIC DRIVEN 10

The value of 10 comes from the 7 located in 1A, 1 located in 2A and 2 located in 5C.

Is there a way to get this from the query, or do I need to create another query to add up these values?

Thanks in advance.

 

[NauticalGent]

Not sure I understand your question, but it appears that all you need to do is uncheck the Location field in the QBE...?

 

[kevnaff]

Not sure I understand your question, but it appears that all you need to do is uncheck the Location field in the QBE...?

Thanks for the reply.

If I uncheck the location field, I get this...

What I want to do is combine the records where each device type appears more than once so it would look like this...

BED ELECTRIC DRIVEN 10
BED PLINTH ELECTRIC 1
DOCKING STATION 1
DOPPLER VASCULAR 2

If that makes sense?

 

[NauticalGent]

If you want to count the DevType, than change the Count to that field and not the Location.

 

[mike60smart]

Thanks for the reply.

If I uncheck the location field, I get this...

View attachment 114659


What I want to do is combine the records where each device type appears more than once so it would look like this...

BED ELECTRIC DRIVEN 10
BED PLINTH ELECTRIC 1
DOCKING STATION 1
DOPPLER VASCULAR 2

If that makes sense?

Can you upload a zipped copy of your Database?

 

[CJ_London]

For your second query you need to remove location or use a criteria such as =[location] which will always return true

 

[kevnaff]

If you want to count the DevType, than change the Count to that field and not the Location.

I tried this too, when I count the DevType, I got the same results.

I have just created a new query to look at the results of the initial query and have managed to get the data I require.

 

[Gasman]

Thanks for the reply.

If I uncheck the location field, I get this...

View attachment 114659


What I want to do is combine the records where each device type appears more than once so it would look like this...

BED ELECTRIC DRIVEN 10
BED PLINTH ELECTRIC 1
DOCKING STATION 1
DOPPLER VASCULAR 2

If that makes sense?

You would group by DevType.