Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query Max Date Whether Selecting or Not

Status
Not open for further replies.

misscrf

Technical User
Jun 7, 2004
1,344
US
I have a form with a subform to allow users to run a series of reports (subquery in grid view). The main form has multiple list boxes. They are all multi-select. All of them are values that users can select to filter what they want to report for, and 1 is to select the fields that the query will select and group by.

My issue is the the users have told me they only want the max date of one of the sub records to pull up. They may not want this to show, but they don't want all the records to show, just the most recent. This is the code that runs the report based on the listbox choices selected:

Code:
 Private Sub cmdSummary_Click()
 Dim Mysql As String
 Dim strCriteria As String
 Dim strFields As String
 Dim strQuery As String
 Dim lbo As ListBox
 Dim itm
 Dim i As Long
 strQuery = "qryCarLocationReportFields"
 Set lbo = Me.lstFields
 If Me.lstFields.ListIndex = -1 Then
 For i = 0 To Me.lstFields.ListCount - 1
 Me.lstFields.Selected(i) = True
 Next
 For Each itm In lbo.ItemsSelected
 strFields = strFields & "[" & lbo.ItemData(itm) & "], "
 Next
 strFields = Left(strFields, Len(strFields) - 2)
 Else
 For Each itm In lbo.ItemsSelected
 strFields = strFields & "[" & lbo.ItemData(itm) & "], "
 Next
 strFields = Left(strFields, Len(strFields) - 2)
 End If
 strCriteria = "1=1 "
 strCriteria = strCriteria & _
 BuildIn(Me.LstCarStatus, "[Car Status]", "'")
 strCriteria = strCriteria & _
 BuildIn(Me.lstDealer, "Dealer", "'")
 strCriteria = strCriteria & _
 BuildIn(Me.lstLocation, "Location", "'")
 Mysql = "SELECT  " & strFields & " FROM  qryCarLocationReport Where " & strCriteria & " Group By " & strFields
 CurrentDb.QueryDefs(strQuery).SQL = Mysql
 Me.frmSubStatusCarQry.SourceObject = "QUERY." & strQuery
 Me.frmSubStatusCarQry.Visible = True
End Sub

Basically, there are records for cars. They are set as being with 1 dealer, and they can be at any location. The location can change and there is an effective date for when a car is moved to a new location. When the users are running reports based on different cars, they only want the record for the most recent location to show up, whether they are showing that date or not. Any ideas how I can add this to the main query?

thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot
 
You just need to use the MAX() function in SQL.

So, something like:

Code:
strSQL = "SELECT l.CarID ,Max(l.LocationDate) AS [LocationDate] " & Chr(13) & _
"FROM LocationTable l " & Chr(13) & _
"WHERE l.CarID = " & MyForm.CarIdField

That's what you'd want to set for your record source... well, you would of course edit it to fit your needs, adding whatever fields, renaming the example fields, etc..

I'm just assuming you wanted to do it in VBA, that's why I included it the way I did.

Or am I missing something?
 
Oh, I did miss something... the GROUP BY..
Code:
strSQL = "SELECT l.CarID ,Max(l.LocationDate) AS [LocationDate] " & Chr(13) & _
"FROM LocationTable l " & Chr(13) & _
"WHERE l.CarID = " & MyForm.CarIdField & Chr(13) & _
"GROUP BY l.CarID "
 
the only problem is that I need it, in a sense, to be the where or criteria of what is chosen but I can't always choose it as a select. If you look at my vba, I am dyanmically building the criteria as the user decides through the listboxes. Whether they choose to select that date or not, I need to only pull the records for that max date for each car.

Make sense?

misscrf

It is never too late to become what you could have been ~ George Eliot
 
You can use a HAVING clause... at least I think you can use that in Jet SQL, I know you can on SQL Server...

Well, maybe that'd work... but maybe not... I know I've done this before.. It might require a subquery for this portion... so just a moment, and I'll type something like that up and post it... and by then, someone else will have a better answer. [wink]
 
Oh, nevermind, I think my brain is on fire at the moment, ready to burst into flames. Got too many things floating around in there...

Well, I'm still wondering why you HAVE to have it in the WHERE clause at all.. I'm actually thinking you can instead have a subquery in the FROM clause, and use that as your condition... basically, you'd be selecting from records that match the subquery... and in the subquery is wher eyou'd have the max...

Either that, or you could stick the subquery in your where clause - not sure which will work best....

