Totals with WHERE left() criteria

[Hugh self taught]

Hi Brainy people,

I have fields Male & Register & I want to total the number of males (yes/no
field) where the left([Register],2) = "GU"

I have to create the same query for 11 different combinations "GU", "FS",
"KZ" etc & then I need to combine those results to look something like
GU Male 302
GU 401
FS Male 254
FS 287

Does that make sense? Is there a shorter route to get the desired result? My
brain is tired & I've been out of touch with access for a little while again
so any assistance is greatly appreciated.

Regards
Hugh

 

[John_G via AccessMonster.com]

Hi -

You can use a simple totals query. Three fields-

Left([register],2) with Group By
[Male] with Group By and
[Male] or any field with Count

If you want to see "Male" instead of Yes / No, the second column can be

iif([Male],"Male", " ")

I do have one question, though -
If not "Male" then.....what??

HTH

John

Hi Brainy people,

I have fields Male & Register & I want to total the number of males (yes/no
field) where the left([Register],2) = "GU"

I have to create the same query for 11 different combinations "GU", "FS",
"KZ" etc & then I need to combine those results to look something like
GU Male 302
GU 401
FS Male 254
FS 287

Does that make sense? Is there a shorter route to get the desired result? My
brain is tired & I've been out of touch with access for a little while again
so any assistance is greatly appreciated.

Regards
Hugh

 

[Marshall Barton]

I have fields Male & Register & I want to total the number of males (yes/no
field) where the left([Register],2) = "GU"

I have to create the same query for 11 different combinations "GU", "FS",
"KZ" etc & then I need to combine those results to look something like
GU Male 302
GU 401
FS Male 254
FS 287

One query should be able to do that, but only one line per
register. In addition to what John suggested, You can count
the total records using the expression Count(*) and the
records with male = True/Yes by using the expression
Sum(IIf(Male,1,0))

In SQL view, it would look like:
SELECT Left([Register],2) As Reg2, "Male" As Males,
Sum(IIf(Male,1,0)) As TotalMale,
Count(*) As All
FROM yourtable
GROUP BY Left([Register],2)

The results would look like:
GU Male 302 401
FS Male 254 287

 

[KARL DEWEY]

Try this --
SELECT Left([Register],2) AS Reg_2, IIF([Male] = -1, "Males", "") AS Males,
Count([Male]) AS Quanity
FROM YourTable
GROUP BY Left([Register],2), IIF([Male] = -1, "Males", "");

 

[Hugh self taught]

Hi Karl, John & Marshall,

I tried Karl's sql & it works great. John beat me to the next question
because where it is not Male = true/yes then it's female. My ultimate
objective is to have the results available for a report & there I would like
to list Males in GU = xx Females in GU = yy & so on. How would I modify the
code you've given me to reflect in a way that I could achieve that?

Try this --
SELECT Left([Register],2) AS Reg_2, IIF([Male] = -1, "Males", "") AS Males,
Count([Male]) AS Quanity
FROM YourTable
GROUP BY Left([Register],2), IIF([Male] = -1, "Males", "");

--
Build a little, test a little.

Hi Brainy people,

I have fields Male & Register & I want to total the number of males (yes/no
field) where the left([Register],2) = "GU"

I have to create the same query for 11 different combinations "GU", "FS",
"KZ" etc & then I need to combine those results to look something like
GU Male 302
GU 401
FS Male 254
FS 287

Does that make sense? Is there a shorter route to get the desired result? My
brain is tired & I've been out of touch with access for a little while again
so any assistance is greatly appreciated.

Regards
Hugh

 

[Hugh self taught]

I need to go one level more as well. Can I Get a "Grand Total" of Males &
Females from the same query?

Hi Karl, John & Marshall,

I tried Karl's sql & it works great. John beat me to the next question
because where it is not Male = true/yes then it's female. My ultimate
objective is to have the results available for a report & there I would like
to list Males in GU = xx Females in GU = yy & so on. How would I modify the
code you've given me to reflect in a way that I could achieve that?

Try this --
SELECT Left([Register],2) AS Reg_2, IIF([Male] = -1, "Males", "") AS Males,
Count([Male]) AS Quanity
FROM YourTable
GROUP BY Left([Register],2), IIF([Male] = -1, "Males", "");

--
Build a little, test a little.

