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

Re: Access SQL Query: Find Oldest Entries for Each Entity 80 80

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

Re: Access SQL Query: Find Oldest Entries for Each Entity 80 80

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.

Re: Access SQL Query: Find Oldest Entries for Each Entity 80 80

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

Re: Access SQL Query: Find Oldest Entries for Each Entity 80 80

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;
Re: Access SQL Query: Find Oldest Entries for Each Entity 80 80

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… :(

Be a part of the DaniWeb community

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