Access SQL Query: Find Oldest Entries for Each Entity

[Holger.HBB]

Hello!

I have a question and I don’t know whether the title is actually descriptive.

For some background on the matter, we use Sage 50 as our merchandise information system and for our reporting and controlling tools we run Access queries. Since our database had been reset at some point I am missing some history data. I need to find the creation date for each article. But the only query covering the whole period since the beginning will return a table like that:

PK | ART | DATE
3262 | 000 | 17.05.2013
3262 | 000 | 23.01.2019
3150 | 0003 | 03.06.2013
3150 | 0003 | 23.01.2018
3150 | 0003 | 43.05.2019
(where PK is the article’s primary key, ART its article number and DATE the date of the last update)

The query looks like this:

SELECT *
FROM (SELECT sg_auf_artikel_history.SG_AUF_ARTIKEL_PK AS PK, sg_auf_artikel.ARTNR AS ART, sg_auf_artikel_history.AKTDAT AS DAT
FROM sg_auf_artikel_history INNER JOIN sg_auf_artikel ON sg_auf_artikel_history.SG_AUF_ARTIKEL_PK = sg_auf_artikel.SG_AUF_ARTIKEL_PK
GROUP BY sg_auf_artikel_history.SG_AUF_ARTIKEL_PK, sg_auf_artikel.ARTNR, sg_auf_artikel_history.AKTDAT)
ORDER BY ART, DAT;

How can I get the following result:

PK | ART | DATE
3262 | 000 | 17.05.2013
3150 | 0003 | 03.06.2013

That is, I only want for each article the oldest entry.

I’d appreciate your solutions!

Thanks in advance!

Holger

[rproffitt]

I think you are close to the answer. My thought is to use your SELECT and a LIMIT 1 with the ORDER BY date with the DESC or ASC keyword as you see fit. Light reading at https://www.1keydata.com/sql/sqlorderby.html

[Holger.HBB]

Thank you for your answer even though it is somewhat off the mark. I specifically mentioned that I’m using Access, so LIMIT is out of question. I tried its Microsoft version SELECT TOP.

There are only two places where TOP can be applied to (it has to come after either one of the SELECT statements) and in both cases it does return the top rows of the overall selection but not the top rows of each article.

[rproffitt]

As to LIMIT, I use SQL so my thoughts are there. If TOP is the keyword you can try TOP 1 on line 3 after the FROM keyword. The Access syntax is not one I work with so you have to work that out. Example: https://stackoverflow.com/questions/4551112/access-database-limit-keyword

[vbacoder1962]

Try using the below syntax:

SELECT *
FROM (SELECT sg_auf_artikel_history.SG_AUF_ARTIKEL_PK AS PK, sg_auf_artikel.ARTNR AS ART, sg_auf_artikel_history.AKTDAT AS DAT
FROM sg_auf_artikel_history INNER JOIN sg_auf_artikel ON sg_auf_artikel_history.SG_AUF_ARTIKEL_PK = sg_auf_artikel.SG_AUF_ARTIKEL_PK
GROUP BY sg_auf_artikel_history.SG_AUF_ARTIKEL_PK, sg_auf_artikel.ARTNR, sg_auf_artikel_history.AKTDAT)
WHERE #2013-04-01# < DATE < #2013-04-30#
ORDER BY ART, DAT;

[Holger.HBB]

Unlike LIMIT the Access clause TOP is necessarily linked to the SELECT statement, in the meaning that TOP always follows immediately the SELECT statement. At least as far as I can tell…

And the idea of using a WHERE on the date won't work either. The dates are sometimes like this:
PK | ART | DATE
3262 | 000 | 17.05.2013
3262 | 000 | 23.01.2014
3150 | 0003 | 03.06.2015
3150 | 0003 | 23.01.2018
3150 | 0003 | 43.05.2019

And I still want the first line for each PK:
PK | ART | DATE
3262 | 000 | 17.05.2013
3150 | 0003 | 03.06.2015

I’m out of ideas… :(