Help with finding Max Value for 4 columns with if statement

[Lavenderchan]

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

 

[Andrzejek]

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?

[pre]
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
[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[Lavenderchan]

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

 

[Andrzejek]

I must be missing something....

[pre]
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[blue]
3 2016 7/7/2017 5981[/blue]
[/pre]
"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.

 

[Lavenderchan]

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.

 

[dhookom]

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

 

[Andrzejek]

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

record:[pre]
ID AFS_fye date_received header_id
1 [blue]2015[/blue] 6/01/2017 5940[/pre] became[pre]
1 [red]2016[/red] 6/01/2017 5940
[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[MajP]

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;

 

[Andrzejek]

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

[pre]
Select TOP 1
ID,
AFS_fye,
date_recieved,
Header_ID
FROM tblData
ORDER BY date_recieved DESC[/pre]

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.

 

[MajP]

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.

1 2016 5/31/2017 5934
[b]1 2015 6/01/2017 5940[/b]

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.

 

[Andrzejek]

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

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.