Calculation Query (1 Viewer)

[Jul]

:banghead:
Hoping to get some help with this one. We have a company that designed a Quality system for us, but didn't build any of the reports, so I have been left trying to figure out the different queries to run these reports. Here is my latest issue: the company built a quality table, where it shows how many defects were found, and samples that were inspected. However, if a defect is found, that information goes into another data table, using the auto # id field as the reference # to which inspection the defect was found in. That is the only way to join these. The problem is, I also have those inspections in the Quality table where there weren't any defects found. I am trying to write a query to calculate the quality index each day for each of our production lines, but any of the lines that have no defects found won't show up because they aren't in the defect table. Does anybody have any thoughts on how I can combine them? I have tried to figure out if I can use the is null or is not null somehow in a query but can't seem to get anything to work. Any advice would be great! Thanks in advance!

 

[isladogs]

Try a UNION query

 

[JHB]

I would first run a query to find all the inspection (samples) done per day.
Then run a query to find all the defect found per day (summing them per sample) and then link the 2 queries together with a left join.

 

[The_Doc_Man]

The solution to your problem MIGHT be something called an OUTER JOIN, and I advise you to look it up.

You write a query to OUTER JOIN (usually written as LEFT JOIN or RIGHT JOIN depending on which table is the independent table and which is the dependent table). When you do this, you get your selected fields from all records from the independent table and your selected fields from any matching records from the dependent table. BUT you get the selected fields from records in the independent table even if there are NO records in the dependent table. In THAT case, you get ONE record from the independent table and the selected fields that WOULD have come from the dependent table are NULL.

So then rather than using the raw fields from the dependent table, you use NZ([dependent-field], "" ) or NZ([...], 0) depending on data type.

Once you have THAT query, you can do count queries of the query rather than of the raw tables, and your counts can include WHERE clauses that include or exclude the fields that came back as nulls. This is "query layering" and works perfectly well in Access/SQL.

The idea is to first form the virtual data set with the OUTER JOIN query, THEN do your statistics as needed knowing that there will be some records that contain no error info but you can still count them with careful use of summation queries with restrictive or non-restrictive WHERE clauses.

 

[arnelgp]

this will confuse you more, lol

UNION Query. you have the first part, the defective
ones. now add the good ones with Union.

then from this Union you can compute your
Quality Index on a specific date.

 

[Jul]

The solution to your problem MIGHT be something called an OUTER JOIN, and I advise you to look it up.

You write a query to OUTER JOIN (usually written as LEFT JOIN or RIGHT JOIN depending on which table is the independent table and which is the dependent table). When you do this, you get your selected fields from all records from the independent table and your selected fields from any matching records from the dependent table. BUT you get the selected fields from records in the independent table even if there are NO records in the dependent table. In THAT case, you get ONE record from the independent table and the selected fields that WOULD have come from the dependent table are NULL.

So then rather than using the raw fields from the dependent table, you use NZ([dependent-field], "" ) or NZ([...], 0) depending on data type.

Once you have THAT query, you can do count queries of the query rather than of the raw tables, and your counts can include WHERE clauses that include or exclude the fields that came back as nulls. This is "query layering" and works perfectly well in Access/SQL.

The idea is to first form the virtual data set with the OUTER JOIN query, THEN do your statistics as needed knowing that there will be some records that contain no error info but you can still count them with careful use of summation queries with restrictive or non-restrictive WHERE clauses.

This worked PERFECTLY!!!! THANK YOU, THANK YOU, THANK YOU!!!!!