查询按地区计算活跃员工的性别 [英] Query to Count Gender of Active Employees by region

问题描述

我正在使用Access 2007和Vista。我正在试图弄清楚如何编写一个查询来获取GENDER，RETIREMENT DATE和REGION字段中的信息，以获得每个地区活跃（未退休）的传教士数量。


这是我到目前为止所做的，但它为每个性别返回零值。如果我取出男性 - 它的工作原理。

I''m using Access 2007 with Vista. I''m trying to figure out how to write a query to take information in the fields GENDER, RETIREMENT DATE, and REGION to get a count of the number of missionaries that are active (not retired) by each region.

This is what I haev so far, but it returns a zero value for each gender. If I take out the male - it works.

展开 | 选择 | Wrap | 行号

推荐答案

这应该比你在尝试什么：

This should work a lot better than what you are trying:

展开 | 选择 | Wrap | 行号

这应该比你正在尝试的要好很多：


SELECT（SELECT COUNT（*）FROM tblMissionaries WHERE tblMissionaries.Gender =" Female" ）AS TotalFemale，

（SELECT COUNT（*）FROM tblMissionaries WHERE tblMissionaries.Gender =" Male"）AS TotalMale


您正在创建两个子查询获取数据，然后将其汇总到最终答案。

This should work a lot better than what you are trying:

SELECT (SELECT COUNT(*) FROM tblMissionaries WHERE tblMissionaries.Gender="Female") AS TotalFemale,
(SELECT COUNT(*) FROM tblMissionaries WHERE tblMissionaries.Gender="Male") AS TotalMale

You are creating two subqueries that are getting the data and then rolling it up to the final answer.

我收到错误消息查询输入必须包含至少一个表或查询

I get an error message "Query input must contain at least one table or query"

嗨。您需要包含SQL语句的其他部分 - FROM子句等等 - 前面的海报可能已经包含了这些部分。


但是，有一个更简单的方法来统计男性和女性 - 从查询编辑器中选择查看，总计以打开分组和总计，然后按性别对数据进行分组并添加计算字段以获取每个性别的总数。


这就是

Hi. You would need to include the other parts of the SQL statement - the FROM clause and so on as well - which the previous poster may have assumed would be included.

However, there is an easier way to total the males and females - from the query editor select View, Totals to turn on grouping and totalling, then group your data by gender and add a computed field to get the total number for each gender.

This is along the lines of

展开 | 选择 | Wrap | 行号

这篇关于查询按地区计算活跃员工的性别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！