SQL Question in relation to Max for most recent date

[ImLost]

Hello Again!

First I just want to say thanks to everyone because you guys are great and have helped me with more than one database. Now for my problem...

I've been searching the forum and reading a lot of the posts about how get the most recent date from a query using Max. My problem is that I don't understand SQL. So, the stack of papers that I printed out to help me aren't really helping at all. ^_^

I know that I don't necessarily need to understand SQL if I know what to put into it -- so please help!

I need to pull the most recent date from a list of court dates that are updated on a subform of a subform. This way I can print a list of names to watch for in a given week.

The tables involved go as follows:

BaseTable
CrtTable
CrtSubTab

BaseTable is linked 1:Many via [ID#] to CrtTable
CrtTable is linked 1:Many via [Court ID#] to CrtSubTab

I need to find out the most recent court date [Pending Court Date] from the CrtSubTab for each [ID#] depending on the quarter (which is a field [Quarter] in the CrtTable). I will set [Quarter] criteria to pull from a choice selected on a seperate form prior to running the query. In addition, I'd like to have the dates that are pulled fall within in a specific date set, which I can also input on the same form that restricts the quarter. This I know is a simple Iif statement using < & >. But - will that impact my ability to use Max through SQL? I have seen several posts referencing the need to use two queries.

Pertinent Fields by Table would be:

BaseTable - [ID#], [Last Name], [First Name], [Middle Name], [DOB]
CrtTable - [Quarter]
CrtSubTab - [Pending Court] (which gives the hearing type), [Pending Court Date] (which provides the date)

Thanks and I hope I gave enough information.

 

[KeithG]

Can you post an example db and I will show you. You can take all the data out if it is private. I noticed you are from central Illinois, what city are you in?

 

[ImLost]

I'm near the U of I's Champaign Campus.

I'm off work in little while so I will clear out a dummy database and post it tomorrow. Thanks!

 

[ajetrumpet]

I noticed you are from central Illinois

And I noticed you are from Illinois too Keith!

 

[KeithG]

That write Springfield to be exact.

 

[ajetrumpet]

You write Springfield? What language is that in? Cobol?

 

[ImLost]

You write Springfield? What language is that in? Cobol?

LOL

Will post the database later this morning.

Ok - it's later! Here it is. There are a few dummy files to work with. Let me know if you have any questions. Thanks again!

 

[ImLost]

I just thought of something. I don't need to use Quarter in the query at all. All I need is for the date I'm looking for to fall within the dates I specify on the report form, that way I don't need to look through both the last and current quarters.

 

[KeithG]

try the below SQL

SELECT BaseTable.[ID #], BaseTable.[Last Name], BaseTable.[First Name], Max(CrtSubTab.[Pending Court Date]) AS [MaxOfPending Court Date]
FROM CrtSubTab INNER JOIN BaseTable ON CrtSubTab.[ID #] = BaseTable.[ID #]
GROUP BY BaseTable.[ID #], BaseTable.[Last Name], BaseTable.[First Name];

 

[ImLost]

It's working!

I started out with the response in gray below and then decided to try adding the CrtTable back into the equation since the BaseTable and CrtSubTable weren't joined together, which negated my response. It's working across the board and looks great.

So this is what I wound up with:

SELECT BaseTable.[ID #], BaseTable.[Last Name], BaseTable.[First Name], Max(CrtSubTab.[Pending Court Date]) AS [MaxOfPending Court Date]
FROM (BaseTable INNER JOIN CrtTable ON BaseTable.[ID #] = CrtTable.[ID #]) INNER JOIN CrtSubTab ON CrtTable.[Court ID#] = CrtSubTab.[Court ID#]
GROUP BY BaseTable.[ID #], BaseTable.[Last Name], BaseTable.[First Name]
HAVING (((Max(CrtSubTab.[Pending Court Date]))>=[forms]![reportmenu]![text26] And (Max(CrtSubTab.[Pending Court Date]))<=[Forms]![ReportMenu]![Text28]));

Thank you so much! This will make my job so much easier when it comes time to use this! ^_^



That worked except that it returns the same date for every record even though that date may not even be in the pertinent record. It looks like it picked the most recent date across the board and is returning that value for each record. In my case, it chose 04/04/08, but RecordA's most recent date is 02/08/08, so I know that is not correct.

Then, when I added in the criteria to pull from a specific date set, it doesn't return anything. Is there a way to make it pull the most recent date within the date set even when the date I want isn't the most recent in the record overall? Here is the criteria that I entered under the date:
>=[forms]![reportmenu]![text26] And <=[Forms]![ReportMenu]![Text28]

 

[KeithG]

Glad I could help.

 

[ajetrumpet]

Glad I could help too.

Adam, the good 'ol comic relief...