selecting "X" number of records

[nmoakeh]

Hi,
Still a bit new to things so please bear with me and thanks in advance
for any/all suggestions.

I'm trying to figure out a way/query that would be on a form to select
any inputed number of records that someone is looking for. Example:

in table1 there may be 5000 records, but the user only wants to select
50 records from that table to be moved into table2. this should leave
4950 records in table1 now. then say that user wants to select 300
more records to move into table2. now table2 should have 350 records
and table1 should have 4650. how would i create a query or queries to
do this to have the user to select whatever number of records they'd
want from table1 and move into table2?

once again, thanks in advance!

 

[giloosh]

Does it matter which records will be added, or just 50 random records?
If so... try adding a new "autonumber" field to table one which will
number all of the fields from 1-5000 in your example. we will name this
field count_id for this example. Than you make a new append query that
will append all of the fields in table1 which meet the criteria
(count_id >49 and < 101) into table2. than you make a delete query with
the same criteria. Delete all fields in table1 where count_id >49 and <
101
I just used numbers 49-101 as an example but for your scenario you can
create a form with two input fields asking to insert a range of records
to add from table1 to table2.

I hope this made sense and good luck

 

[nmoakeh]

hi giloosh,

well tables 1 and 2 are actually temp tables with one field, where
table1 is already randomized from a query that originally created it.
so i'd just need the user to input on a form or prompt "x" records and
it could just take from the top going down since table1 is already
randomized. does that make sense?

thanks again.

 

[giloosh]

Try this:
make a form with a blank textbox named Text0 and a button.
apply this code to the Click event of the button.

############## start here ##################

x = Me.Text0

'insert x amount of records from table1 to table2

insertTable2 = "INSERT INTO TABLE2 (FIELD1) SELECT TOP " & x & " FIELD1
FROM TABLE1"

'delete x amount of records from table1

deletetable1 = "DELETE * FROM TABLE1 " _
& "WHERE FIELD1 IN " _
& "(SELECT TOP " & x & " FIELD1 FROM TABLE1)"


DoCmd.RunSQL (insertTable2)
DoCmd.RunSQL (deletetable1)

############## end here ##################

this code is assuming you have Table1 and Table2 both with a field
named Field1.