# Thread: Sample Total (2000)

1. ## Sample Total (2000)

The following line is from a sub that pulls a thousand-person sample stratified by state from a larger list. My problem involves the part of the routine that generates the number of people to pull from each state. The sub loops through a table and reads the number of people in each state (intStateDCCnt(i), and then figures the count to use using this formula (where lngDCTotal is set to the record count for the table with the entire population).

!Cnt2Use = CInt(intStateDCCnt(i) / lngDCTotal * 1000)

My problem is that after looping through all the states I end up with a total count of 1005, not 1000. I suspect an error with rounding. Is there a better function to use than CInt?

Kevin

2. ## Re: Sample Total (2000)

CInt rounds to the nearest integer. This leads to rounding up in some cases, rounding down in others. You can't predict in advance how the rounding errors add up. You'll have to pass through the records twice; in the second pass decrease Cnt2Use for the records with the largest relative rounding error until you arrive at the correct total.

Note: statistical applications such as SPSS are much more suitable for something like this. They have built-in sampling routines that take care of things like this with a single interactive command.

3. ## Re: Sample Total (2000)

I'm sorry, how do I know which which records have the largest relative rounding error?

Kevin

4. ## Re: Sample Total (2000)

You need to add a calculated field to the recordset you open:
Abs(CInt(intStateDCCnt(i) / lngDCTotal * 1000) - intStateDCCnt(i) / lngDCTotal * 1000) As RoundingError
and sort (descending) on this expression. Clear as mud now? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

5. ## Re: Sample Total (2000)

Well, it's a little clearer than mud. Thanks once again, Hans.

Kevin

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•