Challenge -- Select # records based on another query

[sluggercoach]

I have a challenging issue... I have 2 tables. One contains engines that are available to build by a specific part number. The other contains a schedule of which part numbers need to be built. I have been asked to take the schedule table, count the number of engines by part number (for instance 8 of part A, 10 of part B, etc) and from that, choose the engines from the available engine list based on it. The available engine list has the list of engines in first in, first out order. So, if I need 8 of part A and 10 of Part (I know how to count them from the schedule table), I need my program to automatically choose the 1st 8 of Part A (based on date in) and the 1st 10 of Part B and display them in one list. So essentially, I need to know how to create a query dynamically that will select the Top 8 or Top 10 based on the schedule. I am sure there are lots of complicated ways to do this but any ideas on the most simple and quick way?

 

[ajetrumpet]

i think the best way would be to upload a db for people to look at and fix for you

 

[sluggercoach]

haha. Do I sense a little sarcasm there?? I have the database and everything set up, I just need to know the best way to create the query to select the top -- however many -- based on the value from another query. I am sure I can figure out but just wondering if anyone had any thought.s

 

[ajetrumpet]

haha. Do I sense a little sarcasm there?? I have the database and everything set up, I just need to know the best way to create the query to select the top -- however many -- based on the value from another query. I am sure I can figure out but just wondering if anyone had any thought.s

i'm not really sure if there is a way you can do this. the parameter would have to be used, but i don't think u can use a param as a value from another query. the params are inputs. maybe you could put the number of recs you want on a form and then concat it into an SQL statement in the code? I know this is possible, but probably take the most time.