Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 12th, 2006, 10:51 AM
Registered User
 
Join Date: Jul 2006
Location: , , .
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.

Reply With Quote
  #2 (permalink)  
Old July 12th, 2006, 02:29 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #3 (permalink)  
Old July 13th, 2006, 03:38 PM
Registered User
 
Join Date: Jul 2006
Location: , , .
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!

Reply With Quote
  #4 (permalink)  
Old July 16th, 2006, 02:10 PM
Friend of Wrox
 
Join Date: Jul 2005
Location: Oklahoma City, OK, USA.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 12:52 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.