Hi Brainy people,

I have fields Male & Register & I want to total the number of males (yes/no
field) where the left([Register],2) = "GU"

I have to create the same query for 11 different combinations "GU", "FS",
"KZ" etc & then I need to combine those results to look something like
GU Male 302
GU 401
FS Male 254
FS 287

Does that make sense? Is there a shorter route to get the desired result? My
brain is tired & I've been out of touch with access for a little while again
so any assistance is greatly appreciated.

Regards
Hugh

 

[Marshall Barton]

I need to go one level more as well. Can I Get a "Grand Total" of Males &
Females from the same query?

Isn't that what my query does?

 

[KenSheridan via AccessMonster.com]

You should be able to get the grand totals in a query by means of subqueries,
e.g.

SELECT LEFT(Register,2) As Category,
IIF(Male,"Male","Female") AS Gender,
COUNT(*) AS CountByCategory,
(SELECT COUNT(*)
FROM YourTable
WHERE Male) AS GrandTotalMale,
(SELECT COUNT(*)
FROM YourTable
WHERE NOT Male) AS GrandTotalFemale
FROM YourTable
GROUP BY LEFT(Register,2),
IIF(Male,"Male","Female");

But as this is the basis for a report you could, dispensing with the
subqueries, do it in the report itself with controls in the report footer
with ControlSource properties of:

=Sum(IIf(Male,1,0))
and:
=Sum(IIf(Male,0,1))

Ken Sheridan
Stafford, England

I need to go one level more as well. Can I Get a "Grand Total" of Males &
Females from the same query?

Hi Karl, John & Marshall,

[quoted text clipped - 28 lines]

 

[KARL DEWEY]

Try this --
SELECT Left([Register],2) AS Reg_2, IIF([Male] = -1, "Males", "Females") AS
Gender, Count([Male]) AS Quanity
FROM YourTable
GROUP BY Left([Register],2), IIF([Male] = -1, "Males", "Females")
UNION ALL SELECT "Total", IIF([Male] = -1, "Males", "Females") AS Gender,
Count([Male]) AS Quanity
GROUP BY "Total", IIF([Male] = -1, "Males", "Females");

--
Build a little, test a little.

I need to go one level more as well. Can I Get a "Grand Total" of Males &
Females from the same query?

Hi Karl, John & Marshall,

I tried Karl's sql & it works great. John beat me to the next question
because where it is not Male = true/yes then it's female. My ultimate
objective is to have the results available for a report & there I would like
to list Males in GU = xx Females in GU = yy & so on. How would I modify the
code you've given me to reflect in a way that I could achieve that?

Try this --
SELECT Left([Register],2) AS Reg_2, IIF([Male] = -1, "Males", "") AS Males,
Count([Male]) AS Quanity
FROM YourTable
GROUP BY Left([Register],2), IIF([Male] = -1, "Males", "");

--
Build a little, test a little.


:

Hi Brainy people,

I have fields Male & Register & I want to total the number of males (yes/no
field) where the left([Register],2) = "GU"

I have to create the same query for 11 different combinations "GU", "FS",
"KZ" etc & then I need to combine those results to look something like
GU Male 302
GU 401
FS Male 254
FS 287

Does that make sense? Is there a shorter route to get the desired result? My
brain is tired & I've been out of touch with access for a little while again
so any assistance is greatly appreciated.

Regards
Hugh

 

[Hugh self taught]

Hi Karl,

Now I'm getting an error "The SELECT Statement contains a reserved word or
an argument name that misspelled or missing, or the punctuation is incorrect"

I isolated it to the UNION ALL SELECT statements as the sql runs fine if I
remove the code from that select statement on. It must be something over my
head because the code is identical to that of the first SELECT statement
except for the "Total", parameter. Can you suggest what's wrong?

Try this --
SELECT Left([Register],2) AS Reg_2, IIF([Male] = -1, "Males", "Females") AS
Gender, Count([Male]) AS Quanity
FROM YourTable
GROUP BY Left([Register],2), IIF([Male] = -1, "Males", "Females")
UNION ALL SELECT "Total", IIF([Male] = -1, "Males", "Females") AS Gender,
Count([Male]) AS Quanity
GROUP BY "Total", IIF([Male] = -1, "Males", "Females");

--
Build a little, test a little.

