Select "X number" of most recent records

[baseball17bucks]

Hi,



I'm pretty new to all of this so please assume I know as little as possible when answering .

I have been tasked to determine the actual cost to build a product by summing the cost of the most recent required components to come off of our assembly line. For example, say I'm trying to determine the cost of a car with multiple sub-components (1 engine block, 4 doors, 4 wheels, 2 headlights, etc.). We make parts for all kinds of products (cars, boats, televisions, etc.). All of the data for ALL of these parts are contained in a table called PART_DATA (i.e. part ID, date started, date finished, hours spent, etc.).

Then I have a list of parts required to build a certain model car in a table called CAR_PARTS_LIST. This table contains data like (part ID, quantity needed, etc.).

If I want to know how much it would cost to build a car using the most recent car-part data, I need to pull data for the most recent required car parts to come off the assembly line that are required for a single car. That is, my car needs 4 wheels, so I need data for the most recent 4 wheels to come off the line.

How can I write a querey for the "4 most recent wheel records" given that "4" is specified in the CAR_PARTS_LIST?

Thanks for any help!

John

[John_G]

Hi -

It's easy to do if you know how many records you want, e.g. 4 - just use TOP n in the query SQL.

In you case for example, you might have something like:

Select top 4 field1, field2, ... from Part_Data where criteria ORDER BY [Date Finished] desc

Criteria is whatever you need to filter for the parts you want. [Date Finished] desc is what you will use to get the 4 most recent records that match the criteria.

Since it appears that the "4" is variable, i.e. coming from another table, you will probably have to use VBA to define and open the Query.

HTH

John