Can I make these 2 queries become 1? (1 Viewer)

[betheball]

I have this query:

SELECT testScreen, Count(ID) AS testCount FROM SERPTestInput GROUP BY testScreen

It returns a count for each testScreen that appears in the table.

Then this query returns the same thing except it joins another table and adds the criteria that the status for the record must be successful.

SELECT testScreen, Count(testID) AS myCount FROM Results RIGHT JOIN SERPTestInput ON (SERPTestInput.ID=Results.testID AND Results.testStatus = 'Successful') GROUP BY testScreen

So both queries currently generate an 8 row table with the first column being the testScreen and the second being a count. I would like to somehow combine the two so I get a three column, 8 riow table. Column 1 would still be the testScreen, column 2 the total count for each testScreen and column 3 would be the successful count for each test screen.

In short, can the above two queries be combined into one?

 

[antomack]

There are two ways to get the 3 fields into one query

1) Build a third query based on the other two queries, linked on TestScreen with the 3 required fields added.

2) Build a query similar to the second query with a DCount to get the total count of tests for each TestScreen, SQL as below assuming testscreen is a text field.

SELECT testScreen, DCount("[ID]","SERPTestInput","[testScreen]='" & [testScreen] & "'") AS TotalTests, Count(testID) AS myCount FROM Results RIGHT JOIN SERPTestInput ON (SERPTestInput.ID=Results.testID AND Results.testStatus = 'Successful') GROUP BY testScreen

 

[betheball]

Thanks antomack. That works as advertised. So since it works, of course I want to complicate it. The issue I have now is that each test will be either successful or failed. However, some have yet to be tested, meaning they don't appear in the Results table at all. I would like to move my results to 4 columns. The first three would be the same as above, screenTest, total count and count of successful. The fourth would be a count of failed tests. Because there are some tests that have yet to be performed, I cannot simply subtract the successful count from the total count.

Any thoughts on this?

 

[antomack]

It sounds like you need 3 select queries to get the values you want and then 1 final query to combine them all.

Query1: As currently - Count number of records for each testscreen

Query2: As currently - Count number of successful results for each testscreen

Query3: Count number of failed results for each testscreen, similar to Query2 but teststatus of 'Failed' instead of successful.

Query4: Query combining the other 3 queries, link 1 to 2 and 1 to 3 on the testscreen field. Add the fields you want from each query. You will need to change the 2 join expressions by double-clicking on the line connecting the tables. Select the option where it says 'Include all records from Query1......' This will ensure that if a testscreen has no successful or failed tests it will still appear.

 

[betheball]

Thanks again. I forgot that the same test can have multiple failed entries and up to one successful entry, because the test is rerun until it passes. So, I will need to rethink how to do this. I think I will stick with what you gave me above and then perhaps add run a second query to get the other column. I should mention that this is an ASP app, with Access just as a backend so I can easily run two queries, dump the results into an array and display the data how I want. Thanks again for the help.