I need to go one level more as well. Can I Get a "Grand Total" of Males &
Females from the same query?

Hi Karl, John & Marshall,

I tried Karl's sql & it works great. John beat me to the next question
because where it is not Male = true/yes then it's female. My ultimate
objective is to have the results available for a report & there I would like
to list Males in GU = xx Females in GU = yy & so on. How would I modify the
code you've given me to reflect in a way that I could achieve that?

:

Try this --
SELECT Left([Register],2) AS Reg_2, IIF([Male] = -1, "Males", "") AS Males,
Count([Male]) AS Quanity
FROM YourTable
GROUP BY Left([Register],2), IIF([Male] = -1, "Males", "");

--
Build a little, test a little.


:

Hi Brainy people,

I have fields Male & Register & I want to total the number of males (yes/no
field) where the left([Register],2) = "GU"

I have to create the same query for 11 different combinations "GU", "FS",
"KZ" etc & then I need to combine those results to look something like
GU Male 302
GU 401
FS Male 254
FS 287

Does that make sense? Is there a shorter route to get the desired result? My
brain is tired & I've been out of touch with access for a little while again
so any assistance is greatly appreciated.

Regards
Hugh

 

[John Spencer]

Try the following. You did not have a FROM clause in the second query. Also
if you want a grand total, you need to return one value for Gender (Null or
"BOTH" or ...)

SELECT Left([Register],2) AS Reg_2
, IIF([Male] = -1, "Males", "Females") AS Gender
, Count([Male]) AS Quantity
FROM YourTable
GROUP BY Left([Register],2)
, IIF([Male] = -1, "Males", "Females")
UNION ALL
SELECT "Total"
, Null AS Gender
, Count([Male]) AS Quantity
FROM YourTable
GROUP BY "Total", IIF([Male] = -1, "Males", "Females")

An alternative query that returns all three values in one row
SELECT Left([Register],2) AS Reg_2
, Abs(Sum([Male] = -1)) AS CountMales
, Abs(Sum([Male] =0)) as CountFemales
, Count([Male]) AS CountAll
FROM [YourTable]
GROUP BY Left([Register],2)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Hugh self taught]

Vandergast pointed out that the 2nd FROM statement after the 2nd SELECT was
missing

Hi Karl,

Now I'm getting an error "The SELECT Statement contains a reserved word or
an argument name that misspelled or missing, or the punctuation is incorrect"

I isolated it to the UNION ALL SELECT statements as the sql runs fine if I
remove the code from that select statement on. It must be something over my
head because the code is identical to that of the first SELECT statement
except for the "Total", parameter. Can you suggest what's wrong?

Try this --
SELECT Left([Register],2) AS Reg_2, IIF([Male] = -1, "Males", "Females") AS
Gender, Count([Male]) AS Quanity
FROM YourTable
GROUP BY Left([Register],2), IIF([Male] = -1, "Males", "Females")
UNION ALL SELECT "Total", IIF([Male] = -1, "Males", "Females") AS Gender,
Count([Male]) AS Quanity
GROUP BY "Total", IIF([Male] = -1, "Males", "Females");

--
Build a little, test a little.

I need to go one level more as well. Can I Get a "Grand Total" of Males &
Females from the same query?

:

Hi Karl, John & Marshall,

I tried Karl's sql & it works great. John beat me to the next question
because where it is not Male = true/yes then it's female. My ultimate
objective is to have the results available for a report & there I would like
to list Males in GU = xx Females in GU = yy & so on. How would I modify the
code you've given me to reflect in a way that I could achieve that?

:

Try this --
SELECT Left([Register],2) AS Reg_2, IIF([Male] = -1, "Males", "") AS Males,
Count([Male]) AS Quanity
FROM YourTable
GROUP BY Left([Register],2), IIF([Male] = -1, "Males", "");

--
Build a little, test a little.


:

Hi Brainy people,

I have fields Male & Register & I want to total the number of males (yes/no
field) where the left([Register],2) = "GU"

I have to create the same query for 11 different combinations "GU", "FS",
"KZ" etc & then I need to combine those results to look something like
GU Male 302
GU 401
FS Male 254
FS 287

Does that make sense? Is there a shorter route to get the desired result? My
brain is tired & I've been out of touch with access for a little while again
so any assistance is greatly appreciated.

Regards
Hugh