423,485 Members | 1,664 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,485 IT Pros & Developers. It's quick & easy.

Multi criteria query

P: 15
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
File Type: docx Understanding the Metrics Handout.docx (21.4 KB, 6 views)
File Type: xlsx Maximo Report.xlsx (952.5 KB, 9 views)
2 Weeks Ago #1

✓ answered by 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

Share this Question
Share on Google+
11 Replies


PhilOfWalton
Expert 100+
P: 1,353
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
2 Weeks Ago #2

P: 15
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 .
2 Weeks Ago #3

PhilOfWalton
Expert 100+
P: 1,353
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
2 Weeks Ago #4

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

P: 15
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=...78388848224494
2 Weeks Ago #6

PhilOfWalton
Expert 100+
P: 1,353
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
Attached Files
File Type: zip Jeannie.zip (390.3 KB, 4 views)
File Type: zip Normalise.zip (417.0 KB, 4 views)
2 Weeks Ago #7

P: 15
i have a hard time getting on skype since im not aloud to skype at work
2 Weeks Ago #8

PhilOfWalton
Expert 100+
P: 1,353
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
2 Weeks Ago #9

P: 15
i agree and see you point let me see if i can get permission from ny boss.
2 Weeks Ago #10

P: 15
where do i send skype request do you have time tomorrow afternoon.
1 Week Ago #11

PhilOfWalton
Expert 100+
P: 1,353
Please see the Private Message

Phil
1 Week Ago #12

Post your reply

Sign in to post your reply or Sign up for a free account.