Simple query question

ltblue14 · July 12th, 2006, 10:51 AM

First off, some definitions of what I'm working with:
BalanceFE is a table of lots of policy numbers, dates, balances, etc.
Sheet1 is a table that includes only a few policy numbers.
Sheet2 is a table that includes only a few dates.

I would like to be able to search BalanceFE for the balances of those specific policies on those specific dates. Well, not exactly. I actually want all policies except those specific policy numbers to show up.

I can achieve the "date" sort with the following code:

Code:

SELECT BalanceFE.Policy, BalanceFE.Product, BalanceFE.Date, BalanceFE.Ballance, BalanceFE.Price
FROM BalanceFE,  Sheet2
WHERE (((BalanceFE.Date)=[Sheet2].[Date]));

[Note: I know I could perform both the date and policy queries in a single query, but I've separated them for the time being for simplicity and troubleshooting.]

Now I want to search the above query, call it Query1, for all account numbers except those listed in Sheet1. The following code does not work:

Code:

SELECT Query1.Policy, Query1.Product, Query1.Date, Query1.Ballance, Query1.Price
FROM Query1, Sheet1
WHERE (((Query1.Policy)<>[Sheet1].[policy]));

Thanks for any help you can provide, I'll be happy to clarify any questions.

mmcdonal · July 12th, 2006, 02:29 PM

You can do this quick and dirty if there are only a few policy numbers. You can enumerate them individually in the designer criteria line like this:

<>"acct123" And <>"acct234" etc

If the account numbers a re fairly static, this will work. If you want something more dynamic, there are all sorts of options, including functions and temp tables and etc.

Does this help?

mmcdonal

ltblue14 · July 13th, 2006, 03:38 PM

The account numbers were a bit more numerous than a method like that would allow, but I was able to find a workaround. I created a table of all of the account numbers I did need (something I was not able to do at first), and ran a query exactly like the Date query.
Thank you for your help!

AccessMaster · July 16th, 2006, 02:10 PM

This is very simple. You want an "umatched" query. There is autually a querty wziard that will help you do this call "Find umatched query wizard".

Example:

SELECT Query1.Policy, Query1.Product, Query1.Date, Query1.Ballance, Query1.Price
FROM Query1 LEFT JOIN [Sheet1] ON Query1.Policy= [Sheet1].Policy
WHERE ((([Sheet1].[policy] Is Null));

Boyd
"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.
http://www.officeprogramming.com