Count By Suburb

[3dman]

Hi All,

I'm having difficulty counting the number of "Y" and "N" for each suburb.

Details:

The Table -

--------------------------------------------------------
ID- Shop Type-Shop Name-Address1 - Address2 - Suburb - State
--------------------------------------------------------
1 - LP- ExampleName - 1/2 Example St - West Example


The result I need is:

--------------------------------------------------------
Suburb - State - Count No. of "Y" - Count No. of "N"
--------------------------------------------------------
West Example - TT - 4 - 5
East Example - WW - 1 - 0

There are also duplicate suburbs.

Any direction or help is much appreciated

Thank You,

Dave

 

[jal]

Huh? Which column has the Y's and N's that you wish to count?

 

[3dman]

Sorry, I left out one column, it should be:

----------------------------------------------------------------------
ID- Shop Type-Shop Name-Address1 - Address2 - Suburb - State-Yes/No
----------------------------------------------------------------------

Thank You,

Dave

 

[Galaxiom]

In a Y/N field, a yes is recorded as -1 while a no is zero.
Count of Yes is achieved by:
Abs(Sum([fieldname]))

Count of No is achieved by:
Count([fieldname])+Sum([fieldname])

 

[Galaxiom]

Your query will have fields Suburb, State set as Group By and the other two fields derived with the expressions.

Just type them in the field box of the query designer. They will default to Expr1 and Expr2 but you can edit that to whatever you want.

 

[3dman]

Hi Galaxiom,

Thank You so much, it worked very well, I go the results needed.

Thank You,

Dave.

 

[jal]

Obviously Galaxiom is better with the query designer than I am. I only use plain SQL. I'll post my solution in a moment...

 

[jal]

I saved it as Query1 in the attached MDB database. Looks like this:

SELECT YesCount.State, YesCount.Suburb, YesCount.CountOfYs, NoCount.CountOfNs
FROM
(
SELECT State, Suburb, COUNT([Yes/No]) AS CountOfYs FROM TABLE1
WHERE [Yes/No] = 'Y'
GROUP BY State, Suburb
) AS YesCount
INNER JOIN
(
SELECT State, Suburb, COUNT([Yes/No]) AS CountOfNs FROM TABLE1
WHERE [Yes/No] = 'N'
GROUP BY State, Suburb
) AS NoCount
ON NoCount.State = YesCount.State
AND NoCount.Suburb = YesCount.Suburb

 

[3dman]

Hi jal,

I also tried your solution and it works. Your example database "Suburbs.mdb" illustrated the solution very well.


Thank You so much,

Dave.