Solved

Query filter

Posted on 2014-11-13
2
168 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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
Perfect solution!!  Thank you!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now