You are here:

Using MS Access/MS Access 2007 showing most recent record

Advertisement


Question
QUESTION: Hi Scott,

I hope you are doing well.  You have helped me in the past.  I presently use MS Access 2007.

I'm pulling records from two tables to create a query.  I'm trying to do several things in one query.  The records that I am querying are unique by inspection date and record number.  Each inspection  record also has an assigned health score.  Each record number can have multiple inspection dates.  What I want to do is to do a date range search for the most current inspection date of records that have a certain health score range.

Any help is much appreciated.

ANSWER: Hi Silvia,

Yes I remember you. Hope things are going well with you too. Was that Culvert project ever completed?

As to this query, can you show me the SQL you currently have. My initial thinking here is you will probably need a subquery.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Hi Scott,

Nope, the database was never finished due to budget cuts.  The process has begun this year to get a new database.  Fixes need to be made to make it work as best we can for now.  I was brought back to work with the program.

Here's the SQL code.  For whatever reason now it is asking me for the date range twice.



SELECT Max(dyn_CulvertInspection.InspectionDate) AS MaxOfInspectionDate, SystemGeneral_sta.SYSDistNo, SystemGeneral_sta.SYSCOText, SystemGeneral_sta.SYSRoute, dyn_CulvertInspection.SysNo, dyn_CulvertInspection.INETNO, dyn_CulvertInspection.OUTETNO, CLng(IIf(IsNumeric([dyn_CulvertInspection].[Health Assess]),[dyn_CulvertInspection].[Health Assess],-1)) AS [Health Assess_Num], CLng(IIf(IsNumeric([dyn_CulvertInspection].[Health Assess]),[dyn_CulvertInspection].[Health Assess],-1)) AS Expr1
FROM dyn_CulvertInspection INNER JOIN SystemGeneral_sta ON dyn_CulvertInspection.SysNo = SystemGeneral_sta.SYSNO
GROUP BY SystemGeneral_sta.SYSDistNo, SystemGeneral_sta.SYSCOText, SystemGeneral_sta.SYSRoute, dyn_CulvertInspection.SysNo, dyn_CulvertInspection.INETNO, dyn_CulvertInspection.OUTETNO, CLng(IIf(IsNumeric([dyn_CulvertInspection].[Health Assess]),[dyn_CulvertInspection].[Health Assess],-1)), CLng(IIf(IsNumeric([dyn_CulvertInspection].[Health Assess]),[dyn_CulvertInspection].[Health Assess],-1))
HAVING (((Max(dyn_CulvertInspection.InspectionDate))>=[Forms]![District Report Subform by Date Range]![txtBeginDate] And (Max(dyn_CulvertInspection.InspectionDate))<=[Forms]![District Report Subform by Date Range]![txtEndDate]) AND ((CLng(IIf(IsNumeric([dyn_CulvertInspection].[Health Assess]),[dyn_CulvertInspection].[Health Assess],-1)))>=0 And (CLng(IIf(IsNumeric([dyn_CulvertInspection].[Health Assess]),[dyn_CulvertInspection].[Health Assess],-1)))<=79));

Answer
Well first, it shouldn't be prompting you at all for a date range since you should be inputting the range into a form. But if [i]District Report Subform by Date Range[/i] is actually a subform on another form, that would explain it, since you cant address that subform directly. It would have to be something like:

Forms!mainformname.FORM.subformname.controlname

I would also, tend to layer your queries instead of trying to do too much in one query. I would first create a query to do all your conversions, then use that to feed the next query until you get down to the data you need. THEN create the group by.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.