asked on

MS Access sub-select

I have a database that logs users' test results for ESD equipment they wear on their body. All users have a wrist strap they must test. Many have a heel strap on each foot they must test, (but not all).
If a user tests their equipment, a record will be inserted with all PASS/FAIL information.
I will see:
WRIST_STATUS: Pass, NotUsed, Fail
LFOOT_STATUS: Pass, NotUsed, Fail
RFOOT_STATUS: Pass, NotUsed, Fail

Here is my query that is used to view the data:

SELECT FIRST_NAME, LAST_NAME, DATE_TIME, WRIST_STATUS, WRIST_RESISTANCE, LFOOT_STATUS, LFOOT_RESISTANCE, RFOOT_STATUS, RFOOT_RESISTANCE FROM ESD_RESULTS ORDER BY ENTRY_NUMBER ASC;

This query currently displays this data:

I'd like to refine this query. If any one status = "Fail", then I highlight the cell in red; else I highlight in green. This is very visual.
As this list grows throughout the course of the day, it will be hard to determine who had any "Fails" but did not go pack and re-test until they got "Pass" results.

QUESTION:

How can I write a query that shows me all the people who had a "Fail" in any one of the status fields, but do NOT have a record later in the day with all "Pass" or some combination of "Pass" and "NotUsed" ("NotUsed" is used by folks who do not need to test their heel straps.

Jeffrey Coachman

Not sure of your requirement here:

<a query that shows me all the people who had a "Fail" in any one of the status fields, but do NOT have a record later in the day with all "Pass" or some combination of "Pass" and "NotUsed">

Are you saying that one person can have more than one test in a day?

So if a person gets tested 5 times in a day, and in any 1 test they fail in one field, ...then you want just that "Failed" record shown?

You did not state what value constitutes a "Fail", so here I will just say if the Ohms is 1 or more

This query seems to work for me:

SELECT FIRST_NAME, LAST_NAME, DATE_TIME, WRIST_STATUS, WRIST_RESISTANCE, LFOOT_STATUS, LFOOT_RESISTANCE, RFOOT_STATUS, RFOOT_RESISTANCE
FROM ESD_RESULTS
WHERE WRIST_RESISTANCE>=1 OR LFOOT_RESISTANCE>=1 OR RFOOT_RESISTANCE >=1

If not, then please post an example of your data, then show the exact results you want...

IrogSinta

Sounds to me like you just need to get everyone's most recent test for the day and see if they had a "Fail" in any category. If that is the case, this query gives the Employee name and time of their last ESD test , as long as their last time stamp for each day included a "Fail".

SELECT [Employee Name], Max([Test Time Stamp]) AS LastTestTaken, DateValue([Test Time Stamp]) AS TestDate
FROM ESD_RESULTS
WHERE [Wrist Status] = "Fail" Or [Left Foot Status] = "Fail" Or [Right Foot status] = "Fail"
GROUP BY [Employee Name], DateValue([Test Time Stamp]);

Open in new window

If you also want to see what the results of each category were for these records, then, using the fields Employee Name and LastTestTaken as the criteria, you could either:

1. Add DLookup fields to this query.
2. Create another query that includes this query joined to the original table.
3. Create a nested query (Not really my specialty as I'm still learning how to do this.)

dimmergeek

ASKER

Very close.
What I need to do is get the most recent test result for each person and see if their test record was a pass or a fail. If it was a pass, I do not need to see it.
If any status columns was a fail, then I want to display the record.

dimmergeek

ASKER

This query currently shows me latest test record.
Now I need to filter out the "Passes"

SELECT ESD_RESULTS.LAST_NAME, ESD_RESULTS.FIRST_NAME, Max(ESD_RESULTS.DATE_TIME) AS LastTestTaken, DateValue([DATE_TIME]) AS TestDate
FROM ESD_RESULTS
WHERE
(ESD_RESULTS.WRIST_STATUS="High" OR ESD_RESULTS.WRIST_STATUS="Low") OR
    (ESD_RESULTS.LFOOT_STATUS="High" OR ESD_RESULTS.LFOOT_STATUS="Low") OR 
    (ESD_RESULTS.RFOOT_STATUS="High" OR ESD_RESULTS.RFOOT_STATUS="Low")
AND (ESD_RESULTS.[DATE_TIME] Like "09-23-2013")
GROUP BY ESD_RESULTS.LAST_NAME, ESD_RESULTS.FIRST_NAME, DateValue([DATE_TIME]);

Open in new window

Right now this is showing me the last record where there is a failure, but if there is a record AFTER this one that is all passes, I do not want to see this record.
Basically, I want to see who tested, failed and did not re-test and pass later.

PortletPaul

Is there a unique identifier for every row in the table of results?

IrogSinta

Can you post some data and the expected results?

PortletPaul

The reason for asking is that I'd like to know exactly how to match-up records. Note the following is a 'concept' and not fully developed, it requires knowing how to match up the records accurately by person. It also needs adaption for those who aren't testing all 3 items.

Pl. note Access syntax is not a strength, but believe EXISTS is supported

Here goes:

SELECT
        PERSON_ID
      , FIRST_NAME
      , LAST_NAME
      , DATE_TIME
      , WRIST_STATUS
      , WRIST_RESISTANCE
      , LFOOT_STATUS
      , LFOOT_RESISTANCE
      , RFOOT_STATUS
      , RFOOT_RESISTANCE
FROM ESD_RESULTS
WHERE NOT EXISTS (
                SELECT 1
                FROM ESD_RESULTS AS R
                WHERE r.WRIST_STATUS = 'Pass'
                        AND r.LFOOT_STATUS = 'Pass'
                        AND r.RFOOT_STATUS = 'Pass'
                        AND ESD_RESULTS.PERSON_ID = R.PERSON_ID
                        AND DATEVALUE(ESD_RESULTS.DATE_TIME) = DATEVALUE(r.DATE_TIME)
                        AND r.DATE_TIME > ESD_RESULTS.DATE_TIME
        )
ORDER BY
        ENTRY_NUMBER ASC;

Open in new window

{+ 2 edits, v.sorry}

dimmergeek

ASKER

Please find the attached file with sample data.
As you scroll down you will see users Ricky Rose and Sheila Ruffing highlighted in yellow. The individuals failed their first attempt at testing for one criteria or another, but their most recent record is considered a pass. I would NOT want the see them in the query generated by this question on EE.

However scroll down further to where Paola De La Cruz is listed. She tested three times. Her last test was also a fail. She never passed the test today. I would want to see her name.
So, I'm really after the most recent test for every user in the table and I want to see their record listed ONLY IF that record is considered a fail.

Fail criteria: (WRIST_STATUS <> "Pass") OR (LFOOT_STATUS == "High" OR LFOOT_STATUS == "Low") OR (RFOOT_STATUS == "High" OR RFOOT_STATUS == "Low")
ESD-RESULTS.xlsx

PortletPaul

Working from a subset* of those records, a result like this:

| EMPID | FIRST_NAME*|           DATE_TIME | WTUS |  WRES | LTUS | LRES | RTUS | RRES |
--------|------------|---------------------|------|-------|------|------|------|------|
|  1292 |    Soon-Ok | 2013-09-23 07:23:59 | High | 74.75 | Pass | 1.98 | Pass | 1.82 |
| 14448 |      Jimmy | 2013-09-20 14:51:03 | High |   100 | Pass | 0.86 | Pass | 0.89 |
| 14650 | De La Cruz | 2013-09-23 07:00:01 | Pass | 18.35 | Pass | 1.81 | High |  100 |
* last names removed from this output

Open in new window

was produced by this query (nb: adapted from sql server to what I hope is access syntax).
This will quite literally only be the latest record (NOT the latest per day) for those not having a full pass.

SELECT
        ESD_RESULTS.EMPID
      , ESD_RESULTS.FIRST_NAME
      , ESD_RESULTS.LAST_NAME
      , ESD_RESULTS.DATE_TIME
      , ESD_RESULTS.WRIST_STATUS
      , ESD_RESULTS.WRIST_RESISTANCE
      , ESD_RESULTS.LFOOT_STATUS
      , ESD_RESULTS.LFOOT_RESISTANCE
      , ESD_RESULTS.RFOOT_STATUS
      , ESD_RESULTS.RFOOT_RESISTANCE
FROM (
      ESD_RESULTS
        INNER JOIN (
                      SELECT
                              EMPID
                            , MAX(DATE_TIME) AS DATE_TIME
                      FROM ESD_RESULTS
                      WHERE NOT EXISTS (
                                      SELECT 1
                                      FROM ESD_RESULTS AS R
                                      WHERE r.WRIST_STATUS = 'Pass'
                                        AND r.LFOOT_STATUS = 'Pass'
                                        AND r.RFOOT_STATUS = 'Pass'
                                        AND ESD_RESULTS.EMPID = R.EMPID
                                        AND datevalue(ESD_RESULTS.DATE_TIME) = datevalue(r.DATE_TIME)
                                        AND r.DATE_TIME > ESD_RESULTS.DATE_TIME
                              )
                      AND NOT (     WRIST_STATUS = 'Pass'
                                AND LFOOT_STATUS = 'Pass'
                                AND RFOOT_STATUS = 'Pass' )
                      GROUP BY EMPID
                    ) AS FAILS ON ESD_RESULTS.EMPID = FAILS.EMPID
                              AND ESD_RESULTS.DATE_TIME = FAILS.DATE_TIME
      )
ORDER BY
        ESD_RESULTS.ENTRY_NUMBER ASC;

Open in new window

* entry numbers 1-21 & 81-91

IrogSinta

@PortletPaul
I believe it should be Jeffrey on your list instead of Soon-Ok since Soon-Ok had all "Pass" a couple of seconds later at 9/23/13 7:24:01 AM.

@dimmergeek
See if this works for you:

SELECT R.DATE_TIME, R.EMPID, R.LAST_NAME, R.FIRST_NAME, R.DEPT, R.Wrist_Status, R.LFoot_Status, R.RFoot_status, R.WRIST_RESISTANCE, R.LFOOT_RESISTANCE, R.RFOOT_RESISTANCE, R.GMT_OFFSET
FROM (SELECT DateValue([DATE_TIME]) AS TestDate, Max(ESD_RESULTS.DATE_TIME) AS LastTestTaken, ESD_RESULTS.EMPID
FROM ESD_RESULTS
GROUP BY DateValue([DATE_TIME]), ESD_RESULTS.EMPID) AS T, ESD_RESULTS AS R
WHERE (DateValue(R.DATE_TIME)=T.TestDate) AND R.DATE_TIME=T.LastTestTaken AND R.EMPID=T.[EMPID] AND (R.Wrist_Status Not In ("Pass","NotUsed") OR R.LFoot_Status Not In ("Pass","NotUsed") OR R.RFoot_status Not In ("Pass","NotUsed"))

Open in new window

Here are the results (w/o last names):

9/23/13 7:00 AM	14650	De La Cruz	PROD	Pass	Pass	High	18.35	01.81	100.00	-5
9/23/13 9:31 AM	11852	Jeffrey		PROD	Pass	Pass	High	01.52	01.67	100.00	-5
9/20/13 2:51 PM	14448	Jimmy		PROD	High	Pass	Pass	100.00	00.86	00.89	-5

Open in new window

Ron

PortletPaul

line 26 of my query above may be removed (this saves quite a bit) but produces the same outcome - it was a legacy of thinking results were wanted "per day".

@IrogSinta, Yours does look good.

In my defence: as I was only using part of the sample data, using that same sample with your query I get the same result (except columns are different). sqlfiddle permits a grand total of 8000 chars of DDL including the inserts - that's why it was partial.

**[Results]**:
    
    |                        DATE_TIME | EMPID | LAST_NAME | FIRST_NAME | DEPT | WRIST_STATUS | LFOOT_STATUS | RFOOT_STATUS | WRIST_RESISTANCE | LFOOT_RESISTANCE | RFOOT_RESISTANCE | GMT_OFFSET |
    |----------------------------------|-------|-----------|------------|------|--------------|--------------|--------------|------------------|------------------|------------------|------------|
    | September, 20 2013 14:51:03+0000 | 14448 |  Hxxxxxxx |      Jimmy | PROD |         High |         Pass |         Pass |              100 |             0.86 |             0.89 |         -5 |
    | September, 23 2013 07:00:01+0000 | 14650 |     Pxxxx | De La Cruz | PROD |         Pass |         Pass |         High |            18.35 |             1.81 |              100 |         -5 |
    | September, 23 2013 07:23:59+0000 |  1292 |   Sxxxxxx |    Soon-Ok | PROD |         High |         Pass |         Pass |            74.75 |             1.98 |             1.82 |         -5 |


**Query 1**:

    SELECT R.DATE_TIME
    	, R.EMPID
    	, R.LAST_NAME
    	, R.FIRST_NAME
    	, R.DEPT
    	, R.Wrist_Status
    	, R.LFoot_Status
    	, R.RFoot_status
    	, R.WRIST_RESISTANCE
    	, R.LFOOT_RESISTANCE
    	, R.RFOOT_RESISTANCE
    	, R.GMT_OFFSET
    FROM (
    	SELECT convert(date,[DATE_TIME]) AS TestDate
    		, Max(ESD_RESULTS.DATE_TIME) AS LastTestTaken
    		, ESD_RESULTS.EMPID
    	FROM ESD_RESULTS
    	GROUP BY convert(date,[DATE_TIME])
    		, ESD_RESULTS.EMPID
    	) AS T
    	, ESD_RESULTS AS R
    WHERE (convert(date,R.DATE_TIME) = T.TestDate)
    	AND R.DATE_TIME = T.LastTestTaken
    	AND R.EMPID = T.[EMPID]
    	AND (
    		R.Wrist_Status NOT IN (
    			'Pass'
    			, 'NotUsed'
    			)
    		OR R.LFoot_Status NOT IN (
    			'Pass'
    			, 'NotUsed'
    			)
    		OR R.RFoot_status NOT IN (
    			'Pass'
    			, 'NotUsed'
    			)
    		)

Open in new window

dimmergeek

ASKER

We're very close.
PLease see resulting output

Jimmy should not be showing up as he has had a pass since his last failure.

Using IrogSinta,'s query as PortletPaul's was causing a circular reference in Access adn the follow up query had convert functios which were not recognized.
Capture.JPG

IrogSinta

So are you saying that the Pass could be on another day since Jimmy's Pass was 3 days later?

dimmergeek

ASKER

In the end this query will be run on a web page and we will be querying by date.
So, when the page loads, I want to see who tested today and failed, but did not re-test and pass.

This is my current web page query that retrieves all test data for the current date:

<%
myD = Day(Date)
myM = Month(Date)
myY = Year(Date)
        
if (myD < 10 ) then
     myD = "0" & myD
end if
        
if (myM < 10 ) then
     myM = "0" & myM
end if
        
currdate = myM & "-" & myD & "-" & myY

SELECT FIRST_NAME, LAST_NAME, DATE_TIME, WRIST_STATUS, WRIST_RESISTANCE, LFOOT_STATUS, LFOOT_RESISTANCE, RFOOT_STATUS, RFOOT_RESISTANCE FROM ESD_RESULTS WHERE Format(CDate([DATE_TIME])) > #" & currdate & "# ORDER BY ENTRY_NUMBER ASC;
%>

Open in new window

PortletPaul

apologies for the confusion, the 'follow-up' was IrogSinta's not mine.

Results from the sample data (all records):

| ENTRY_NUMBER | EMPID | LAST_NAME |      DATE_TIME      | WRIST_STATUS | LFOOT_STATUS | RFOOT_STATUS |
---------------|-------|-----------|---------------------|--------------|--------------|--------------|
|           86 | 14650 |     Paola | 2013-09-23 07:00:01 |         Pass |         Pass |         High |
|           99 | 11852 | Zimmerman | 2013-09-23 09:31:12 |         Pass |         Pass |         High |
|          103 | 11295 |      Haas | 2013-09-23 10:22:30 |         Pass |      NotUsed |      NotUsed |

Open in new window

Produced by this query (as close as I can get it to Access syntax):

SELECT
        ESD_RESULTS.ENTRY_NUMBER
      , ESD_RESULTS.EMPID
      , ESD_RESULTS.FIRST_NAME
      , ESD_RESULTS.LAST_NAME
      , ESD_RESULTS.DATE_TIME
      , ESD_RESULTS.WRIST_STATUS
      , ESD_RESULTS.WRIST_RESISTANCE
      , ESD_RESULTS.LFOOT_STATUS
      , ESD_RESULTS.LFOOT_RESISTANCE
      , ESD_RESULTS.RFOOT_STATUS
      , ESD_RESULTS.RFOOT_RESISTANCE
FROM ( ESD_RESULTS
INNER JOIN (
              SELECT
                      EMPID
                    , MAX(ENTRY_NUMBER) as ENTRY_NUMBER
              FROM ESD_RESULTS
              WHERE NOT EXISTS (
                              SELECT ENTRY_NUMBER
                              FROM ESD_RESULTS AS R
                              WHERE r.WRIST_STATUS = "Pass"
                                AND r.LFOOT_STATUS = "Pass"
                                AND r.RFOOT_STATUS = "Pass"
                                AND ESD_RESULTS.EMPID = R.EMPID
                                AND r.DATE_TIME > ESD_RESULTS.DATE_TIME
                      )
              AND NOT (     WRIST_STATUS = "Pass"
                        AND LFOOT_STATUS = "Pass"
                        AND RFOOT_STATUS = "Pass" )
              GROUP BY EMPID
            ) AS FAILS ON ESD_RESULTS.ENTRY_NUMBER = FAILS.ENTRY_NUMBER )
;

Open in new window

ASKER CERTIFIED SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IrogSinta

@dimmergeek,
Just wondering if Haas was supposed to show up in this list. Although his latest test had Pass and NotUsed, his test prior to that had fails for the LeftFoot and RightFoot equipment. In his last test, he only tested the WristStrap and marked the other two as NotUsed. So since he never retested them to get a Pass, is he a candidate for your list?

Ron