Insert SQL query does not work

[leifoet]

From two existing tables
[li] Table A contains, for example, 20 members (fields : memberID, membernumber, firstname, lastname, …) [/li]
[li] Table B for example, contains 10 events (fields: activityID, location …)[/li]
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

 

[dhookom]

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

 

[leifoet]

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

 

[dhookom]

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

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

 

[leifoet]

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

 

[dhookom]

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