Query problem - probably simple but I am a novice!

[JackA]

I am an access novice and need some help pretty badly. My problem is as follows:

I have two tables of data like below

Table1
TopCode SubCode Item
ABC ABC FishA
ABC ABCD FishB
ADF ADFG PhoneA


ADF ADFH PhoneB

Table 2
SubCode Date
ABC 01/01/2001
ABCD 01/01/2001
ADFG 05/02/2003
ADFH 04/02/2003

I want to run a query that produces the following

TopCode Date
ABC 01/01/2001
ADF 04/02/2003

Where the date is added by matching the SubCode from Table 2, with the TopCode from Table 1. If there is no TopCode match, I would like the match to be made by matching the SubCode from Table 2, with the SubCode from Table 1 with the oldest date chosen (e.g. 04/02/2003 rather than 05/02/2003).

At the moment all I have is

TopCode Date
ABC 01/01/2001

And ADF is removed from the result.

Can any Access wizards help with this problem?

I would really appreciate a reply

[jzwp11]

Where the date is added by matching the SubCode from Table 2, with the TopCode from Table 1. If there is no TopCode match, I would like the match to be made by matching the SubCode from Table 2, with the SubCode from Table 1 with the oldest date chosen (e.g. 04/02/2003 rather than 05/02/2003).

Once you make a join (top code of table1 to subcode of table2) you cannot alter that join during the execution of the query.

From what I see, your tables are not structure properly.

What are top and sub codes and how do they relate to one another?

Can you please provide more info on the business process you are modeling?

[JackA]

I am restricted by the tables structure and can not change them.

The only other thing I can add is that there is also a TopName and SubName.

I am basically creating an access database for reporting on the performance of funds in terms of size of assets and amount of sales (with attention paid to when they were created). Where I extract data from 3 sources, Assets, Sales and CreationDate. Link them together, then get Assets + Sales + CreationDate report, which varies on Dates of Sales and Asset counts.

Hope that helps.

[rpeare]

Do the items in your TABLE2 have a 1 to 1 relationship with the SUBCODE in your TABLE1?

In other words could you have something like:

Table1
TopCode SubCode Item
ABC     ABC     BLAHBLAH
ADF     ABC     BLAHBLAH

if so how are you linking the two tables?

Is it possible in TABLE2 to have:

Table2
SubCode SubCodeDate
ABC     1/1/2001
ABC     1/15/2001
ABC     1/1/2011

You seem to say in your original post that you want to pick out the most recent record if so then I have to assume your TABLE2 can have multiple records for each subcode?