Unique ID between two date ranges

[vpranitha]

Hello,

Would like assistance to run a query in Access for the table below, where the ID is a text format.

Date	ID	Value
4/15/2020​	ABA	2.00
4/15/2020​	NHB	1.00
4/19/2020​	BBB	0.222​
4/20/2020​	RRRf	1.55​
5/1/2020​	NNN	9.55​
5/2/2020​	JUY	2.22​
5/2/2020​	YYY	2.22​
5/2/2020​	mm	4.55​
5/2/2020​	ABA	2.05​

The query I would like to run is as follows: For date range between 04/15/2020 and 04/20/200 and 05/1/2020 and 05/2/2020, return values for ID's that are present in both date ranges. In the above example the table would return as below

Date	ID	Value
4/15/2020​	ABA	2.00
5/2/2020​	ABA	2.05​

Thanks in advance.

 

[Joe4]

One easy way would be to do the following:
1. Create a query (let's call it Query1) on the table that returns all records where the Date field is between 4/15 and 4/20.
2. Create another query (let's call it Query2) on the table that returns all records where the Date field is between 5/1 and 5/2.
3. Create a matched query between Query1 and Query2, matching on ID.

That will return a record per ID, but you can return both values in different fields on that record.
You could actually do this all in one single query, if you know how to write subqueries (cannot use the Query Builder for that, you have to write the SQL code).

If you really need it over two lines, and not on one, let me know. There are ways to do that, but it is not quite as easy.

 

[vpranitha]

My access only shows unmatched Query wizard, that's not what we need is it ?

 

[Joe4]

All you have to do for a matched query is to create a new query in Query Builder, add both Queries to it, and then drag a line from the ID field in one query to the ID field in the other query.
The default relationship type will be to only show matched records.
Then, you can just double-click on the fields in each query that you want to see in the results, and it will add them to the return results.