ms access query by insert a random record which not exist in other table

[jdlc]

I admit that I'm a novice in creating a query but I appreciate if somebody can share their knowledge to achieve my intension.

Here is my scenario, I want to insert a random record from Table1 which is not exist in Table2.

I found a query that insert a record from Table1 and does not exist in Table 2, but the problem with this query if I run it again it will give me the same result from the first time I run it. Below is the statement:

INSERT INTO tbl_table1 ( Field1, Field2, Field3 )
SELECT TOP 1 tbl_table2.[Field1], tbl_table2.[Field2], tbl_table2.[field3]
FROM tbl_table2
WHERE NOT EXISTS (SELECT tbl_table1.[Field1], tbl_table1.[Field2], tbl_table1.[Field3]
FROM tbl_table1
WHERE tbl_table1.[Field1] = tbl_table2.[Field1]);

I don't know where to insert the statement below so it will pick up a random record from tbl_table2, this statement will pick up a random record.
ORDER BY Rnd((INT(NOW*[tbl_table2.ID])-NOW*[tbl_table2.ID]))

Thanks you in advance and I appreciate for the help.

 

[theDBguy]

I'm not sure I follow. If the first time you run the insert query was successful, then the second time you run the query should exclude the record you already copied, which means you should get a different record from table 2. Are you saying you are still getting the same record from the first run inserted into table 1 again?

As for the random order by, try inserting it at the end of your WHERE NOT EXISTS select statement. e.g. WHERE NOT EXISTS(SELECT... ORDER BY...);

Hope that helps...

 

[The_Doc_Man]

The problem MAY be that the sub-query triggers the need for aliasing of table designators. Table 1 appears twice in the query, once in the INSERT leg and once in the sub-query leg, and they are used differently - once for an insert and once for a search. Also, your sub-query was a bit verbose. Assuming Field1 is your primary key, what I have shown below might be easier for selection purposes.

Maybe...

INSERT INTO tbl_table1 (Field1, Field2, Field3)
SELECT TOP 1 tbl_table2.Field1, tbl_table2.Field2, tbl_table2.Field3
FROM tbl_table2
WHERE tbl_table2.Field1 NOT IN
    (SELECT tx.Field1 FROM tbl_table1 AS TX ) ;

The "random" issue is somewhat of a problem. The order that something appears in a table doesn't matter. According to set theory (the basis for SQL operations), a query involves all records at once. You and I both know that the computer has to do things one at a time, but the theory says that when SQL gets back to you, you can't tell the difference in how it was done. What that means in practical terms is that inherently, tables are an unordered set. If you choose randomly from an unordered set, you are "gilding the lily" since they behave like they were effectively random already and you are probably wasting your time randomize again. Only queries have inherent or implied order of presentation. Tables? What you see is what you get. If tables have any predictable order at all, it is that the most recently inserted or modified record would be what you get with the LAST qualifier.

If you bloody-be-damned HAVE to have that random number, what you need to do is have a separate SINGLE field that you reload with a new random number each time you want to randomize the pick. Usually doing so is more trouble than it is worth. If you decide that your life won't be properly fulfilled without that randomness, come back and ask. Don't let me drown your spirits here, but this would not be a productive feature in the long term.

Now, the final question to be asked is this: Are tables tbl_table1 and tbl_table2 structured identically? Because normally you don't copy a record from point A to point B. I know you abstracted what you wanted to do, but it is just not normally done to duplicate a record in a normalized DB. If we know why you are doing this, we might be able to suggest a way to not have to move anything at all.

 

[jdlc]

I'm sorry to confused you guys, anyway the difference between table1 and table2, is the table 2 has a field name called ID which I use for the Rnd() function other than that everything are the same. The statement below is successful in picking a random record, but it doesn't validate if the record already exist in table2.

INSERT INTO tbl_table1 ( Field1, Field2, Field3 )
SELECT TOP 1 tbl_table2.[Field1], tbl_table2.[Field2], tbl_table2.[field3]
FROM tbl_table2
ORDER BY Rnd((INT(NOW*[tbl_table2.ID])-NOW*[tbl_table2.ID]))

pardon me if this is not crystal to you guys.

 

[Pat Hartman]

It is always better if you explain the purpose of something like this. Regardless, I have a sample that could help. It is a membership manager application. It uses a random number generator to choose a door raffle winner. Without knowing what you are actually trying to do this is the best example I have.

REMEMBER, you MUST seed the randomizer before using rnd()

 

[The_Doc_Man]

See my suggested "WHERE NOT IN (SELECT...)" clause to verify whether the record already exists. Either way you look at it, when you use a table name for two purposes in the same query, you have to isolate the uses with that alias (the AS clause) so that the SQL processor knows which cursor to use. The WHERE clause should precede the ORDER BY clause when both are in a query.

 

[plog]

Here is my scenario, I want to insert a random record from Table1 which is not exist in Table2.

How does a record exist in both tables? That is, what fields make it a duplicate? Can any of those fields be NULL?

Also, I think its time to move from generalities to specifics. Give us actual table and field names.

 

[jdlc]

Thank you for all the input you guys provided.

The alternative I did is to create a vba module:

• i open the table 1 recordset (with Rnd) and put the result in a variables
• with the result I pass Field1 as parameter to the recordset of Table2 to check if Field1 already exist
• if not then I add Field1, Field2 and Field3 in Table2

Though it takes a bit more time, but the result what I'm expecting.

Thanks again guys, till next time.