limited records past date

[ablim]

I have a query that returns a set of records which details stock items that are older than a date given in a form. However i need to limit the results to the number of items held in stock
i.e say i want to look at stock over 1 year old, i get a list of all the stockids, and the date added.
Say there is a stock level of 3 for a particular stockid the results should be limited to the first 3 records that are over 1 year old. Rather than the whole list of dates I get now.

Any help or direction with searching terms would be appreciated

thanks

 

[Fuga]

In SQL, you use the "top" statement, combined with the method of sorting.

select top 3 ....... order by .....desc.

In design view, you click the summation sign and enter the number of records you want shown. Make sure you have the right sorting too. You will also need to include your where clause to select only those records that are a year old.

Fuga.

 

[ablim]

Thanks, I did look into the TOP SQL statement but its too rigid. I need to be able to vary the the number of top items dependent on the stock level associated to the stockid.

i.e. if i have 5 in stock of Item with stockid 11111 and need to return the last 5 dates and if i have 3 of stock item 22222 i need the last 3 dates.

Once i get the answer to that the rest should fall into place