×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Help with finding Max Value for 4 columns with if statement

Help with finding Max Value for 4 columns with if statement

Help with finding Max Value for 4 columns with if statement

(OP)
Hello,

I am looking for help with a max query. I have four columns ID, afs_fye, date received, and header_id. The problem I am having is the max of the data received might not be the most recent afs_fye. So if the latest record is for another fiscal year I still need it to pull the most recent fiscal year. How do I fix this. I was think an if then statement but its not working. Any help would be appreciated.

Thanks,

Keri

RE: Help with finding Max Value for 4 columns with if statement

Some sample data representing your issue, and what do you want as an output, would be nice to see, along with - what do you consider a Fiscal Year?

ID  afs_fye  date_received  header_id
1     abc      1/1/2018        x
2     xyz      5/5/2000        y
3     abc    12/12/1999        z
 


---- Andy

There is a great need for a sarcasm font.

RE: Help with finding Max Value for 4 columns with if statement

(OP)
So the data looks like this
ID AFS_fye date_received header_id
1 2016 5/31/2017 5934
1 2015 6/01/2017 5940
2 2016 7/1/2017 5980
3 2016 7/7/2017 5981


What I need is for the max of the date received and the afs_fye however the prior fiscal was submitted after the current year so its pulling the wrong data. That is why I need the if then statement if thats the right direction.

Thanks,
Keri

RE: Help with finding Max Value for 4 columns with if statement

I must be missing something....

ID AFS_fye date_received header_id
 1  2016    5/31/2017     5934
 1  2015    6/01/2017     5940
 2  2016     7/1/2017     5980
 3  2016     7/7/2017     5981
 
"What I need is for the max of the date received" (which is ID 3) "and the [Max of ?] afs_fye " (which is - again - ID 3)

Unless this is NOT Some sample data representing your issue


---- Andy

There is a great need for a sarcasm font.

RE: Help with finding Max Value for 4 columns with if statement

(OP)
So to explain further
ID AFS_fye date_received header_id
1 2016 5/31/2017 5934
1 2015 6/01/2017 5940
2 2016 7/1/2017 5980
3 2016 7/7/2017 5981

if I set max to date_received it pulls this for record 1
ID AFS_fye date_received header_id
1 2016 6/01/2017 5940
2 2016 7/1/2017 5980
3 2016 7/7/2017 5981

I need it to pull the data for afs_fye 2016 but its pulling 2015.

RE: Help with finding Max Value for 4 columns with if statement

Lavenderchan can you please use TGML? Andy used it and his data is readable. Your's is garbled and needs to be organized in order to make any sense.

Did either of your datasets include what you expected for results?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Help with finding Max Value for 4 columns with if statement

How come your data changes when you 'pull' it?

record:
 ID AFS_fye date_received header_id
 1   2015     6/01/2017    5940 
became
 1   2016     6/01/2017    5940
 


---- Andy

There is a great need for a sarcasm font.

RE: Help with finding Max Value for 4 columns with if statement

CODE -->

SELECT 
 tblData.ID, 
 tblData.[AFS_fye ], 
 tblData.date_recieved, 
 tblData.Header_ID
FROM 
 tblData
WHERE
  tblData.[AFS_fye ] In ( SELECT Max(tblData.[AFS_fye ]) AS MaxOfAFS_fye FROM tblData GROUP BY tblData.ID)
ORDER BY 
  tblData.date_recieved; 

RE: Help with finding Max Value for 4 columns with if statement

If Lavenderchan wants latest AFS_fye and latest date_received, it could simply be:

Select TOP 1 
    ID, 
    AFS_fye, 
    date_recieved, 
    Header_ID
FROM tblData
ORDER BY date_recieved DESC 

Since it is: give me the newest / latest record.

Unless Lavenderchan needs something else, which I cannot figure out.


---- Andy

There is a great need for a sarcasm font.

RE: Help with finding Max Value for 4 columns with if statement

I think the OP was unclear but he wants the latest date for the latest FY by each group ID. But the strange part is why would you have a newer date in the next FY but an older FY. Not sure of that business model.

CODE -->

1 2016 5/31/2017 5934
1 2015 6/01/2017 5940 
Now that I think of it this could easily make sense. Do not know what the fields represent, but it could be a fiscal year funding line and then payments against different funding lines. So the date field and the FY field may not be related.

RE: Help with finding Max Value for 4 columns with if statement

That's why I keep asking for "sample data representing [the] issue", but... no cigar sad

I do too deal with Fiscal Year data at my place, and, unfortunately, I have a table where I have a date column and FY column. Sometimes those two do not 'agree' with each other, like the sample above in MajP's post. That's why I refuse to use this FY column. My FY starts on July 1, and ends on June 30. So I just add 6 months to my date and get the Year from that calculated date. This way it is always correct FY for any given date.


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close