Create a count if query

EnginerdUNH
NewLounger
Posts: 20
Joined: 14 Aug 2019, 00:12

Create a count if query

Post by EnginerdUNH »

I have an access database that has organizes test result data for multiple suppliers. Three of the tables in the database are tblSuppliers which lists all of the suppliers we have test data for, tblResults which lists the possible combinations of test outcomes, and tblTestResults which lists the actual outcomes of the tests which were performed. What I am trying to do is come up with a query that will list all the possible combinations of suppliers from tblSuppliers and results from tblResults and then count all of the instances of this combination from tblTest Results. So far, I have gotten so far as creating a query that pulls SupplierName from tblSupplier and Result from tblResults and when you run the query, it lists all of the possible combinations. When I try to add the following expression to the query, I'm getting a "Your expression does not include the specified expression 'SupplierName' as part of an aggregate function." error:

Code: Select all

Supplier Result: Count(IIf([tblTestResults]![SupplierName]=[tblSuppliers]![SupplierName] And [tblTestResults]![TestResult]=[tblResult]![Result],1))
For your information in case it's helpful, this is the SQL view for what I currently have:

Code: Select all

SELECT tblSuppliers.SupplierName, tblResult.Result, Count(IIf([tblTestResults]![SupplierName]=[tblSuppliers]![SupplierName] And [tblTestResults]![TestResult]=[tblResult]![Result],1)) AS [Supplier Result]
FROM tblSuppliers, tblResult, tblTestResults;
Can anyone tell me where I'm making a mistake?

User avatar
HansV
Administrator
Posts: 71177
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create a count if query

Post by HansV »

I'd do it in two steps:
First, a query based on tblSuppliers and tblResults, not joined, that returns all possible combinations.
Second, a query based on tblTestResults and the first query, with outer joins, to count the number of results for each combination.
See the attached small sample database.

TestResults.zip
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

EnginerdUNH
NewLounger
Posts: 20
Joined: 14 Aug 2019, 00:12

Re: Create a count if query

Post by EnginerdUNH »

HansV wrote:
06 May 2021, 07:09
I'd do it in two steps:
First, a query based on tblSuppliers and tblResults, not joined, that returns all possible combinations.
Second, a query based on tblTestResults and the first query, with outer joins, to count the number of results for each combination.
See the attached small sample database.


TestResults.zip
Hans, first of all, thank you for your reply and suggestions. I just have one question right of the bat after I took a look at your example database. I see you create the query to return the possible combinations of supplier and result based of the SupplierID and ResultID and then you pull those fields from the query as well as the SupplierName, Result and Score into the second query. I have my tblTestResults set up so that it stores SupplierName and Result rather than the ID value so I attempted to modify my queries accordingly but instead, it's throwing me large numbers for each of the combinations when there's only about 37 records in tblTestResults. I have attached a copy of the database so you can see what I mean.

EnginerdUNH
NewLounger
Posts: 20
Joined: 14 Aug 2019, 00:12

Re: Create a count if query

Post by EnginerdUNH »

It doesn't look like the file actually attached to my last post so I'm attaching it again.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 71177
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create a count if query

Post by HansV »

I'm confused now because the supplier is determined by the tubeserial. This causes an extra complication. I will have to think about it.
Regards,
Hans

EnginerdUNH
NewLounger
Posts: 20
Joined: 14 Aug 2019, 00:12

Re: Create a count if query

Post by EnginerdUNH »

The TubeSerial is determined by the SupplierName not the other way around

EnginerdUNH
NewLounger
Posts: 20
Joined: 14 Aug 2019, 00:12

Re: Create a count if query

Post by EnginerdUNH »

Or should I say, not determined by but tied to. As in, only certain TubeSerials are tied to each supplier.

User avatar
HansV
Administrator
Posts: 71177
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create a count if query

Post by HansV »

But one tubeserial cannot belong to two different suppliers - or can it?
Regards,
Hans

EnginerdUNH
NewLounger
Posts: 20
Joined: 14 Aug 2019, 00:12

Re: Create a count if query

Post by EnginerdUNH »

HansV wrote:
06 May 2021, 11:39
But one tubeserial cannot belong to two different suppliers - or can it?
That is correct, each tubeserial can only be tied to one supplier but each supplier has multiple tubeserials

User avatar
HansV
Administrator
Posts: 71177
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create a count if query

Post by HansV »

