Last four records or each item

Hi,

I have a database that lists each time stock items are purchased, who
they are purchased from and the date of that purchase.

I have a query that list those purchases, it's linked to the order
also to grab the purchase date.

The problem I have is that I've been asked to run a query that looks
up the last four purchase prices for each item, then get the highest
price of each to show as the base price for the database (I'll then
add their markup % to that).

I've successfully managed to list the last four in ascending order for
each individual item [using the Top Values facility in the query], but
I can only list all items or the tope four of ALL, not the tope four
of each, I need to get these last four of each before I consider
moving on to the top price of the last four!

Any ideas if this is possible? I've been around in circles so many
times I beginning to think perhaps I can only do it for individual
items, not the whole database!

Many thanks,

Jon

Re: Last four records or each item

J-P-W wrote in
news:0abf18c9-5708-4df1-8442-
64120df3877a@s19g2000prg.googlegroups.co
m:

> Hi,
>
> I have a database that lists each time stock items are purchased,
> who they are purchased from and the date of that purchase.
>
> I have a query that list those purchases, it's linked to the order
> also to grab the purchase date.
>
> The problem I have is that I've been asked to run a query that
> looks up the last four purchase prices for each item, then get the
> highest price of each to show as the base price for the database
> (I'll then add their markup % to that).
>
> I've successfully managed to list the last four in ascending order
> for each individual item [using the Top Values facility in the
> query], but I can only list all items or the tope four of ALL, not
> the tope four of each, I need to get these last four of each
> before I consider moving on to the top price of the last four!
>
> Any ideas if this is possible? I've been around in circles so many
> times I beginning to think perhaps I can only do it for individual
> items, not the whole database!
>
> Many thanks,
>
> Jon
>
What you have to do is get the top four items for each article as a
sub-form or sub-report of your list of individual articles.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Re: Last four records or each item

See:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"J-P-W" wrote in message
news:0abf18c9-5708-4df1-8442-64120df3877a@s19g2000prg.google groups.com...
> Hi,
>
> I have a database that lists each time stock items are purchased, who
> they are purchased from and the date of that purchase.
>
> I have a query that list those purchases, it's linked to the order
> also to grab the purchase date.
>
> The problem I have is that I've been asked to run a query that looks
> up the last four purchase prices for each item, then get the highest
> price of each to show as the base price for the database (I'll then
> add their markup % to that).
>
> I've successfully managed to list the last four in ascending order for
> each individual item [using the Top Values facility in the query], but
> I can only list all items or the tope four of ALL, not the tope four
> of each, I need to get these last four of each before I consider
> moving on to the top price of the last four!
>
> Any ideas if this is possible? I've been around in circles so many
> times I beginning to think perhaps I can only do it for individual
> items, not the whole database!
>
> Many thanks,
>
> Jon

Re: Last four records or each item

On 30 Dec, 06:45, "Allen Browne" wrote:
> See:
> =A0 =A0 Subquery basics: TOP n records per group
> at:
> =A0 =A0http://allenbrowne.com/subquery-01.html#TopN
>
> --
> Allen Browne - Microsoft MVP. =A0Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "J-P-W" wrote in message
>
> news:0abf18c9-5708-4df1-8442-64120df3877a@s19g2000prg.google groups.com...
>
>
>
> > Hi,
>
> > I have a database that lists each time stock items are purchased, who
> > they are purchased from and the date of that purchase.
>
> > I have a query that list those purchases, it's linked to the order
> > also to grab the purchase date.
>
> > The problem I have is that I've been asked to run a query that looks
> > up the last four purchase prices for each item, then get the highest
> > price of each to show as the base price for the database (I'll then
> > add their markup % to that).
>
> > I've successfully managed to list the last four in ascending order for
> > each individual item [using the Top Values facility in the query], but
> > I can only list all items or the tope four of ALL, not the tope four
> > of each, I need to get these last four of each before I consider
> > moving on to the top price of the last four!
>
> > Any ideas if this is possible? I've been around in circles so many
> > times I beginning to think perhaps I can only do it for individual
> > items, not the whole database!
>
> > Many thanks,
>
> > Jon- Hide quoted text -
>
> - Show quoted text -

Allen, 'Gawd Blimey' as us Londoners might say,

followed by a Gawd Bless You Guv'n'r

It does exaclty what I was hoping to do, thank you VERY much.

Jon

Re: Last four records or each item

Ah, she's right mate.

Gotta git t' London t'ear this fo' m'self! :-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Allen, 'Gawd Blimey' as us Londoners might say,

followed by a Gawd Bless You Guv'n'r

It does exaclty what I was hoping to do, thank you VERY much.

Jon