SQL Query - Name and Date search in one SQL query

[DanSMT]

All,

I'm trying to pull the last items entered from the latest date added from my Database

Not sure how to tackle two statements at once.

There can be 10 or more items with the same ID number on the same date, which I would like to receive all of them. There can also be only 1 occurrence in some cases.

Current code is below. This pulls are entries based on a part number, but I would like to pull only the items that were entered on the last recorded date.

SQL = "SELECT * FROM RIDatabase WHERE [Part Number] = '" & var & "'"

 

[denzo36]

In the SQL statement, you could partition by ID number and order by date descending - then select the row number = 1 like the below - doing this will take the latest date for each product ID

SELECT *

FROM

(
SELECT
ProductID
,ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Date DESC) AS RowNum

FROM YourTable Name

) temp

WHERE temp.RowNum = 1

 

[Micron]

Your actual date values are going to be important here. If you're not real familiar with date/time type table fields I suggest you create a test query and use a calculated field such as
test: Format([yourDateFieldNameHere],"dd/mm/yyyy hh:nn:ss")
What you see in a field because of formatting doesn't always reflect what is really there. If the time is all zeros then you can use Max of your date field in the SELECT portion of you sql and write it as a Totals query. However, you would have to specify the fields individually as you can't use aggregate functions with * wildcard. So maybe like

"SELECT fld1, fld2, etc., Max(RIDatabase.yourDateField) AS MaxOfDate FROM
RIDatabase WHERE RIDatabase.[Part Number] = '" & var & "' GROUP BY RIDatabase.[Part Number]"

Or you can use a subquery that finds the Max date and keep the wildcard. If your times are not zeros, you'll have to use a function that ignores the time. Fix(yourDateField) might work.

 

[DanSMT]

Your actual date values are going to be important here. If you're not real familiar with date/time type table fields I suggest you create a test query and use a calculated field such as
test: Format([yourDateFieldNameHere],"dd/mm/yyyy hh:nn:ss")
What you see in a field because of formatting doesn't always reflect what is really there. If the time is all zeros then you can use Max of your date field in the SELECT portion of you sql and write it as a Totals query. However, you would have to specify the fields individually as you can't use aggregate functions with * wildcard. So maybe like

"SELECT fld1, fld2, etc., Max(RIDatabase.yourDateField) AS MaxOfDate FROM
RIDatabase WHERE RIDatabase.[Part Number] = '" & var & "' GROUP BY RIDatabase.[Part Number]"

Or you can use a subquery that finds the Max date and keep the wildcard. If your times are not zeros, you'll have to use a function that ignores the time. Fix(yourDateField) might work.

Thanks. I will give this a shot. Explanation is a bit out of my realm as I am still learning this portion.

 

[DanSMT]

When you state fld1, fld2. etc. is this implying I have to manually select each field in the row?

 

[DanSMT]

Ok I believe the fld area is where im stuck. Not understand what it is implying.

 

[Micron]

Explanation is a bit out of my realm as I am still learning this portion.

If that means you don't understand what I'm saying about times then consider that no matter how you sort or just find the Max, if you have 2 records like
Something | 01/01/2022 02:00:00 PM
Otherthing | 01/01/2022 03:00:00 PM

you will only retrieve Otherthing even though they are on the same day.

you can't use aggregate functions with * wildcard.

Max is an aggregate function, so yes, each field individually. Or get the max date as a subquery ( a SELECT inside of a SELECT ) as post 2 suggests.

Or you can use a subquery that finds the Max date and keep the wildcard.

 

[denzo36]

This will do what you need - assuming you only care about the latest date and not the latest time and date.

SELECT *

FROM

(
SELECT
ProductID
,Whatever other fields you need to include here
,ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Date DESC) AS RowNum

FROM RIDatabase

) temp

WHERE temp.RowNum = 1
AND [Part Number] = '" & var & "'"

 

[DanSMT]

This will do what you need - assuming you only care about the latest date and not the latest time and date.

SELECT *

FROM

(
SELECT
ProductID
,Whatever other fields you need to include here
,ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Date DESC) AS RowNum

FROM RIDatabase

) temp

WHERE temp.RowNum = 1
AND [Part Number] = '" & var & "'"

Is there a way to do the same thing as this, but pull the entire recordset? Just seems like a lot of hastle adding all of the individual fields (100 +).

 

[DanSMT]

I'm getting the following error when running the script.

'Declaring variables.
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dbPath As String
Dim SQL As String
Dim var
'add error handling
On Error GoTo errHandler:
'Disable screen update.
Application.ScreenUpdating = False
'clear worksheet
Worksheets("Data").Range("A2:ZZ10000").ClearContents
'get the path to the database
dbPath = "C:\Users\dans\Documents\Dest2\Database.accdb"

Set cnn = New ADODB.Connection

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";Jet OLEDB:RIDatabase"

For a = 1 To 24
var = Me("Part" & a)
'exit sub if textbox is empty
If var = "" Then
GoTo done:
End If

SQL = "SELECT * FROM (SELECT Supplier, [Part Number], Date, Rev, [LL A], [N A], [UL A], [A], [LL B], [N B], [UL B], [B], [LL C], [N C], [UL C], [C], [LL D], [N D], [UL D], [D], [LL E], [N E], [UL E], [E], [LL F], [N F], [UL F],ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Date DESC) AS RowNum FROM RIDatabase) temp WHERE temp.RowNum = 1 AND [Part Number] = '" & var & "'"

'Create the ADODB recordset object.
Set rs = New ADODB.Recordset 'assign memory to the recordset

rs.Open SQL, cnn

'Check if the recordset is empty.
If rs.EOF And rs.BOF Then
'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'Enable the screen.
Application.ScreenUpdating = True
'In case of an empty recordset display an error.
MsgBox "There are no records in the recordset!", vbCritical, "No Records"

Exit Sub
End If

'Write the recordset values in the sheet.
Worksheets("Data").Range("A1").CopyFromRecordset rs

'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'loop
Next a

done: