|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 24th, 2009, 08:38 PM
posted to microsoft.public.access.reports
|
|||
|
|||
Report totals depending on value in field
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 
|
|
#2
July 25th, 2009, 12:34 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report totals depending on value in field
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" wrote: 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
|
|
#3
July 26th, 2009, 09:58 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report totals depending on value in field
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" wrote: 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" wrote: 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
|
|
#4
July 27th, 2009, 05:30 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report totals depending on value in field
Post back how you modified the SQL.
-- Build a little, test a little. "Thel" wrote: 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" wrote: 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" wrote: 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
|
|
#5
July 29th, 2009, 09:33 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report totals depending on value in field
I went into query builder and typed the code there.
"KARL DEWEY" wrote: 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" wrote: 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
|
|
#6
July 29th, 2009, 10:09 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report totals depending on value in field
Did you solve your problem?
If not, post the SQL that gave you the error. -- Build a little, test a little. "Thel" wrote: I went into query builder and typed the code there. "KARL DEWEY" wrote: 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" wrote: 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
|
|
#7
August 1st, 2009, 06:39 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report totals depending on value in field
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" wrote: Did you solve your problem? If not, post the SQL that gave you the error. -- Build a little, test a little. "Thel" wrote: I went into query builder and typed the code there. "KARL DEWEY" wrote: 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" wrote: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
