Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 12th, 2006, 10:51 AM
Registered User
 
Join Date: Jul 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Simple query question

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.

 
Old July 12th, 2006, 02:29 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old July 13th, 2006, 03:38 PM
Registered User
 
Join Date: Jul 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!

 
Old July 16th, 2006, 02:10 PM
Friend of Wrox
 
Join Date: Jul 2005
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
not at all simple question petergoodman XSLT 0 July 18th, 2008 08:37 AM
Simple question VBAprutser VB How-To 2 August 20th, 2007 06:00 AM
Very Simple Question YoungLuke C# 2 May 4th, 2007 02:23 AM
Simple Question dpkbahuguna Java Basics 2 May 19th, 2006 12:05 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.