By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,656 Members | 1,882 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,656 IT Pros & Developers. It's quick & easy.

Query in MS Access

seraieis
P: 60
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     S.[ACCT_NUM], S.[PAID_FROM]
  3.  
  4. FROM
  5.     SARS_ACCT AS S
  6.  
  7. WHERE 
  8.     S.[PAID_FROM] = (
  9.         SELECT TOP 1 S1.[PAID_FROM]
  10.         FROM SARS_ACCT AS S1
  11.         WHERE S1.[ACCT_NUM]=S.[ACCT_NUM]
  12.         ORDER BY
  13.             S1.[ACCT_NUM] ASC,
  14.             S1.[PAID_FROM] DESC)
  15.  
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!
Attached Images
File Type: jpg query.JPG (29.4 KB, 134 views)
May 14 '09 #1
Share this Question
Share on Google+
9 Replies


beacon
100+
P: 579
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT S.acct_num, 
  2.        S.paid_from, 
  3.        S.paid_to
  4. FROM SARS_ACCT AS S
  5.  
  6. WHERE (((S.paid_from)=(SELECT TOP 1 S1.[PAID_FROM] 
  7.         FROM SARS_ACCT AS S1 
  8.         WHERE S1.[ACCT_NUM]=S.[ACCT_NUM] 
  9.         ORDER BY 
  10.             S1.[ACCT_NUM] ASC, 
  11.             S1.[PAID_FROM] DESC)));
  12.  
Is that what you were after?
May 14 '09 #2

ADezii
Expert 5K+
P: 8,684
@seraieis
Given the Format of your Data, wouldn't this simple approach work?
Expand|Select|Wrap|Line Numbers
  1. SELECT SARS_ACCT.ACCT_NUM, Max(SARS_ACCT.PAID_FROM) AS PAID_FROM, 
  2. Max(SARS_ACCT.PAID_TO) AS PAID_TO
  3. FROM SARS_ACCT
  4. GROUP BY SARS_ACCT.ACCT_NUM;
May 18 '09 #3

NeoPa
Expert Mod 15k+
P: 31,712
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT S.*
  2.  
  3. FROM   SARS_ACCT AS S INNER JOIN
  4.       (SELECT   ACCT_NUM,
  5.                 Max(PAID_FROM) AS MaxPF
  6.  
  7.        FROM     SARS_ACCT
  8.  
  9.        GROUP BY ACCT_NUM) AS subQ
  10.   ON  (S.ACCT_NUM=subQ.ACCT_NUM)
  11.  AND  (S.PAID_FROM=subQ.MaxPF)
May 18 '09 #4

seraieis
P: 60
@NeoPa

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 :)
May 18 '09 #5

NeoPa
Expert Mod 15k+
P: 31,712
It's a pleasure :)

(But why the surprise?)
May 18 '09 #6

seraieis
P: 60
@NeoPa
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 :)
May 18 '09 #7

NeoPa
Expert Mod 15k+
P: 31,712
You're fine really. I was just playing :)
May 18 '09 #8

seraieis
P: 60
@NeoPa
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...
:)
May 18 '09 #9

NeoPa
Expert Mod 15k+
P: 31,712
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.
May 18 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.