Use results of Query as Parameter in Another Query

[muttnut]

I'm quite new to VBA and am having trouble figuring this out.

I've got a query that provides a list of users (QryUsers). Using the results of that query I would like to run an INSERT INTO TblResults SELECT TOP 10 * from another query (QryPREPOP). QryUsers and QryPrepop join on the field USER. The number of users in QryUsers can vary based on parameters set in that query.

[NeoPa]

Why not link the two queries in your APPEND query?

[muttnut]

I'm trying to limit my output to only 10 records per user in the user table (I'm creating a list of files to audit) and there can be many more records in my PREPOP table than 10 per user. The entire process as I foresee it is that the manager will run the users query to identify their direct reports and then this VBA job will pull 10 per person...trying to avoid the manager having to run an individual query for each direct report.

[zmbd]

Let's see if I understand:

You have say 150 supervisors
You want to pull some number, say 10 of these supervisors
each supervisor has a number of employees say 200
and you want to pull only 10 employees for each supervisor

Is this what you are after?

[muttnut]

Not quite....I have 100 managers and each of those managers has 25 direct reports. For each of those 25 direct reports I want to pull 10 files to audit (the PREPOP table contains a list of files the employees worked--which could be 50 or so per employee).

[zmbd]

Please don't be so litteral.
The numbers I used were arbitray and really don't matter.

The short, qualified answer is: Yes, provided your database schema is correctly designed.

You must have a way to indicate which directs are related to which supervisors and which directs worked on which project-document-whatever.

so a VERY simple schema might be (NOTE: I might not set a database up like this, this is just an example)

tbl_employee
[employee_pk]
[...]

tbl_directs
[directs_pk]
[directs_subordinate_fk_employee] 1:m w/tbl_employee
[directs_supervisor_fk_employee] 1:m w/tbl_employee

tbl_work
[project_pk]
[project_fk_employee]
[...]

Now, create a query against the tbl_directs that returns on [directs_supervisor_fk_employee] the supervisors that you desire = q_s

q_s now becomes the record set you join against a query on tbl_directs to return only the [directs_subordinate_fk_employee] with a matching [directs_supervisor_fk_employee] this is q_d

Take q_d and use it to join against a query on tbl_work so that only [project_fk_employee] matching the [directs_subordinate_fk_employee] on q_d are returned.

I am fairly certain that this could be written all in one go useing SQL.

THERE MAY BE BETTER SOLUTIONS
However, without an understanding of your database schema this is the best I can offer.

[muttnut]

Names are different, but that's basically how I have it set up now....a table with managers and their direct reports and a table with the files which includes the employee associated with the file. What I'm trying to get to is a select top 10 * from the files table for each employee in the employee/manager table.

[zmbd]

setup the queries as given, in the final query use the TOP predicate (you can use this in the other queries too)

ie:
This is a query on a table of 26 people:

SELECT TOP 10 tbl_people.people_pk
FROM tbl_people;

It will return 10 of them

or
same table

SELECT TOP 10 PERCENT tbl_people.people_pk
FROM tbl_people;

In this case it returns 3 people, rounds up.

Now mind you, this is not a random number... it is the first ten records or the first 10 percent of the records, if you need a random selection that's a horse of a different colour:

You can have a simple random generator or get into some heavier stuff with weighted scores > http://bytes.com/topic/access/answer...ed-probability

[muttnut]

thanks--I already checked with my customer and they are ok with it being the first 10 rather than a random 10. Thanks for your help