Report totals depending on value in field

  • Thread starter Thread starter Thel
  • Start date Start date
T

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
 
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];
 
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

KARL DEWEY said:
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.


Thel said:
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
Click to expand...
Click to expand...
 
Post back how you modified the SQL.
--
Build a little, test a little.


Thel said:
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

KARL DEWEY said:
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.


Thel said:
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
Click to expand...
Click to expand...
Click to expand...
 
I went into query builder and typed the code there.

KARL DEWEY said:
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.


Thel said:
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
Click to expand...
Click to expand...
 
Did you solve your problem?

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

--
Build a little, test a little.


Thel said:
I went into query builder and typed the code there.

KARL DEWEY said:
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.


Thel said:
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
Click to expand...
Click to expand...
Click to expand...
 
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#];


KARL DEWEY said:
Did you solve your problem?

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

--
Build a little, test a little.


Thel said:
I went into query builder and typed the code there.

KARL DEWEY said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top