AC2007: Can I duplicate this Excel filtering in my query? (1 Viewer)

[EdNerd]

I have two queries that feed into a table; the first makes the table and the second appends to it. Then I query the table. My final query outputs names, actions, and dates.

Just due to the nature of appending the records, I will have records such as:
-- Name, Action, Date=Good
-- Name, Action, Date=Null

I'd like to find a way to filter my query so that if I have any record where ("Name"&"Date") duplicates any other records and Date = Null, that record is excluded.

I exported my data to Excel and did the following:
-- Sorted Name (Ascending), Action (Ascending), Date (Newest first)
-- Added a helper column with: =IF((B2&D2)=(B1&D1)," ","Good")
-- When filled down, any records with the same name and action but a blank date did not have "Good".
-- Filtering on the last column for "Good" gave me my desired results.

Can I duplicate this in my query? (Or even preferred at the table level?)

Ed

 

[James Dudden]

Using a 'Group By' on the query is a good way to remove duplicate data as long as any other fields are the same.

 

[Rx_]

So, if the Name, Action match - you only want to show the record with a "Date = good"?

Sorted Name (Ascending), Action (Ascending), Date (Newest first decending)
In design mode - choose the sigma (Totals)
They will all automatically Group By - in the last column Date choose Max (max date)

Here is the SQL for a table named aaaSorttest
The word "name" and "date" are reserved words so the column names are Name1, Action, and Date1

SELECT aaaSorttest.Name1, aaaSorttest.Action, Max(aaaSorttest.Date1) AS MaxOfDate1
FROM aaaSorttest
GROUP BY aaaSorttest.Name1, aaaSorttest.Action
ORDER BY aaaSorttest.Name1, aaaSorttest.Action, Max(aaaSorttest.Date1) DESC;

In addition, you can add the filter "good"

 

[EdNerd]

Thank you, Rx! It worked!!
I sorted my fields, and added Group By with Max on the date.

You guys are making me look good! :8>)
Ed