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]

You would need to check what the columns in your database are actually called before using them in the SQL query i.e. for this bit:

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

I have assumed the column name of your products is ProductID and the date column is called Date - you would need to check the actual names in your database and change them accordingly

Also based on your code, it looks like you are querying an Access database, I'm not entirely sure that the SQL syntax I provided works for that, I assumed it was a SQL server database you were querying

 

[DanSMT]

I am querying access from excel

 

[DanSMT]

Below is an example of the code that works but pulls everything.

I only want to pull each Part Number added on the most recent date.

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

The variable (var) is based on a textbox value which is derived earlier in code

For a = 1 To 24
var = Me("Part" & a)

 

[denzo36]

Why don't you just scrap the VBA and use power query or the Get Data to connect to your access database?

 

[DanSMT]

Why don't you just scrap the VBA and use power query or the Get Data to connect to your access database?

View attachment 65604

The operation is doing many other things all at once. I'm trying to automate something using a user form. Can't really post all of it, but the intent is to reduce the monotony of document generation.

 

[DanSMT]

Ok, So I think I have it doing something here, however it is returning some strange information back and not sure why.

Code below (The SQL section is the area of concern.) is pulling data VIA an Excel User Form from Microsoft Access Database.

The "Part" is C073061-D-F. This part value changes based on the users entry into the User Form.

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 RIDatabase WHERE [Date]=(SELECT MAX(Date) from RIDatabase WHERE [Part Number] = '" & var & "')"

The code is pulling incorrect part numbers, Field 2 is the Part Number.

Output from this code is as follows;

5/3/2022​	PRT-0000000672-100-B-F	B	E06		No	No	No	NA
5/3/2022​	PRT-0000000672-100-B-F	B	E06		No	No	No	NA
5/3/2022​	PRT-0000000672-100-B-F	B	E06		No	No	No	NA
5/3/2022​	PRT-0000000672-100-B-F	B	E06		No	No	No	NA
5/3/2022​	PRT-0000000672-100-B-F	B	E06		No	No	No	NA
5/3/2022​	BOS130008-A1-F	A1	E06		No	No	No	No
5/3/2022​	BOS130008-A1-F	A1	E06		No	No	No	No
5/3/2022​	BOS130008-A1-F	A1	E06		No	No	No	No
5/3/2022​	BOS130008-A1-F	A1	E06		No	No	No	No
5/3/2022​	BOS130008-A1-F	A1	E06		No	No	No	No
5/3/2022​	C073061-D-F	D	E06		No	No	No	No
5/3/2022​	C073061-D-F	D	E06		No	No	No	No
5/3/2022​	C073061-D-F	D	E06		No	No	No	No
5/3/2022​	C073061-D-F	D	E06		No	No	No	No
5/3/2022​	C073061-D-F	D	E06		No	No	No	No
5/3/2022​	PRT-0000000672-100-B-F	B	E06		No	No	No	NA
5/3/2022​	PRT-0000000672-100-B-F	B	E06		No	No	No	NA
5/3/2022​	PRT-0000000672-100-B-F	B	E06		No	No	No	NA

 

[Micron]

I'd almost given up on you trying that as per post 7 (?).
Perhaps because Date is a function, [Date] can be a field name, although I try very hard to avoid using reserved words. I also think more like

SQL = "SELECT * FROM RIDatabase WHERE [Date]=(SELECT MAX([Date]) from RIDatabase) AND [Part Number] = '" & var & "')"

Mainly the date selection should be returned by the subquery, the part criteria in the main query.

 

[DanSMT]

I'd almost given up on you trying that as per post 7 (?).
Perhaps because Date is a function, [Date] can be a field name, although I try very hard to avoid using reserved words. I also think more like

SQL = "SELECT * FROM RIDatabase WHERE [Date]=(SELECT MAX([Date]) from RIDatabase) AND [Part Number] = '" & var & "')"

Mainly the date selection should be returned by the subquery, the part criteria in the main query.

Still not functioning after modified to the above. If I change the "Date" Field name to "DT" what would this look like? And, would this assist with the reservation issue?

 

[denzo36]

Could you share what part numbers exactly you are trying to return e.g. a few examples?

you should be able to build that bit into the where clause in the SQL statement

 

[DanSMT]

Could you share what part numbers exactly you are trying to return e.g. a few examples?

you should be able to build that bit into the where clause in the SQL statement

In this specific instance, C073061-D-F is the only number I want to return (with maximum dates only of course).