INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Insert SQL query does not work

Insert SQL query does not work

(OP)
From two existing tables
  • Table A contains, for example, 20 members (fields : memberID, membernumber, firstname, lastname, …)
  • Table B for example, contains 10 events (fields: activityID, location …)

  • I try to insert some of the fields (from this tables A and B) in a new Table C using the following SQL query

    INSERT INTO tabelC (date, activity, memberId, status, Init)
    SELECT [date?] AS Date, (SELECT description FROM TableB) AS Activity, tabelA.membernr, [Status?] AS status, (left (tabelA.firstname, 1) & "" & left (tabelA.lastname, 1)) AS init
    FROM TableA
    ORDER BY tabelA.membernr;

    After each run of the query I want to see in table C:
    - 20 new records = members from Table A
    - in each record the same activity (e.g. activity 7) from table B => in each run of the query I want to have the opportunity to manually select a specific activity (e.g. activity 4 or 8 ...) from table B => as a dropdown menu in the query

    How to create a working query with that dropdown menu ?

    Without "(SELECT description FROM TableB) AS Activity" INSERT does work
    Thanks for correct syntax - helptips

    RE: Insert SQL query does not work

    I wouldn't go any further until you replaced [activity] in tableC with the activityID.

    You can't have dropdowns in a query. You should create a form for selecting values for your query. Build an SQL string based on user input and then run it from a command button.

    Duane
    Hook'D on Access
    MS Access MVP

    RE: Insert SQL query does not work

    (OP)
    Thanks dhookom for the tips - .
    I will try this but I do not understand (good) what you mean by [Build an SQL string based on user input and then run it from a command button]
    Is it possible to receive some more explanation about this.
    Thanks

    RE: Insert SQL query does not work

    You would need to write some VBA code that would be run when a command button is clicked. The code might look like:

    CODE --> vba

    Dim strSql as String
    strSQL = "INSERT INTO tabelC ([date], activityID, memberId, status, Init) " & _
        "SELECT [date?], " & Me.cboActivityID & ", MemberID, Status, Init  " & _
        "FROM TableA " 
    debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError 
    I have no idea where you want to get the values to fill the date, status and Init fields.

    Duane
    Hook'D on Access
    MS Access MVP

    RE: Insert SQL query does not work

    (OP)
    date, status => must be fields in table B (in my test I did the input manualy)
    Init => comes from fields in table A => (left (tabelA.firstname, 1) & "" & left (tabelA.lastname, 1)) AS init

    RE: Insert SQL query does not work

    If you have the primary key values from table A and table B then you generally have no reason to store other fields from A and B in table C.

    Duane
    Hook'D on Access
    MS Access MVP

    Red Flag This Post

    Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

    Red Flag Submitted

    Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
    The Tek-Tips staff will check this out and take appropriate action.

    Reply To This Thread

    Posting in the Tek-Tips forums is a member-only feature.

    Click Here to join Tek-Tips and talk with other members!

    Resources

    Close Box

    Join Tek-Tips® Today!

    Join your peers on the Internet's largest technical computer professional community.
    It's easy to join and it's free.

    Here's Why Members Love Tek-Tips Forums:

    Register now while it's still free!

    Already a member? Close this window and log in.

    Join Us             Close