Using text fields to connect the tables is an unfortunate design; it is best to always link on the ID fields. In the attached version I have tried to implement this. Not everything will work yet, but the queries and the report do.

Test.zip
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

EnginerdUNH
NewLounger
Posts: 20
Joined: 14 Aug 2019, 00:12

Re: Create a count if query

Post by EnginerdUNH »

Thank you Hans for helping me restructure everything so that the queries now work as expected. You are certainly a true genius in your own right! Part of the reason that I was tying back to the field names instead of the ID values and using textboxes to requery certain fields is because I didn't want to store everything in tblTestResults as the ID values and then have to use one or more lookup queries in order to have the records print right on the report. I like the idea that you came up with to use combo boxes on the report that display the field name instead of the ID. It's definitely not something I would have come up with on my own.

EnginerdUNH
NewLounger
Posts: 20
Joined: 14 Aug 2019, 00:12

Re: Create a count if query

Post by EnginerdUNH »

I have one question with regards to the form frmTestResults. I have modified the combobox cmbSerial to filter based on the SupplierID value from the combobox cmbSupplier which is working properly but can you explain to me why I'm getting an error that says "the current field must match the join key '?' in the table that serves as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table." For your information, I was also getting this before I tried to filter the combobox by the supplier ID value from the other combobox.

I am also getting a "You tried to assign the Null value to a variable that is not a Variant data type" error when I try to run the code behind the Clear button which attempts to set the value of each of the comboboxes to Null and it is stopping on the line that attempts to set Me.cmbSerial.Value = Null.

User avatar
HansV
Administrator
Posts: 71177
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create a count if query

Post by HansV »

Here is a new version. I have removed the Clear button - it's dangerous to clear all values in existing records.

Test.zip
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

EnginerdUNH
NewLounger
Posts: 20
Joined: 14 Aug 2019, 00:12

Re: Create a count if query

Post by EnginerdUNH »

I have a follow-up question to my original post. Let's say I now have a similar structure to the original tables with the exceptions in available fields noted below:
tblTestResults
TestID
SupplierID
SerialID
TestTypeID
ResultID

tblTestResults2
TestID
SupplierID
SerialID
TestTypeID
Result1ID
Result2ID
Result3ID

I am having a devil of a time trying to modify the original query to give me the correct count of where tblTestResult2.Result1ID = qryCombinations2.ResultID, tblTestReslts2.Result2ID = qryCombinations2.ResultID or tblTestResults2.Result3ID = qryCombinations2.ResultID. This may be because I'm trying to do it all in one query and probably messing up the syntax. Am I better off doing individual queries to count the number of instances where the results are equal to the query results and then using a third query to combine them all for use later?

Please let me know if I should move this question over to a new thread or if there is more information that you need me to provide in order to understand the problem.

User avatar
HansV
Administrator
Posts: 71177
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create a count if query

Post by HansV »

Instead of using fields Result1ID, Result2ID and Result3ID, I'd create an extra table.

tblTestResults becomes tblTests, with fields

TestID(AutoNumber, Primary Key)
SerialID (joined to SerialID in tblSerial)
TestTypeID (joined to TestTypeID in tblTestType)

The new table will be tblTestResults, with fields

TestResultID (AutoNumber, Primary Key)
TestID (joined to TestID in tblTests)
ResultID (joined to ResultID in tblResult)

S0472.png
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

EnginerdUNH
NewLounger
Posts: 20
Joined: 14 Aug 2019, 00:12

Re: Create a count if query

Post by EnginerdUNH »

Hans, thank you again for you ideas. My only question is the following...I want to be able to count the number of instances where Result1ID = ResultID for a given supplier and test type as well as the same for Result2ID and Result3ID. I don’t want to sum up all instances where Result1ID, Result2ID or Result3ID is equal to ResultID for each given supplier and test type.

Would I simply just expand upon your example above to have a Result1ID, Result2ID and Result3ID field in the tblTestResults that you have in your above example? And then going back to my original question, would I then want to do individual queries to find all of the counts for each of the three IDs and then another query to combine them or is it something that can be done with one?

User avatar
HansV
Administrator
Posts: 71177
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create a count if query

Post by HansV »

If you really want that, don't create a new table, and go with your idea of creating separate queries for Result1ID, Result2ID and Result3ID.

But if you ever need more results per test, the complexity will increase.
Regards,
Hans