Count if??

[vicsaccess]

ok, I've have some good luck with the great people on this forum giving me ideas. I need another though. I have two columns in a table, one has the thickness of a product that is in decimal (.115) and the next column is the width of the product. what i'm being ask is how many of this thickness range and width range do we use per month. if it was excel I would use some sort of "countif" function but access does not have that. what I've found so far is that I probably need an infinite if statement. my thoughts so far are to use something like-
exp: sum((iif(thickness >.25 and <.35,1,0) and iif(width >48 and <60,1,0))
obviously my thoughts are not working because i'm here asking for help. here's an example of the data in the tables

Mill Thickness	Width
0.126	60
0.126	72
0.127	48
0.112	60
0.097	60
0.97	60
0.142	60
0.171	48
0.1775	60
0.112	48
0.12	60

how do I count the number of products between the thickness of .17 and .18 between the widths of 60 and 71?

[ranman256]

I would build a union query to batch my thickness ranges...count or sum or whatever

SELECT "112-126" AS Batch, Sum(tTbl.Width) AS SumOfWidth
FROM tTbl
WHERE (((tTbl.[Mill Thickness]) Between 0.112 And 0.126))
GROUP BY "112-126";

union

SELECT "25-35"" AS Batch, Sum(tTbl.Width) AS SumOfWidth
FROM tTbl
WHERE (((tTbl.[Mill Thickness]) Between 0.25 And 0.35))
GROUP BY "25-35";

[vicsaccess]

i'm not good with SQL. I was trying to stay within my knowledge of a normal query just for the ease of being able to fix it later. if i'm reading that right you're batching both as sum of width?

[orange]

Your infinite if statement is really an ImmediateIf. see IIF

You must explicitly state the thickness and width each time.


SELECT Sum(IIf((thickness>0.25 And thickness<0.35) And (width>48 And width<60),1,0)) AS MyCount
FROM [Your table name];



SELECT Sum(IIf((thickness>0.16 And thickness<0.17) And (width>60 And width<71),1,0)) AS MyCount
FROM [Your table name];

[vicsaccess]

Thanks that looks better than mine. I'll try that.

[June7]

How many range combinations are there?

[vicsaccess]

12 altogether.
.050-.088 less than 60 wide
.050-.088 equal to or greater than 60 wide
.089-.133 less than 60 wide
.089-.133 equal to or greater than 60 wide
.134-.164 less than 60 wide
.134-.164 equal to or greater than 60 wide
.165-.299 less than 60 wide
.165-.299 equal to or greater than 60 wide
.3-.489 less than 60 wide
.3-.489 equal to or greater than 60 wide
.49-.625 less than 60 wide
.49-.625 equal to or greater than 60 wide

[June7]

I always thought it was InlineIf.

Width is a reserved word. Should not use reserved words as names.

Do you want to count the records or sum the dimension?

If you want a query or report to display all group combinations, options:

1. calculate two fields to be group identifiers and use those fields in a GROUP BY aggregate query.

SELECT *, Switch([Thickness]<=0.088,"A", [Thickness]<=0.133,"B", [Thickness]<=0.164,"C", [Thickness]<=0.299,"D", [Thickness]<=0.489,"E", [Thickness]<=0.625,"F", [Thickness]>0.625,"G") AS ThickGrp, IIf([Width]<60,"Less60","GreaterEqual60") AS WidthGrp FROM tablename;

SELECT Count(*) AS CountRecs, ThickGrp, WidthGrp FROM query1 GROUP BY ThickGrp, WidthGrp;

2. Ranman's suggestion would involve 12 SELECT lines in the UNION statement.

[vicsaccess]

is there an expression that I can use in a query instead of sql? not real familiar with sql. when I try the expression in a querry it is returning everything as the highest group?