Identify and select row if entry appears for first time in column based on criteria? (SQL)

Question

I am using VBA and ADODB.Connection to extract data from an Access database. I would like to select data from the database if an entry in a specific column appears for the first time. This is best explained by example so here is a sample table I quickly made in Excel, let's pretend the table is called "DrinkTable"

I would like to extract transactions that took place after 9/1/2020 by a new name (it was the first time that name ever purchased a drink, there name never appears in the name column before 9/1/2020).

I am able to write an SQL statement that pulls all the entries after 9/1/2020 using: "SELECT [Name], [Drink], [Cost], [Date] FROM [DrinkTable] WHERE [Date] >= #"9/1/2020#" ORDER BY [ID] DESC; but that does not give what I am attempting. The correct output I am looking for is:

Sample output

Can someone help me refine my SQL statement to only extract data if the entry in the [Name] column did not appear before "9/1/2020?"

Answer 1

You can use not exists to filter out rows whose Name appeared before the split date:

SELECT [Name], [Drink], [Cost], [Date] 
FROM [DrinkTable] d 
WHERE 
    [Date] >= #"9/1/2020#" 
    AND NOT EXISTS (
        SELECT 1
        FROM [DrinkTable] d1
        WERE d1.[Date] < #"9/1/2020#" AND d1.[Name] = d.[Name]
    )
ORDER BY [ID] DESC