Solved

Microsoft Access - Query

Posted on 2011-03-17
6
244 Views
Last Modified: 2012-05-11
I have two queries within access.  Query 1 - contains all accounts that have been opened from Jan 1 - Dec 31, 2010.   Query 2 contains all accounts opened from Jan 1 2010 - March 15 2011.  Is there a way to identify the new accounts that have been opened?   Those that are new will not be duplicated on Query 2.  Is there a way to determine which ones have not been duplicated?
0
Comment
Question by:ArisaAnsar
6 Comments
 
LVL 11

Expert Comment

by:wrmichael
ID: 35159552
Do you have table structures and query?

depends on the data available to you.

0
 
LVL 39

Expert Comment

by:als315
ID: 35159564
Left join your queries (Account field): select all from Query 1 with criteria: is Null for any field from Query 2
0
 
LVL 1

Expert Comment

by:gmartinnc
ID: 35159586
It seems your dates are a little wonky.  Is the second range Jan 1 2011 - March 15 2011?

If so, I think you are asking for something like this:
SELECT * FROM Query2 WHERE OpenDate NOT IN (SELECT OpenDate FROM Query1)

Is that correct?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 10

Accepted Solution

by:
Luke Chung earned 500 total points
ID: 35160100
What you need is a "non-in" query, otherwise known as an Outer Join between your two queries.

We have a paper Microsoft Access/SQL Server Query Tip: Finding Records in One Table but Not Another with "Not In" Queries here:  
http://www.fmsinc.com/microsoftaccess/query/outer-join/index.html

Hope this helps.
0
 

Author Closing Comment

by:ArisaAnsar
ID: 35168516
The Microsoft Access/SQL Server Query Tip is incredibly helpful and got me to the solution.  Thank you very much.
0
 
LVL 1

Expert Comment

by:gmartinnc
ID: 35190601
Hmmm.  seems to me I just said that.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

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