COUNT, LIKE in a query

[Deirdre Marie]

Good afternoon~

I have a table which contains information downloaded from an AS400 system. From this table I've created a query for the information I wish to use in a future report.

One of the fields is called Canteen and contains varied entries. There are 3 Types of Canteens, AR SP and CH. Unfortunately the entries in the field Canteen arent always labelled as such. It can be determined that any entry which is "AR*" is an AR Canteen. Any entry which is "SP*" is a SP Canteen, and any entry which does NOT start with AR* or SP* is a CH Canteen.

Now, what I need my query to do is look at the Field Canteen and COUNT the number of AR, SP, and CH Canteens. I tried creating new entries in the query using the COUNT function along with LIKE. I've also tried using IF to return a True/False value. Unfortunately, my skills have not progressed in ACCESS to do as I want and all my attempts have been futile.

Any assistance is greatly appreciated.

 

[Alexandre]

It's been a long time since I last got a right to a Snoopy dance.
Try:

SELECT
IIf(InStr([YourFieldName],"AR")<>0, "AR ", IIf(InStr([YourFieldName],"SP")<>0, "SP", "CH"))
   AS Category,
Count([Category])
   AS Total
FROM YourTableName
GROUP BY IIf(InStr([YourFieldName],"AR")<>0, "AR ", IIf(InStr([YourFieldName],"SP")<>0, "SP", "CH"));

Note that the above assumes that all that is not AR or SP - like is CH like.

Sooooo... Is this a winning ticket?
(OK this a bit of an ugly and not most efficient query sample... but it works )

 

[RV]

Try this:

SELECT Count(IIF(Mid(Canteen,1,2) ="AR", Canteen)) AS "AR Canteens", Count(IIF(Mid(Canteen,1,2) = "SP", Canteen)) AS "SP Canteens",
Count(IIF(Mid(Canteen,1,2) NOT IN ("AR", "SP"), Canteen)) AS "CH Canteens"
FROM Canteen;

You'll have to consider whether Canteen can obtain zero lenght ""or null.

RV

 

[Deirdre Marie]

Alexandre and RV, thank you much for your time and replies. However, I seem to be doing something incorrectly.

I wanted to be able to do this in my Query. Are these codes written for the eventual Report from this query?

sidenote: Alexandre, I think you may be pleased with how much I've learned since our "first meeting" - still not 1/10th of your skill in Access, but I've come a long way

 

[RV]

>I wanted to be able to do this in my Query. Are these codes written for the eventual Report from this query?<

1) you can copy and paste the basic statement in a query. Make a new query using the SQL view.
Adapt it to your table and column names
2) you can a query as a base for a report.
However, I can't tell you whether the query can be used for your report. That depends on your report definition.

RV

 

[Deirdre Marie]

Snoopy Dance

Alexandre and RV

It has been a bit since I posted, but I just wanted to let you both know that I was able to use the SQL to get the information I needed. You may see a few more questions form me in the near future as I now have the time to work on this project.

Thank you both ever so much.