Selecting a set number of records from a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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..
 
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.
 
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
 
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]
 
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
 
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
 
Back
Top