多标准查询 [英] Multi criteria query

问题描述

我有一个数据库，其中所有工作订单项目必须符合某些要求才能符合要求。我需要知道满足这些要求的项目总数。首先，状态必须在COMP或FCOMP中。 <劳动时间> 0第三必须在一个时间范围内。在所有这些工单中的第二部分我需要知道PMINS，pmor，PMPDM，pmreg，PMRT之间工作订单的百分比。不符合要求1，有多少不符合要求2，有多少不符合要求3.可能有变化 - 例如，项目A符合要求1但不符合要求1和3;项目B符合要求1& 3，但不是2等。


这些计数需要根据不符合规定的项目工作订单总数转换为百分比。


如何在查询或报告中设置此信息以显示此信息？


非常感谢您的帮助。我知道我可以用SQL做到这一点，但我不是那么流利的SQL。我知道它是一个独特的选择计数，但我不知道如何在查询设计器中这样做。


ms-access

附加文件

（952.5 KB，60 views）

解决方案

很难知道从哪里开始。


主题在桌面软件下，你有附上一个Excel文件，并进一步提到MS Access。


我觉得你指的是Access，在这种情况下，你需要在哪里使用你的项目？ />

如果您要从Excel转换为Access，您需要在开始解决问题之前规范化数据库。


有很多关于这个主题的文章，但这里有一个简单的入门者。


Phil

我已将excel表转换为access并且有union查询两个查询SELECT Count（*）AS LEW

来自MaximoReportT

WHERE（（（MaximoReportT.Status）=" COMP"）AND（（MaximoReportT.ActualLaborHours）>" 00：00"）和（（MaximoReportT.ActualStartDate）在[输入开始日期]和[输入结束日期]之间））或（（（Maxim） oReportT.Status）=" FCOMP"））;


UNION SELECT Count（*）AS LEW

来自MaximoReportT

WHERE（（WorkType）In（PMINS，PMOR，PMPDM，PMREG，PMRT））AND（（状态）<>CAN）AND（TargetStartDate ）在[输入开始日期]和[输入结束日期]之间;

给了我133/608但现在我需要弄清楚如何将这两个数字变成百分比。

我仍然很清楚你想要什么，所以请回答下面的问题。


1）你是否试图创建一个正常的Access数据库包含表格中的数据和带有文本框，组合框等的表格，与Excel无关？


2）您是否尝试创建一个普通的Access数据库您的表格和表格中的数据与文本框，组合框等，但最初从您的Excel文件中提取 的数据？


3）您是否尝试使用文本框，组合框等创建包含表格和表单中数据的普通Access数据库，但数据是否会不断地来自您的Excel文件，并且不断更新和更改？


4）您是否只是使用Access对Excel文件进​​行计算，在这种情况下，您希望在何处显示结果。我不是Excel的专家，但如果你不使用Access就无法达到你的目标，我会感到非常惊讶。


我附上了一个非常基本的Db，可能会也可能不会给出第一次计算的正确结果，可能会或可能不会给你一些想法。


请忽略查询并查看表格。


Phil

I have a database where all Work order project must meet some requirements in order to be in compliance. I need to know out of the total number of projects that meet these requirements. First the status must be in COMP Or FCOMP. Second the labor hours > 0 third the must be with in a time range. the second part out of all those work orders i need to know what percentage of the work orders fall in between PMINS,pmor, PMPDM,pmreg,PMRT. that are not in compliance how many do not meet requirement 1, how many do not meet requirement 2, and how many do not meet requirement 3. There may be variations - for example, PROJECT A meets requirement 1 but not 2 & 3; PROJECT B meets requirement 1 & 3 but not 2, etc.

These counts need to be converted into percents based on the total number of PROJECT WORK ORDERS who are not in compliance.

How can I set this up in a query or report to show this information?

Your help is much appreciated. I know i can do it with SQL but I''m not that fluent with SQL. I know its a Distinct Select Count but i don''t know how to do this in query designer.

ms-access

Attached Files

(952.5 KB, 60 views)

解决方案

Difficult to know where to start.

The topic is under "Desktop Software", you have attached an Excel file, and further on you mention MS Access.

I have a feeling that you are referring to Access, in which case where have you got to with your project?

If you are converting from Excel to Access, you will need to normalise your Database before you can begin to solve your problems.

There are many articles on the subject, but here is a simple starter.

Phil

i have converted the excel table into access and have union query two queries SELECT Count(*) AS LEW
FROM MaximoReportT
WHERE (((MaximoReportT.Status)="COMP") AND ((MaximoReportT.ActualLaborHours)>"00:00") AND ((MaximoReportT.ActualStartDate) Between [Enter the Start Date] And [Enter the End Date])) OR (((MaximoReportT.Status)="FCOMP"));

UNION SELECT Count(*) AS LEW
FROM MaximoReportT
WHERE ((WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) AND ((Status)<>"CAN") AND (TargetStartDate) Between [Enter the Start Date] And [Enter the End Date];
which gives me 133/608 but now i need to figure out how to get those two numbers into a percentage .

I am still very far from clear what you want, so please answer the questions below.

1) Are you trying to trying to create a normal Access database with data in your tables and forms with Text Boxes, Combo boxes etc., and nothing to do with Excel?

2) Are you trying to trying to create a normal Access database with data in your tables and forms with Text Boxes, Combo boxes etc., but with the data initially extracted from your Excel file?

3) Are you trying to trying to create a normal Access database with data in your tables and forms with Text Boxes, Combo boxes etc., but with the data continually coming from your Excel file which is continually being updated and changed?

4) Are you just using Access to do calculations on your Excel file, in which case, where do you want to show the results. I am no expert in Excel, but I would be most surprised if you couldn''t achieve your goal without using Access.

I have attached a very basic Db which may or may not give the correct results for the first calculation, and may or may not give you some ideas.

Please ignore the queries and look at the form.

Phil

这篇关于多标准查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！