Use Subquery as Criteria in Access Query (SubSelect statement compare to average) ~ DataPig
This video demonstrates how to use a subquery in criteria on the grid for an Access Query. Mike calls this a SubSelect statement. In this case, 2 fields are compared to the average for that respective field. It shows how to interrogate the data and get all records that exceed the average value of a column and extends this to another column. So from a simple query and 2 subselects, he gets all records with sales above the average quantity whose extended price is below the average extended price - all within the query design grid.
for a quick lesson on SELECT statements, watch this:
AND vs OR in an Access Query Grid + SQL introduction ~ DataPig (4 minutes 28 sec)
• AND vs OR in an Access Query Grid + SQL in...
This video has been resurrected from an older Access tutorial series by DataPigTechnologies. Even though Access looks different now, the concepts and functionality persist. Thanks to owner, Mike Alexander, for making these videos available for presentation once again. Thanks also to Jack Drawbridge and John Clark for helping get these valuable lessons back online.