Need help creating a data query to eliminate irrelevent data

[suprdave]

Hello all,



Please bear with me as I'm new to Access queries. I am trying to eliminate irrelevant data. Here's the scenario.

Table name = ColorChoice

Field 1 = RequestDate
Field 2 = RequestName
Field 3 = Color
Field 4 = LogEntry (time record was created)

Fields 1 and 2 may have duplicate data, but fields 3 and 4 will contain different data for each combination.

How can I display only the last record for each set based on newest LogEntry for each set?

The column containing "show" was added to demonstrate which record should output from my query.

RequestDate	RequestName	Color	LogEntry
1/1/2016	Ted	Red	12/28/2015 20:15
1/1/2016	Ted	Green	12/29/2015 08:13
1/1/2016	Ted	Blue	12/29/2015 10:04	Show
1/1/2016	Jim	Red	11/16/2015 07:14
1/1/2016	Jim	Green	11/18/2015 09:30	Show
1/2/2016	Wendy	Red	10/31/2015 12:15	Show
1/2/2016	Nancy	Red	9/18/2015 09:26	Show
1/3/2016	Ted	Red	1/1/2016 19:45	Show
1/4/2016	Dave	Red	12/30/2015 08:17
1/4/2016	Dave	Green	12/31/2015 14:55	Show

Thanks,

Dave

[JoeM]

One way is to do this is a set of two queries (or a single nested query).

First, create an Aggregate Query, where you just have three fields: RequestDate, RequestName, and LogEntry.
When you click on the Totals button (looks like a Sigma), it will add a Totals row under each field. Leave the values as "Group By" for the first two fields, but change the value for LogEntry to Max. This will return the latest LogEntry for each RequestDate/RequestName combination.

Now, create a second query where you join your original table to the Aggregate Query you created above, joining on ALL three fields. You can then return any/all of the fields from the original table that you want to display, and it should see that it will only return the records you listed above in your example.

[orange]

suprdave,

Can you give us an overview description of the "business" your database is intended to support? Getting your tables and relationships to match your business is key to database.

[suprdave]

This database will feed data to a web calendar displaying employee time-off requests. The source data is a log file from our payroll provider.

[JoeM]

Did you try my suggestion?

[suprdave]

Looking at it now. Thanks for the fast response. Will report my results shortly.

[suprdave]

Thanks JoeM!
That did the trick.
Dave