Selecting a set number of records from a table

[ExMrBlonde]

I have 2 tables, one which maintains UserId's and required sample sizes, the
other contains the full population of records (including a User ID field).
I would like to be able to run an append query into a new table which only
selects the required sample size of records per User Id from the full
population.
Any help would be gratefully accepted..

 

[Duane Hookom]

Do you have any sample records and table structure you would like to share?
I can't imagine what you have and what you need.

 

[ExMrBlonde]

Apologies..

tblUsers contains a UserId field and a SampleSize field (approx 100 Users,
samplesize is never >20)
tblData contains a total population of data (including a User Id) from which
I would like to select the number of records per UserId as indicated in
tblUsers. (hope this makes sense)
This query would then append the selected data to a new table, which would
be used to work on.

Thanks in advance...
Steve

 

[Duane Hookom]

If I understand correctly (you didn't take the time to post any sample
records), I think you can create a table of numbers (tblNums) with a single
numeric field (Num) and values 1 - 100. Use this table in your append query
with the tblUsers and set the criteria under the SampleSize field to:
<=[Num]

 

[Tom Ellison]

Dear Steve:

If you add a running sum to the tblUsers and have a ranking of the rows in
tblData, this can be done directly.

tblUsers
SampleSize RunningSum
4 4
3 7
1 8
5 13
etc.

In both cases, the ordering is essential to produce the proper result.

If you need help with the SQL for this, I would need a query of each on
which to add this.

Tom Ellison

 

[Tom Ellison]

Dear Steve:

Better yet, make the running sum the total of the previous rows instead:

tblUsers
SampleSize RunningSum
4 0
3 4
1 7
5 8
etc.

There is slightly less math involved this way.

It is essential that the ordering of both tables be unique, or the solution
is ambiguous.

Tom Ellison