是/否字段更改为数据字段 [英] Yes/No field change to a data field
问题描述
问候;
我创建了一个呼叫日志数据库。数据表中90％的字段是是/否问题，员工要么选择（是），要么留空。其他两个字段是员工姓名和日期。问题;现在我需要更改是数据计数，以允许我创建报告和其他计算。我已经尝试在查询中添加了一个yes / no组的字段并再次添加它并计算它...但是当我为同一查询中的6个是/否字段执行此操作时，结果不会感。我无法进行单独的查询，因为我可以一起加入查询报告。
有什么想法吗？
谢谢，
Steve
Greetings;
I have created a call log database. 90% of the fields within the data table are yes/no questions for which the employee either selects (for yes) or leaves blank for No. The other two fields are employee name and date. Issue; now I need to change the "yes" data to counts to allow me to create reports and other calculations. I have tried in queries by adding the one field of yes / no group on it and adding it a second time and counting it...but when i do this for the 6 yes /no fields in the same query, the results dont make sense. I cant make seperate queries as I can join the queries together for the report.
Any thoughts?
Thank you,
Steve
推荐答案
问候;
我创建了一个呼叫日志数据库。数据表中90％的字段是是/否问题，员工要么选择（是），要么留空。其他两个字段是员工姓名和日期。问题;现在我需要更改是数据计数，以允许我创建报告和其他计算。我已经尝试在查询中添加了一个yes / no组的字段并再次添加它并计算它...但是当我为同一查询中的6个是/否字段执行此操作时，结果不会感。我无法进行单独的查询，因为我可以一起加入查询报告。
有什么想法吗？
谢谢，
史蒂夫
Greetings;
I have created a call log database. 90% of the fields within the data table are yes/no questions for which the employee either selects (for yes) or leaves blank for No. The other two fields are employee name and date. Issue; now I need to change the "yes" data to counts to allow me to create reports and other calculations. I have tried in queries by adding the one field of yes / no group on it and adding it a second time and counting it...but when i do this for the 6 yes /no fields in the same query, the results dont make sense. I cant make seperate queries as I can join the queries together for the report.
Any thoughts?
Thank you,
Steve
你不能一次做多个问题。正如您已经看到的那样，分组不能正常工作。您将不得不进行单独的查询，然后组合查询或在报告中使用DCount。
You can''t do this for more than one question at a time. The grouping won''t work correctly as you''ve already seen. You''ll have to make separate queries and then combine the queries or use DCount instead on the report.
Greetings;
我创建了一个呼叫日志数据库。数据表中90％的字段是是/否问题，员工要么选择（是），要么留空。其他两个字段是员工姓名和日期。问题;现在我需要更改是数据计数，以允许我创建报告和其他计算。我已经尝试在查询中添加了一个yes / no组的字段并再次添加它并计算它...但是当我为同一查询中的6个是/否字段执行此操作时，结果不会感。我无法进行单独的查询，因为我可以一起加入查询报告。
有什么想法吗？
谢谢，
史蒂夫
Greetings;
I have created a call log database. 90% of the fields within the data table are yes/no questions for which the employee either selects (for yes) or leaves blank for No. The other two fields are employee name and date. Issue; now I need to change the "yes" data to counts to allow me to create reports and other calculations. I have tried in queries by adding the one field of yes / no group on it and adding it a second time and counting it...but when i do this for the 6 yes /no fields in the same query, the results dont make sense. I cant make seperate queries as I can join the queries together for the report.
Any thoughts?
Thank you,
Steve
史蒂夫
如果我正确地读你，我假设你有一个单独的表，包括让我们说RecordID（自动编号） ，然后将员工姓名作为字段，然后将记录的日期作为字段，然后在同一行中的任意数量的其他字段为是/否字段（复选框）。
如果您已经进行了验证以确保每次都有正确的员工进入行（即下拉列表或其他一些机制），并且您只希望在给定的每列中对每个事件的值为YES（TRUE）要在两个日期参数之间的行（记录）中列出的行数，则以下SQL语句可能对您有所帮助。作为一个示例，它明确依赖于一个名为tblYourTableName的表和一个名为tblDateCriteria的另一个表
tblDateCriteria与tblYourTableName结合在一起被称为CROSS JOIN（诀窍是tblDateCriteria应该只有由你填充一行数据，应该是两个日期（日期来自和日期）
（诀窍是这一行数据基本上提供了标准''提供''查询及其日期参数，其中您计算存在的YES的数量，以及员工名称的GROUPING，用于参数的日期和日期之间存在的记录。这将为您提供x数字的矩阵显示如果你将SQL保存为单个查询，那么你只有一个查询而不是多个查询吗？
不知道你的字段名本身但是下面的SQL工作.....如果你像我提到的那样创建表并抛出一些样本您将看到的数据将查看查询本质上如何为每列使用DERIVED（子查询）计数。你可能会注意到SQL中的单词SURVEY？这称为ALIASING，它只是将表tblYourTableName别名为SURVEY。
这个查询语法（如果你在查询设计窗口中思考一下），你会看到它消除了使用DCOUNT函数的需要（如果你在分割数据库中附加了表格）比SQL慢..
SELECT DISTINCT Survey.EmployeeName，
（SELECT Count（*）FROM tblYourTableName，tblDateCriteria AS DateCriteria
WHERE（（Survey.SurveyDate）[DateCriteria]。[SurveyDateCriteriaFrom]和[DateCriteria]。[SurveyDateCriteriaTo]））AND EmployeeName = Survey.EmployeeName和Field1 = true）AS YesCount1，（SELECT Count（*）FROM tblYourTableName，tblDateCriteria AS DateCriteria
WHERE（（Survey.SurveyDate）[DateCriteria]。[SurveyDateCriteriaFrom]和[DateCriteria]之间]。[SurveyDateCriteriaTo]））AND EmployeeName = Survey.EmployeeName和Field2 = true）AS YesCount2，（SELECT Count（*）FROM tblYourTableName，tblDateCr iteria AS DateCriteria
WHERE（（Survey.SurveyDate）[DateCriteria]。[SurveyDateCriteriaFrom]和[DateCriteria]。[SurveyDateCriteriaTo]））AND EmployeeName = Survey.EmployeeName和Field3 = true）AS YesCount3
FROM tblYourTableName AS Survey，tblDateCriteria AS DateCriteria
WHERE（（Survey.SurveyDate）[DateCriteria]。[SurveyDateCriteriaFrom]和[DateCriteria]。[SurveyDateCriteriaTo]））
ORDER BY Survey.EmployeeName;
问候
Jim
Steve
If I am reading you correctly I am assuming you have a single table which consists of lets say RecordID (an autonumber), and then the employee name as a field and then the date of the record as a field and then any number of other fields in the same row that are Yes/No fields (tickboxes).
If you have validation in place to make sure the correct employee goes into the row each time (ie a dropdown or some other mechanism) and you simply wish to COUNT every event of a YES value (TRUE) in each column over a given number of rows to be listed in row (records) between two date parameters then the following SQL Statement might help you. As a sample it relies explicitly on a table called tblYourTableName and ANOTHER table called tblDateCriteria
tblDateCriteria is JOINED with tblYourTableName in whats is known as a CROSS JOIN (the trick is that tblDateCriteria should only ever be populated by you with one row of data and that should be two dates (Date from and a Date to)
(The trick is that this one row of data essentially provides the criteria to ''feed'' the query with its date parameters where you are COUNTing the number of YESes that exist and GROUPING by the employee name for records existing between the date from and date to parameters. This will give you a matrix display of x number of records with columns taboot. If you save the SQL below as a single query you ONLY have one query not multiples of queries?
The do not know your field names per se however the following SQL works..... provided you create the tables as I have mentioned and throw some sample data in you will see how the query essentially uses DERIVED (sub query) counts for each column. You may notice the word SURVEY within the SQL? this is called ALIASING and it is merely aliasing the table tblYourTableName as SURVEY.
This query syntax (which if you ponder on it in the query design window for a moment) you will see that it eradicates the need to use the DCOUNT function which (if you have attached tables in a split database) is slower versus SQL
SELECT DISTINCT Survey.EmployeeName,
(SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria
WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and Field1=true) AS YesCount1, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria
WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and Field2=true) AS YesCount2, (SELECT Count(*) FROM tblYourTableName , tblDateCriteria AS DateCriteria
WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo])) AND EmployeeName=Survey.EmployeeName and Field3=true) AS YesCount3
FROM tblYourTableName AS Survey, tblDateCriteria AS DateCriteria
WHERE (((Survey.SurveyDate) Between [DateCriteria].[SurveyDateCriteriaFrom] And [DateCriteria].[SurveyDateCriteriaTo]))
ORDER BY Survey.EmployeeName;
Regards
Jim
Jim;
感谢您的详细解释....问题我将需要使用日期标准如何设置....我需要在运行之前创建一个查询你提供的SQL？然后我需要在报告上显示信息......
谢谢，
Steve
Jim;
Thank you for the detailed explanation....question I will need to use a date criteria how do I set that up....do i need to create a query prior to running the sql you provided? Then I need to display the information on a report...
Thank you,
Steve
这篇关于是/否字段更改为数据字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
