Counting text entries based on criteria

[benjammin]

Hello!

I have an inventory database and I want to produce some metrics to hand out to others but I'm not quite sure how to do what I want to do. Right now I have a query that pulls all of the equipment and where it's located. SO, my two main items are EquipmentType and SiteName. So for an example the EquipmentType will be Datalogger and the SiteName will be Shiprock. We have multiple Dataloggers at Shiprock so I want to be able to do is have the query count the number of Dataloggers that belong to the site Shiprock. That in itself is fairly easy but I have multiple sites with dataloggers and I want to count those too. Same goes for EquipmentType of Powersupply/Antenna/etc... at all the different sites I have. So in the end I'll end up with a table with the SiteNames on the Y axis and the EquipmentTypes on the X axis with total numbers filling them in. How would I go about counting all these automatically? Does this make sense?


THANK YOU!

[JoeM]

If I am understanding your correctly, I think you can get your counts pretty easily by doing an Aggregate Query by grouping on your SiteName and EquipmentName fields, and counting the number of records.

Simply create a new query and add the SiteName, EquipmentName, and any other field to it (or add one of the first two fields again).
Now, click on the Totals button (looks like a Sigma) to create an Aggregate Query. This will add a Totals Row to the Query Builder with the words "Group By" under each field.
Under the third field you added, change the Totals row value from "Group By' to "Count".
View your results.

This will give you every single SiteName/EquipmentName combination you have with the count of how many records have each combination.

[pbaldy]

That was my first thought too, but if you want the XY axis look, play with the crosstab query wizard.

[benjammin]

If I am understanding your correctly, I think you can get your counts pretty easily by doing an Aggregate Query by grouping on your SiteName and EquipmentName fields, and counting the number of records.

Simply create a new query and add the SiteName, EquipmentName, and any other field to it (or add one of the first two fields again).
Now, click on the Totals button (looks like a Sigma) to create an Aggregate Query. This will add a Totals Row to the Query Builder with the words "Group By" under each field.
Under the third field you added, change the Totals row value from "Group By' to "Count".
View your results.

This will give you every single SiteName/EquipmentName combination you have with the count of how many records have each combination.

Wow that is great and I think it's getting close to exactly what I want. Attached is an image of the results. So how do I have the query compress all the SiteNames so instead of listing sites multiple times it just lists them once and along with that how do I make it sum/count(?) the 1s, in the SS you can see that Green River has 5 power supplies, I want it to say 5.

Thank you for the quick responses. not sure why everything went wonky with the image.

[benjammin]

This is the old spreadsheet that needed to be manually updated but this is the result I want out of my database so we'll never have to manually count anything again.

[JoeM]

Wow that is great and I think it's getting close to exactly what I want. Attached is an image of the results. So how do I have the query compress all the SiteNames so instead of listing sites multiple times it just lists them once and along with that how do I make it sum/count(?) the 1s, in the SS you can see that Green River has 5 power supplies, I want it to say 5.

If I am interpreting your images correctly, just do like I recommended. Group by your "SiteName" field, and use "Sum" under every other field.

[benjammin]

JoeM,

Perfect! Thanks, I had too many fields that didn't matter. All is well now. Much appreciated.