Hi there,

i don't know if i'm in right Ng, but i'd ask you something bout access
database query.

I have got a db with a table so

ID
ITALY (boolean)
GREECE boolean

Ok. I have to create a query to calculate how many records are true for
ITALY and the same for Greece. I have already written a double query
that is this:
select count(Italy) as IT from XXX where Italy = True
and the same for greece.
Can someone tell me how write this query one time for both?

Thanks
(i hope to be clear!)

On 03/04/2011 04:38 PM, Giovanni Militano wrote:
Have you tried "microsoft.public.access.queries" yet?

Look up the SQL clause "GROUP BY" from the documentation.

On 2011-03-04 16:38, Giovanni Militano wrote:
Some access group or a general database group such as comp.databases
would probably have been a better NG

[...]

First, consider redesigning your data model. It looks as if your table
is some kind of pivot table.

select Country, count(*) from (
select case when Italy then 'Italy' end as Country from IT
union all
select case when Greece then 'Greece' end as Country from IT
) as T
where Country is not null
group by Country;

/Lennart

El 04/03/2011 16:38, Giovanni Militano escribió/wrote:
In MySQL you can just use a CASE statement, supposing you have
TINYINT(1) columns:

SELECT
COUNT(CASE italy WHEN TRUE THEN 1 END) AS num_true_italy,
COUNT(CASE greece WHEN TRUE THEN 1 END) AS num_true_greece
FROM xxx

Whether MS Access supports this is something I don't know.


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

El 07/03/2011 9:28, "Álvaro G. Vicario" escribió/wrote:
I'm sleepy... I you have have TINYINT(1) columns with values 0 and 1,
you can simply make a sum:

SELECT
SUM(italy) AS num_true_italy,
SUM(greece) AS num_true_greece
FROM xxx

Of course, the CASE approach tends to be better since MySQL won't
prevent you from inserting values greater than 1...




--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--