Member Avatar for jclast

Hey, I've got a databae in which each record has seven dates associated with it, and I need to pull out each record's ID and its associated most recent date.

Here's my first attempt, but Access doesn't like it at all.

SELECT id, MAX(initialCertDate, recertDate1, recertDate2, recertDate3, recertDate4, recertDate5, recertDate6) AS certDate FROM cert

Any help would be appreciated.

  • 2 Contributors
  • 1 Reply
  • 95 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by redsa
Member Avatar for redsa

You could write a query like this:

SELECT id, MAX(mydate) AS MaxDate
FROM
(
SELECT id, initialDate AS mydate FROM cert
UNION
SELECT id, recertDate1 AS mydate FROM cert
UNION
SELECT id, recertDate2 AS mydate FROM cert
) DERIVEDTBL

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.