Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query filter

Posted on 2014-11-13
2
Medium Priority
?
195 Views
Last Modified: 2014-11-13
Experts:

I haven't used Access for a while so I'm a bit "rusty" on query filters.

Please find attached 2 documents:
1. QueryCalculation.mdb
2. Calculation.xls

The database contains one Table and two queries.    The spreadsheet mimics the value set in Table1.

In the two Access queries, I'm trying to calculate the average values for the four records BUT MUST EXCLUDE any records where value = 999.

The difference between query 1 and query 2 is where I placed the filter "< > 999" ("and" vs. "or").

The XLS provides the correct average values.   That is, I've manually excluded the 999 values in the average calculation.

My question:   How I can get same four average values (as shown in XLS) in an MS-Access query?

Thanks,
EEH
QueryCalculation.accdb
Calculation.xlsx
0
Comment
Question by:ExpExchHelp
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 40441055
try this query


SELECT Avg(IIf([Field1]=999,Null,[Field1])) AS Expr1, Avg(IIf([Field2]=999,Null,[Field2])) AS Expr2, Avg(IIf([Field3]=999,Null,[Field3])) AS Expr3, Avg(IIf([Field4]=999,Null,[Field4])) AS Expr4
FROM Table1;
0
 

Author Closing Comment

by:ExpExchHelp
ID: 40441804
Perfect solution!!  Thank you!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question