Query in MS Access

[seraieis]

Good afternoon!

I'm trying to run a query on a table to get the first record based on an account number.

On my table, i have multiple records for each account. What my goal is to get the first record for each individual account number (see attached image). I want the most current year for the account (i.e. i want the 01/01/09 record, not the 01/01/07 record). I've tried the following query, however it doesn't work the way I indent it to:

SELECT
    S.[ACCT_NUM], S.[PAID_FROM]

FROM
    SARS_ACCT AS S

WHERE 
    S.[PAID_FROM] = (
        SELECT TOP 1 S1.[PAID_FROM]
        FROM SARS_ACCT AS S1
        WHERE S1.[ACCT_NUM]=S.[ACCT_NUM]
        ORDER BY
            S1.[ACCT_NUM] ASC,
            S1.[PAID_FROM] DESC)

My ultimate goal is to pull the entire record's information that is brought in in the subquery, but I'm really unsure how to do that. I'm not even sure if this is possible, outside of writing VB code, which I'd like to stear away from if at all possible.

Any help would be GREATLY appreciated!

[beacon]

Hi seraieis,

I entered what you had on your table and then copied the query and was able to return the query results you were after by just adding the PAID_TO field to the SELECT clause.

Here's the code:

SELECT S.acct_num, 
       S.paid_from, 
       S.paid_to
FROM SARS_ACCT AS S

WHERE (((S.paid_from)=(SELECT TOP 1 S1.[PAID_FROM] 
        FROM SARS_ACCT AS S1 
        WHERE S1.[ACCT_NUM]=S.[ACCT_NUM] 
        ORDER BY 
            S1.[ACCT_NUM] ASC, 
            S1.[PAID_FROM] DESC)));

Is that what you were after?

[ADezii]

Good afternoon!

I'm trying to run a query on a table to get the first record based on an account number.

On my table, i have multiple records for each account. What my goal is to get the first record for each individual account number (see attached image). I want the most current year for the account (i.e. i want the 01/01/09 record, not the 01/01/07 record). I've tried the following query, however it doesn't work the way I indent it to:

SELECT
    S.[ACCT_NUM], S.[PAID_FROM]

FROM
    SARS_ACCT AS S

WHERE 
    S.[PAID_FROM] = (
        SELECT TOP 1 S1.[PAID_FROM]
        FROM SARS_ACCT AS S1
        WHERE S1.[ACCT_NUM]=S.[ACCT_NUM]
        ORDER BY
            S1.[ACCT_NUM] ASC,
            S1.[PAID_FROM] DESC)

My ultimate goal is to pull the entire record's information that is brought in in the subquery, but I'm really unsure how to do that. I'm not even sure if this is possible, outside of writing VB code, which I'd like to stear away from if at all possible.

Any help would be GREATLY appreciated!

Given the Format of your Data, wouldn't this simple approach work?

SELECT SARS_ACCT.ACCT_NUM, Max(SARS_ACCT.PAID_FROM) AS PAID_FROM, 
Max(SARS_ACCT.PAID_TO) AS PAID_TO
FROM SARS_ACCT
GROUP BY SARS_ACCT.ACCT_NUM;

[NeoPa]

I would suggest a subquery that finds the most recent, linked to the main table.

This way you can show as many of the fields as you choose without extra complications :

SELECT S.*

FROM   SARS_ACCT AS S INNER JOIN
      (SELECT   ACCT_NUM,
                Max(PAID_FROM) AS MaxPF

       FROM     SARS_ACCT

       GROUP BY ACCT_NUM) AS subQ
  ON  (S.ACCT_NUM=subQ.ACCT_NUM)
 AND  (S.PAID_FROM=subQ.MaxPF)

[seraieis]

I would suggest a subquery that finds the most recent, linked to the main table.

This way you can show as many of the fields as you choose without extra complications :

SELECT S.*

FROM   SARS_ACCT AS S INNER JOIN
      (SELECT   ACCT_NUM,
                Max(PAID_FROM) AS MaxPF

       FROM     SARS_ACCT

       GROUP BY ACCT_NUM) AS subQ
  ON  (S.ACCT_NUM=subQ.ACCT_NUM)
 AND  (S.PAID_FROM=subQ.MaxPF)

IT WORKS!!!!!!!!!!

Thank you all SO MUCH for your help and suggestions! I'm not sure why it this trick never occured to me, but I'm glad you two thought of it :)

[NeoPa]

It's a pleasure :)

(But why the surprise?)

[seraieis]

It's a pleasure :)

(But why the surprise?)

It's much more relief than surprise. I've been trying to figure this out for going on two weeks. For some reason, using the MAX function never occured to me. And this solution is so simple and elegant. And now I'm rambling :)

[NeoPa]

You're fine really. I was just playing :)

[seraieis]

You're fine really. I was just playing :)

That doesn't mean I'm not in awe of the solution. I'll have to read over my Access and SQL books a few more times...
:)

[NeoPa]

Awe is good :D

SQL is actually very powerful as a language. There are a few limitations from the standard in Jet SQL, but the concept is very sweet.