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 & "'"

 

[Micron]

Based on your data above, this

SELECT tblSheet10.[F2], tblSheet10.[F1], *
FROM tblSheet10
WHERE (((tblSheet10.[F2])="C073061-D-F") AND ((tblSheet10.[F1])=(SELECT MAX([F1]) AS MaxOfDate FROM tblSheet10 AS T)));

gives me this

F2	F1	F3	F4	F5	F6	F7	F8	F9
C073061-D-F	5/3/2022	D	E06		No	No	No	No
C073061-D-F	5/3/2022	D	E06		No	No	No	No
C073061-D-F	5/3/2022	D	E06		No	No	No	No
C073061-D-F	5/3/2022	D	E06		No	No	No	No
C073061-D-F	5/3/2022	D	E06		No	No	No	No

If I change one record date to the 4th I get only one record

F2	F1	F3	F4	F5	F6	F7	F8	F9
C073061-D-F	5/4/2022	D	E06		No	No	No	No

 

[Micron]

I didn't notice that when playing with design view I ended up with 2 fields and *. Edited to this, which seems OK and puts the fields in correct order.

SELECT *
FROM tblSheet10
WHERE (((tblSheet10.[F2])="C073061-D-F") AND ((tblSheet10.[F1])=(SELECT MAX([F1]) AS MaxOfDate FROM tblSheet10 AS T)));

If that doesn't seem to work, make sure your variables are correct. Debug.Print the sql into the immediate window to check. Of course you'll have to edit to suit your table and field names.

 

[DanSMT]

Based on your data above, this

SELECT tblSheet10.[F2], tblSheet10.[F1], *
FROM tblSheet10
WHERE (((tblSheet10.[F2])="C073061-D-F") AND ((tblSheet10.[F1])=(SELECT MAX([F1]) AS MaxOfDate FROM tblSheet10 AS T)));

gives me this

F2	F1	F3	F4	F5	F6	F7	F8	F9
C073061-D-F	5/3/2022	D	E06		No	No	No	No
C073061-D-F	5/3/2022	D	E06		No	No	No	No
C073061-D-F	5/3/2022	D	E06		No	No	No	No
C073061-D-F	5/3/2022	D	E06		No	No	No	No
C073061-D-F	5/3/2022	D	E06		No	No	No	No

If I change one record date to the 4th I get only one record

F2	F1	F3	F4	F5	F6	F7	F8	F9
C073061-D-F	5/4/2022	D	E06		No	No	No	No

the areas where you defined "tblesheet10" these areas are actually pulling from an Access database. Fields are [Date] and [Part Number].

Can I just assume that changing these to the corresponding fields that this will still function as intended? And will it pull all lines with the maximum date if the dates are all the same?

 

[Micron]

the areas where you defined "tblesheet10" these areas are actually pulling from an Access database.

Is that a question? Don't understand what it means. The sql is in an Access database and so is the table that it's querying. The reason you got F1 et al is because I was too lazy to type out column headers when I copied your data .

And will it pull all lines with the maximum date if the dates are all the same?

Does the 1st table above not answer that?

 

[DanSMT]

Is that a question? Don't understand what it means. The sql is in an Access database and so is the table that it's querying. The reason you got F1 et al is because I was too lazy to type out column headers when I copied your data .

Yes this was a question, my apologies. The data in the table was data that was already pulled from the database. I was trying to show how it is being exported.

Does the 1st table above not answer that?

I think so. Sorry learning here.

 

[DanSMT]

Re-wrote to suit tie to the access database. Going to test this.

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

 

[DanSMT]

Nope. Doesn't work. I must be missing something here. I've removed the RIDatabase. from each fieldname designator, but dont think that was the problem.

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

 

[Micron]

I cannot help but get the feeling that you read/absorb little of what I post or at least don't bother to provide feedback, and I'm basing that from post 7 on, not just the last couple.

If that doesn't seem to work, make sure your variables are correct.

Debug.Print the sql into the immediate window to check.

Did you do either? What did the sql look like? Suggest you post the output from the immediate window, or swap var part with a real value and test. I sure hope that RIDatabase is your table name. It's not real clear.

 

[DanSMT]

RIDatabase is the table within Access.

I'm building the this query in Excel VBA. I am doing so because this operation has numerous other functions.

I do not know what you mean by debug.print.

We may be working in two different programming languages.

Once again I'm learning this all from scratch and am trying to automate a quite large process.

It will probably be much easier for me to start from scratch and provide some examples. This will take me a few.

 

[Micron]

I tested in Access and responded because you posted in the Access forum. The only way I'd know how to run such a query from Excel is by using automation (where I control Access from within Excel) although I used to do it from Excel using MSQuery, which I suppose has been replaced by Power Query or whatever it's called. The sql syntax is the same regardless, although the sql statement might be different if connections to the data from one app to another are at play. I created a spreadsheet and linked to it from Access and ran the sql from there but I don't think that matters.

If you don't know what something means, there is always Google? You can debug.print from vb editor in any Office app that supports code so the app is of no consequence in testing output. I don't see how starting from scratch will help much unless you start addressing unknowns, such as what the heck var might contain. If that takes Google to get you there, then I have to wonder why that isn't part of the exercise.

I would try using your code from post 10 and try to do it the other way (from Excel) to see what happens but since I don't have your form I have no idea what var contains so I think I'll put that off for now. You could put a break point on this line: If var = "" Then, run the code and mouse over var to see what it is as an alternate method to Debug.Print.