looking to return 2nd/5th/10th from last record

[NicholasP]

I have 2 tables, UST_PXs and MTG_PXs and I'm trying to return the date in MTG_PXs based on the ID in UST_PXs...I have been trying DMAX without luck and I can get the right record using MAX, but that's an aggregate and I can't just tack a -2 or -5 on the end of it. Does anyone have any suggestions? I am very new to Access but I'm eager to learn.

Thanks
Nick

 

[CJ_London]

It would help if you posted what you have at the moment

 

[NicholasP]

Right now I'm trying to modify this sql statement

SELECT .COID, [A].[company name], .Value
FROM [A] INNER JOIN ON [A].COID=.COID
WHERE (Select Count(*) from as S where Value >= .Value AND COID = .COID) = 2
ORDER BY .COID;

from here:

"How do I get the second highest value?" from this site (would post the link, but it says I need to have 10 posts or greater)

to get what I'm after, but it's not working

 

[jdraw]

To get the second highest value in general(untested)

Select top 1 yourfield from yourTable
where yourfield not in (Select top 1 yourfield from yourTable)
order by yourfield desc

 

[NicholasP]

Hmmmm, so I was able to use this:

SELECT TOP 2 UST_PXs.ID, MTG_PXs.KeyDate
FROM UST_PXs INNER JOIN MTG_PXs ON UST_PXs.ID = MTG_PXs.ID
WHERE UST_PXs.ID NOT IN (SELECT TOP 1 UST_PXs.ID FROM UST_PXS)
ORDER BY UST_PXs.ID DESC;

But it generates the last 2 records. I only want the 2nd to last record...

 

[jdraw]

Use top 1 as I said in the post.

 

[NicholasP]

If I use this:

SELECT TOP 1 UST_PXs.ID, MTG_PXs.KeyDate
FROM UST_PXs INNER JOIN MTG_PXs ON UST_PXs.ID = MTG_PXs.ID
WHERE UST_PXs.ID NOT IN (SELECT TOP 1 UST_PXs.ID FROM UST_PXS)
ORDER BY UST_PXs.ID DESC;

I get the latest record (10/17/2014). I'm looking to return the second to last record (10/16/2014).

Thanks
Nick

 

[CJ_London]

You need to alias and order the subquery

SELECT TOP 1 UST_PXs.ID, MTG_PXs.KeyDate
FROM UST_PXs INNER JOIN MTG_PXs ON UST_PXs.ID = MTG_PXs.ID
WHERE UST_PXs.ID NOT IN (SELECT TOP 1 ID FROM UST_PXS [COLOR="Red"]AS P ORDER BY ID DESC[/COLOR])
ORDER BY UST_PXs.ID DESC;

 

[jdraw]

The concept is this:

You find the highest (Top 1) of something. Then you SELECT the top 1 of the remaining
eg Not In (SELECT TOP 1...) and that gives you the second highest.

Do you have some sample data from each table? And what exactly you want in the end.
If you can post a zip of your database(remove anything confidential), I'll look.

Ooops: I see CJ has responded.

 

[CJ_London]

sorry, couldn't resist

 

[CJ_London]

but just noticed the alias is in the wrong place - original post corrected

 

[NicholasP]

This worked, thank you very much. Not sure why it worked (even though I understand it conceptually), though I think that's more a matter of syntax.

Specifically, if anyone could shed some light on "AS P ORDER"...I've not seen that before.

Again, thanks a bunch!

Nick

 

[CJ_London]

SELECT TOP 1 ID FROM UST_PXS AS P ORDER BY ID DESC

This aliases UST_PXS as P

You could just as easily had

UST_PXS AS Tmp

 

[CJ_London]

and the reason you need to alias is because the UST_PXS table is in your main query - so if you didn't alias it the subquery would assume you were referring to the table in the main query, and not another version of it.

The only time you don't need to alias is if the table referred to in the subquery is not in the main query, but personally I always alias.