Here's what I was working on with that thought in mind.... disregard if it just causes more confusion..
Code:
strSQL	=	"SELECT	x.CarID ,x.LocationDate	" & Chr(13) & _
			"FROM	LocationTable l1
					INNER JOIN
			( " & Chr(13) & _
			"			SELECT l.CarID ,Max(l.LocationDate) AS [LocationDate] " & Chr(13) & _
			"			FROM LocationTable " & Chr(13) & _
			"			GROUP BY l.CarID " & Chr(13) & _
			") x " & Chr(13) & _
			"		ON l1.CarID = l1 "

It's a good thing I'm nearing the end of my work day! [smile]
 
I guess I will have to see how that would work, and thanks for looking at this again. I guess my concern is I don't have any hard coded fields in the select or where. It is all built by the user when they run it, dynamically.

They choose the fields to select and independently choose the fields for the where criteria. In order to force the records produced to only give the max location date I am unsure how I do that. It is really tough because some queries they run, may not choose to be querying anything about the location at all, in which case It wouldn't make sense to have the max date be part of the query. I guess I am really just confused at how to accomplish what it is they want.

misscrf

It is never too late to become what you could have been ~ George Eliot
 

Well, I think all you need is a SQL String for your "from" statement that will always be static... that SQL string will be selecting the MAX LocationDate for each automobile. Then you dynamically build the main SQL statement off the variables. Do you already have the code built for finding all "selected" controls, and taking the variables from there to build that portion of your SQL statement? If not, I'd imagine that'll be the bigger, and possibly more entertaining, aspect of the whole thing. Well, some would say that's a sick form of entertainment - figuring out what code to use to automate tasks. [wink]

On the other hand, you could also just create an Access query (if it makes it easier for you) that pulls the AutomobileID and the MaxLocationDate into it... it's simple to build there... of course, the basic SQL statement would be simple as well...

So, here's another thought at it, depending upon how you're doing it:
Code:
Private Sub BuildMyQuery()
  Dim frm as Form
  Dim ctl as Control
'One String variable for each SQL Clause that can be put together at the end.
  Dim strSQL As String
  Dim strSQL_Select As String
  Dim strSQL_From As String
  Dim strSQL_Group As String
  Dim strSQL_Order As String

  Set frm = CurrentDb.Forms("MyForm")

  For Each ctl in frm.Controls
    If TypeOf ctl is TextBox Then
      If ctl.Text = vbNullString Then
      Else
        If strSQL_Select = vbNullString Then
          strSQL_Select = "SELECT a." & ctl.Name & " "
        Else
          strSQL_Select = strSQL_Select & "," & ctl.Name & " "
        End If
      End If
  Next ctl

  'You'll need to build each piece dyanmically, fit in where you need to...
  

  'Then at the end, you'll put together the pieces as they are... assuming they include something..

  strSQL = strSQL_Select & Chr(13) & _
           strSQL_From & Chr(13) & _
           strSQL_Where & Chr(13) & _
           strSQL_Group & Chr(13) & _
           strSQL_Order

  MySubForm.RowSource = strSQL

  strSQL = vbNullString
  strSQL_Select = vbNullString
  strSQL_From = vbNullString
  strSQL_Group = vbNullString
  strSQL_Order = vbNullString
  Set ctl = Nothing
  Set frm = Nothing
End Sub

Okay, please understand this code is not complete... and I just typed it as I sat here looking at this thread, so it may have an error or two anyway...

See if you think this is the way you want to go, and I think I can help you further if need be..
 
Howdy misscrf . . .

Have you considered using the [blue]Top[/blue] predicate! You'd have to order descending on your date field and indicate the number of records you want returned:
Code:
[blue]SELECT TOP 5 ...
FROM [i]TableName[/i]
ORDER BY [i]TableName.FieldName[/i] DESC;[/blue]
For purposes of readability I've crunched and formatted your code. This is just for our reading here. However, you may want to note some of the changes:
Code:
[blue]   Dim SQL As String, Cri As String
   Dim Flds As String, qryCLRF As String
   Dim LBx As ListBox, itm, i As Long
   
   Set LBx = Me.lstFields
   qryCLRF = "qryCarLocationReportFields"
   
   If LBx.ListIndex = -1 Then
      For i = 0 To LBx.ListCount - 1
         LBx.Selected(i) = True
      Next
   End If
      
   For Each itm In LBx.ItemsSelected
      [purple][b]If Flds <> "" Then Flds = Flds & ", "
      Flds = Flds & "[" & LBx.ItemData(itm) & "]"[/b][/purple]
   Next
      
   Cri = "1=1 "
   Cri = Cri & BuildIn(Me.LstCarStatus, "[Car Status]", "'")
   Cri = Cri & BuildIn(Me.lstDealer, "Dealer", "'")
   Cri = Cri & BuildIn(Me.lstLocation, "Location", "'")
   
   SQL = "SELECT  " & Flds & " " & _
         "FROM  qryCarLocationReport " & _
         "Where " & Cri & " " & _
         "Group By " & Flds & ";"
   CurrentDb.QueryDefs(qryCLRF).SQL = SQL
   
   Me.frmSubStatusCarQry.SourceObject = "QUERY." & qryCLRF
   Me.frmSubStatusCarQry.Visible = True[/blue]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top