Multi criteria query

[jeannier1975]

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,PMR T. 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

[PhilOfWalton]

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

[jeannier1975]

i have converted the excel table into access and have union query two queries SELECT Count(*) AS LEW
FROM MaximoReportT
WHERE (((MaximoReport T.Status)="COMP ") AND ((MaximoReportT .ActualLaborHou rs)>"00:00") AND ((MaximoReportT .ActualStartDat e) Between [Enter the Start Date] And [Enter the End Date])) OR (((MaximoReport T.Status)="FCOM P"));

UNION SELECT Count(*) AS LEW
FROM MaximoReportT
WHERE ((WorkType) In ("PMINS","PMOR" ,"PMPDM","PMREG ","PMRT")) AND ((Status)<>"CAN ") AND (TargetStartDat e) 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 .

[PhilOfWalton]

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

[jeannier1975]

Answers: 1. Yes, I am trying to create a normal database in ACCESS WITH TABLES FORMS AND REPORTS.
2. YES
3.

[jeannier1975]

Phill i did not see you loaded database.
But here is what i have been working on I hope it gives you idea that I am trying to accomplished.

http://s000.tinyupload.com/?file_id=88121678388848224494

[PhilOfWalton]

So I know what you want but to try to put it gently, there is a lot of work involved. I am more than happy to help, but if we try to do it on the Bytes Website, it is going to go on and on and on.

I am the world's worst typist, so my suggestion would be to use Skype. If you want to do that, please send me a private message, and we will get things going.

Don't worry about loosing your existing data, we can load that into tables. It's messy, but can be done.

Sorry I forgot to attach the Db, and notes on normalisation. Will try again.

Please spend some time reading about normalisation. For a database to work correctly, it is essential to have a correct table structure and relationship between the tables.

Phil

[jeannier1975]

i have a hard time getting on skype since im not aloud to skype at work

[PhilOfWalton]

Well without being able to talk & share screens, it is going to take a long long time to sort out.

Do you have any similar alternatives?

Phil

[jeannier1975]

i agree and see you point let me see if i can get permission from ny boss.

[jeannier1975]

where do i send skype request do you have time tomorrow afternoon.

[PhilOfWalton]

Please see the Private Message

Phil