Report totals depending on value in field

[Thel]

I want to add the number of "males" number of females, break by department
and also have a grand total for each. Does anyone have any sample code to do
this.
Thanks,
Thel

 

[KARL DEWEY]

Use these two queries --
qryMale_Female --
SELECT [Department], IIF([Gender] = "Male", 1, 0) AS [M], IIF([Gender] =
"Female", 1, 0) AS [F]
FROM [YourTable]
GROUP BY [Department]
UNION ALL "All Departments" AS [Department], IIF([Gender] = "Male", 1, 0)
AS [M], IIF([Gender] = "Female", 1, 0) AS [F]
FROM [YourTable]
GROUP BY [Department];

SELECT [Department], Sum([M]) AS [Males], Sum([F]) AS [Females]
FROM [qryMale_Female]
GROUP BY [Department];

 

[Thel]

I copied your code and tried to run, got this message>
You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)
I am trying to make these totals on a report in Access.
Thanks,
Thel

Use these two queries --
qryMale_Female --
SELECT [Department], IIF([Gender] = "Male", 1, 0) AS [M], IIF([Gender] =
"Female", 1, 0) AS [F]
FROM [YourTable]
GROUP BY [Department]
UNION ALL "All Departments" AS [Department], IIF([Gender] = "Male", 1, 0)
AS [M], IIF([Gender] = "Female", 1, 0) AS [F]
FROM [YourTable]
GROUP BY [Department];

SELECT [Department], Sum([M]) AS [Males], Sum([F]) AS [Females]
FROM [qryMale_Female]
GROUP BY [Department];

--
Build a little, test a little.

I want to add the number of "males" number of females, break by department
and also have a grand total for each. Does anyone have any sample code to do
this.
Thanks,
Thel

 

[KARL DEWEY]

Post back how you modified the SQL.
--
Build a little, test a little.

I copied your code and tried to run, got this message>
You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)
I am trying to make these totals on a report in Access.
Thanks,
Thel

Use these two queries --
qryMale_Female --
SELECT [Department], IIF([Gender] = "Male", 1, 0) AS [M], IIF([Gender] =
"Female", 1, 0) AS [F]
FROM [YourTable]
GROUP BY [Department]
UNION ALL "All Departments" AS [Department], IIF([Gender] = "Male", 1, 0)
AS [M], IIF([Gender] = "Female", 1, 0) AS [F]
FROM [YourTable]
GROUP BY [Department];

SELECT [Department], Sum([M]) AS [Males], Sum([F]) AS [Females]
FROM [qryMale_Female]
GROUP BY [Department];

--
Build a little, test a little.

I want to add the number of "males" number of females, break by department
and also have a grand total for each. Does anyone have any sample code to do
this.
Thanks,
Thel

 

[Thel]

I went into query builder and typed the code there.

Use these two queries --
qryMale_Female --
SELECT [Department], IIF([Gender] = "Male", 1, 0) AS [M], IIF([Gender] =
"Female", 1, 0) AS [F]
FROM [YourTable]
GROUP BY [Department]
UNION ALL "All Departments" AS [Department], IIF([Gender] = "Male", 1, 0)
AS [M], IIF([Gender] = "Female", 1, 0) AS [F]
FROM [YourTable]
GROUP BY [Department];

SELECT [Department], Sum([M]) AS [Males], Sum([F]) AS [Females]
FROM [qryMale_Female]
GROUP BY [Department];

--
Build a little, test a little.

I want to add the number of "males" number of females, break by department
and also have a grand total for each. Does anyone have any sample code to do
this.
Thanks,
Thel

 

[KARL DEWEY]

Did you solve your problem?

If not, post the SQL that gave you the error.

--
Build a little, test a little.

I went into query builder and typed the code there.

Use these two queries --
qryMale_Female --
SELECT [Department], IIF([Gender] = "Male", 1, 0) AS [M], IIF([Gender] =
"Female", 1, 0) AS [F]
FROM [YourTable]
GROUP BY [Department]
UNION ALL "All Departments" AS [Department], IIF([Gender] = "Male", 1, 0)
AS [M], IIF([Gender] = "Female", 1, 0) AS [F]
FROM [YourTable]
GROUP BY [Department];

SELECT [Department], Sum([M]) AS [Males], Sum([F]) AS [Females]
FROM [qryMale_Female]
GROUP BY [Department];

--
Build a little, test a little.

I want to add the number of "males" number of females, break by department
and also have a grand total for each. Does anyone have any sample code to do
this.
Thanks,
Thel

 

[Thel]

SELECT [Dept#], IIF([Gender] = "Male", 1, 0) AS [M], IIF([Gender] =
"Female", 1, 0) AS [F]
FROM [queryeeoc]
GROUP BY [Dept#]
UNION ALL SELECT"All Departments" AS [Dept#], IIF([Gender] = "Male", 1, 0)
AS [M], IIF([Gender] = "Female", 1, 0) AS [F]
FROM [queryeeoc]
GROUP BY [Dept#];

Did you solve your problem?

If not, post the SQL that gave you the error.

--
Build a little, test a little.

I went into query builder and typed the code there.

Use these two queries --
qryMale_Female --
SELECT [Department], IIF([Gender] = "Male", 1, 0) AS [M], IIF([Gender] =
"Female", 1, 0) AS [F]
FROM [YourTable]
GROUP BY [Department]
UNION ALL "All Departments" AS [Department], IIF([Gender] = "Male", 1, 0)
AS [M], IIF([Gender] = "Female", 1, 0) AS [F]
FROM [YourTable]
GROUP BY [Department];

SELECT [Department], Sum([M]) AS [Males], Sum([F]) AS [Females]
FROM [qryMale_Female]
GROUP BY [Department];

--
Build a little, test a little.


:

I want to add the number of "males" number of females, break by department
and also have a grand total for each. Does anyone have any sample code to do
this.
Thanks,
Thel