Pulling the next date from another table

[Payal Patel]

I have two tables. One with the following:
Table 1:
ID Date
ABC 1/1/2015

Table 2:
ID Date:
ABC 12/1/2104
ABC 1/3/2015
ABC 1/3/2016

Result I'm looking for:
Table 1:
ID Date: Table__Date:
ABC 1/1/2015 1/3/2015

In this example, I need to pull in the date "1/3/2015" from table 2 as an update in table 1 because it is the date after 1/1/2015 in table 1. Is anyone familiar with a query? I've tried so many different ways, but not working.

 

[plog]

A few of the things I see in your post make me question your tables. 1-- you have 2 tables with the same fields-- why? Or did you omit some fields in the tables that differentiate them? 2-.the use of the term 'update'. Are you actually moving data from one table to the other? Often that's a sign of a poor setup.

Also, you shouldn't name a field 'date'. It's a reserved word and makes coding and querying more difficult. Change it be prefixing it with what date it represents.

Now, to achieve d what you want, you create a query with both tables linked via their ID fields. Then you create a field to show the date in table 2 if it is greater than the date in table 1, otherwise show a null. Then, make it an aggregate query (click the signature/summation symbol in the ribbon and change to the GROUP BY under that calculated field to MIN

 

[JHB]

I've made a sample database for you, run the query "FindMinDatePlusOne".

 

[Payal Patel]

Thank you both - this works great

 

[BeeJayEff]

One word of warning - all the dates you have used in your example could be either US or non-US (e.g. 1/3/2015 could be either 1st March or 3rd January depending on your local settings). Access uses American-format dates internally.