Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

Checking a range of dates in Access

How would I check for all records where Data_A is between April 1, 2011 and December 2, 2011 and no records before April 1, 2011 exists.
0
morinia
Asked:
morinia
  • 4
  • 3
  • 2
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
select * from
tableX
where data_A >=#4/1/2011# and data_A <=#12/2/2011#
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
capricorn1

Does that query ensure that there is no record that is less than 4/2/2011 at all?  If a member has two records one 3/1/2011 and another one 5/1/2011.  I do not want to pull this member.
0
 
macarrillo1Commented:
Then you need a query like:

select * from
YourTable
where data_A > #4/1/2011# and data_A < #12/2/2011#
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Rey Obrero (Capricorn1)Commented:
ohh, try this, you need to change  "recordID" with the actual field for the record ID

select * from
tableX
where data_A >=#4/1/2011# and data_A <=#12/2/2011#
and exists(select T.recordID from tableX where T.date_A< #4/1/2011)=false
0
 
macarrillo1Commented:
If you want to exclude any member with more than one date you can do
a count on the members ID and exclude records with more than one for the count
as follows:


select MemberID, MemberName, count(*) as Ct from
Yourtable
where data_A > #4/1/2011# and data_A < #12/2/2011# and Ct=1
Group by MemberID, MemberName
0
 
macarrillo1Commented:
Another way to do that is:

select * from
YourTable Y
where Y.data_A > #4/1/2011# and Y.data_A < #12/2/2011#
and Y.MemberID Not in(select X.MemberID from YourTable X where X.date_A< #4/2/2011)
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
Capricorn1,

I have not used access in a while.  I am on Access 2007.  Where do I go to enter the SQL version of the query?
0
 
macarrillo1Commented:
In the query Wizard you select the SQL view.
0
 
Rey Obrero (Capricorn1)Commented:
open a new query
from ribbon
Create > Query Design
Close the Show Table window
click on SQL view from the ribbon or right click on the empty space for the "tables" and select SQL view
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now