CODEWITHSUNDEEP
sqlms-access
Rian
Rian

Reputation: 201

Get cell value and use it in an INSERT statement (MS ACCESS)

I'd like to insert a record using values from different tables

just to illustrate:

INSERT INTO tbl1 
VALUES([value1], [value2], NOW())

Here's how I'm trying to do it:

INSERT INTO transactions(itemid, userid, tdate) 
VALUES((SELECT TOP 1 ID FROM items ORDER BY RND(ID)), (SELECT TOP 1 ID FROM users ORDER BY RND(ID)), (NOW()))

But this throws an error:

Query input must contain at least one table or query

Any help would be greatly appreciated.

Thanks!

Upvotes: 0

Views: 193

Answers (1)

trincot
trincot

Reputation: 350705

You could rewrite your statement to use a SELECT instead of VALUES:

INSERT INTO  transactions(itemid, userid, tdate) 
    SELECT   TOP 1 items.ID, users.ID, NOW() 
    FROM     items, users
    ORDER BY Rnd(-(1000*items.ID*users.ID)*Time()), 
             items.ID, users.ID

Edit: I added the ORDER BY clause which will lead to more random sort orders. The negative value will ensure a sort of randomize. See also this question.

Edit2: extended ORDER BY clause to ensure TOP 1 will not have to deal with ties.

Upvotes: 3

Related Questions