Microsoft Access - Query

Posted on 2011-03-17
Medium Priority
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?
Question by:ArisaAnsar
LVL 11

Expert Comment

ID: 35159552
Do you have table structures and query?

depends on the data available to you.

LVL 41

Expert Comment

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

Expert Comment

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?
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 10

Accepted Solution

Luke Chung earned 2000 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:  

Hope this helps.

Author Closing Comment

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

Expert Comment

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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

597 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