Query Problem

[jackadamson]

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

 

[Taruz]

Hi..

If you want to find the date that the smallest of topcod, try this..


select
table1.TopCode,
min(table2.[Date]) as min_date
from table2 inner join table1 on table2.SubCode = table1.SubCode
group by table1.TopCode

 

[jackadamson]

Hi there, Thanks for the reply. It looks like something along the right lines.

However when I run it the following error occurs:

"You tried to execute a query that does not include the specified expression 'TopCode' as part of an aggregate function."

I dont know what this means.

 

[Taruz]

Hi..

"Topcode" grouped field. error should not be. Did you use the bottom row of group?

Is an alternative to this query but than before, is a long way..

select  topcode, (
                  select min([date]) from (
                           select table1.TopCode, table2.[Date]
        from table1 inner join table2 on table1.SubCode = table2.SubCode
                group by table1.TopCode, table2.[Date]) as tt  
                 where trz.topcode=tt.topcode) as min_date
from (
     select topcode,subcode 
        from table1
                group by  topcode,subcode )  as trz
group by topcode

 

[jackadamson]

I am very new to programming so I am trying your instructions by the letter. However I cant get it to work. The following error comes up

"Missing ), ], or Item in query expression '(
select min([date]) from (
select table1.TopCode, table2.[Date]
from table1 inner join table2 on table1.SubCode = table2.SubCode
group by table1.TopCode, table2.[Date]) as tt
where trz.topcode=tt.topcode) as min_date
from (
select topcode,subcode
from table1
group by topcode,subcode ) as trz
group by topcode"

 

[Taruz]

Hi @jackadamson..

The query looks correct.

Can you add a simple example db..