Access select random records split list

I have a list that I want to split into two segments. The records are random expect that I have one field "LANG" that contains either "EN" or "FR" and I need an equal number from each in the resulting two lists.

ie:
Original Table
------------------
5000 Records Total
3000 are "EN"
2000 are "FR"

New Table 1
------------------
2500 Records Total
1500 are "EN"
1000 are "FR"

New Table 2
-------------------
2500 Records Total
1500 are "EN"
1000 are "FR"


This make sense?
High points for urgency :)
LVL 1
nickleplatedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NievergeltSenior SW DevCommented:
- Add an Autonumber column (e.g. rec_num) into Original_Table.

- Execute the following SQL statements:

   SELECT TOP 1500 * INTO New_Table1
   FROM Original_Table WHERE lang = 'EN';

   INSERT TOP 1000 * INTO New_Table1
   FROM Original_Table WHERE lang = 'FR';

   DELETE FROM Original_Table WHERE rec_num IN (SELECT rec_num FROM New_Table1);

- (Maybe) rename Original_Table
0
chanitoCommented:
Ok, add the rec_num for identification of the records, or don't if you already have an ID, and then for a pseudo-random solution:

select top 1500 rand(datepart(ms, getdate())*(abs(checksum(*))/100000000)) as order1, * into New_Table_1
from Original_Table where lang ='EN' order by order1
select top 1000 rand(datepart(ms, getdate())*(abs(checksum(*))/100000000)) as order1, * into New_Table_1
from Original_Table where lang ='FR' order by order1
select * into New_Table_2 from Original_Table where rec_num not in (select rec_num from New_Table_1)

If you get an error expanding the '*' try using instead any changing field in the table, like the name of the person.  Just anything that changes.
0
nickleplatedAuthor Commented:
hmmm..seems like we're getting close, but I'd like to be able to run this on any table; Meaning in my example I was arbitrarily putting numbers of each to make a point.

I'd like to run it now matter how many records there are in the table and in each lang segment.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chanitoCommented:
Piece of cake...  you just need to use top 50 percent :

select top 50 percent rand(datepart(ms, getdate())*(abs(checksum(*))/100000000)) as order1, * into New_Table_1
from Original_Table where lang ='EN' order by order1
0
nickleplatedAuthor Commented:
Getting Syntax error(missing operator) in query expression rand(datepart(ms, getdate())*(abs(checksum(*))/100000000))
0
chanitoCommented:
That's weird, I copy / paste in my Query Analyzer and works fine, you might be missing a parentheses there.  Try changing the * in checksum(*) with another field like checksum(name).
0
nickleplatedAuthor Commented:
nada - sorry.

( don't forget that I am using access for this, not SQL - not sure if that makes a dif)
0
chanitoCommented:
Yes it does... I'm sorry but checking the list of questions in the Database section I can't tell which DB you're using.

In Access there isn't a checksum function.  If you have a numeric id_field you can use:

rnd(rec_num)

Or just use any numeric field you have.  I think that's the only difference with Access, so resuming it should be:  

select top 50 percent rnd(rec_num) as order1, * into New_Table_1
from Original_Table where lang ='EN' order by order1
0
nickleplatedAuthor Commented:
Sorry, but I can't get it to fly.

Trying:

select top 50 percent rnd(ID) as order1, * into New_Table_1
from F_Donors where lang ='EN' order by order1


And it prompts me for ID Parameter

0
chanitoCommented:
Looks like you don't have an ID field in the F_Donors table ...
0
nickleplatedAuthor Commented:
doh - embarassing - I'd changed the ID field name to myID

select top 50 percent rnd(myID) as order1, * into New_Table_1
from F_Donors where myLANG ='EN' order by order1

now prompts me for order1 parameter
0
chanitoCommented:
Try:

select top 50 percent * into New_Table_1
from F_Donors where myLANG ='EN' order by rnd(myID)

Or keep the rnd(myID) as order1 in the fields list if you want to see it ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nickleplatedAuthor Commented:
Sorry, comes up with "You are about to paste 0 row(s) into a new table"
0
chanitoCommented:
Try the select query without the into clause first...

That's a bit strange.  The problem could be the myLANG = 'EN' condition, maybe the value is different?  Try just:

select top 50 percent * from F_Donors where myLANG ='EN' order by rnd(myID)   or
select top 50 percent * from F_Donors order by rnd(myID)

Also, I found out you can't see the rnd(myID) value because if you include it in the fields and in the order by it actually generates 2 different random values, however give it a try and you'll see it does randomize the sort order.
0
nickleplatedAuthor Commented:
Sorry, I am a jerk ( working on way too many different things today)

The values in "myLANG" were actually "E" or "F"

Thanks for your patience....points well earned :-S
0
chanitoCommented:
Thanks, and don't worry.  We all have those days... :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.