0

I have 2 tables, Tags and Certs, in a one-to-many relationship - each tag will have anywhere between 0 and 10+ certs associated with it.

Tags                       Certs
[Tag] [Other fields]       [Tag]    [Date]     [Length]
A-22       ...             A-22    03/05/17      265
P-63       ...             A-22    15/02/14      331
...                        A-22    09/12/12      656
                           P-63    01/02/12      1024
                           ...

Is there a way to return the most recent, second most recent, third most etc. length for each Tag as different fields?

My ideal end product would look something like:

[Tag] [Most recent length] [Second most recent] [third most] ...
A-22          265                  331              656
P-63          1024
...

This issue is especially grating since I recently ported it from MySQL, which has the LIMIT clause which solved this easily. I'm aware there is no direct equivalent (from MS Access Limit), but I'm wondering if there is some other way to achieve what I need.

Count Quizzical
  • 111
  • 4
  • Just wondering, but what about a [a crosstab query](https://support.office.com/en-us/article/make-summary-data-easier-to-read-by-using-a-crosstab-query-8465b89c-2ff2-4cc8-ba60-2cd8484667e8?ui=en-US&rs=en-US&ad=US) ? Maybe you could cross data of every of your tags with dates in columns, from most recent to older one – Foxfire And Burns And Burns Jul 23 '18 at 11:26
  • Sorry, just realised I need length values, not date. Question edited – Count Quizzical Jul 23 '18 at 12:01
  • The solution would be the same probably. From min to max – Foxfire And Burns And Burns Jul 23 '18 at 13:30
  • This is not easy in Access. I think you can get what you want by creating a [Ranking query](http://allenbrowne.com/ranking.html#query) and then using that as the data source for a crosstab. – HansUp Jul 23 '18 at 17:12
  • @HansUp, Foxfire: Thank you! I was able to find a solution using those links. Posting it as an answer. – Count Quizzical Jul 25 '18 at 09:16

1 Answers1

1

Thanks to HansUp and Foxfire, I was able to find a solution. Posting here so it can maybe help others having a similar problem.

First step was to construct a ranking query from the Certs table:

SELECT t1.[Tag], t1.[Date], t1.[Length], COUNT(t1.[Date]) AS Rank
FROM [Certs] AS t1, [Certs] AS t2
WHERE (t1.[Date] <= t2.[Date]) AND t1.[Tag] = t2.[Tag]
GROUP BY t1.[Tag], t1.[Date], t1.[Length]
ORDER BY t1.[Tag];

Which gave the result:

[Tag]    [Date]     [Length]   [Rank]
A-22    03/05/17      265        1
A-22    15/02/14      331        2
A-22    09/12/12      656        3
P-63    01/02/12      1024       1
...

From there a simple crosstab query constructed from the wizard with [Tag] as row headers, [Rank] as column headers, and MAX([Length]) as values, produced the desired table in the question.

Count Quizzical
  • 111
  • 4