×
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!
  • Students Click Here

*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.

Students Click Here

Jobs

Insert SQL query does not work

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!

    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