Access error: 'Query input must contain at least one table or query'

[johnmerlino]

Hey all,

I have this query:

INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )
VALUES ((SELECT update_mailer_step_two.id
FROM update_mailer_step_two), (SELECT mailer_states.id FROM mailer_states WHERE mailer_states.mailer_state = 'sent'), Now());

When I try to run ti, I get the error message 'Query input must contain at least one table or query'.

I think problem has to do with the VALUES and SELECT statement not working together as intended. Any solutions?



Thanks for response.

[NoellaG]

Hi John,

the INSERT INTO... VALUES ... is a valid Oracle/SQL server statement, but Access SQL syntax can sometimes work different on several points.

Try something like:

INSERT INTO Tablename (Field1, Field2, ..., Fieldn)
SELECT F1,F2,.... Fn
FROM Table1 INNER JOIN (or other join expression) Table2 on JoinFieldA = JoinfieldB
WHERE .....



success
NG

[johnmerlino]

I tried this:

INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )
VALUES (DLookup("id","update_mailer_step_two"), DLookup("id","mailer_states" & "mailer_state = 'sent'"), Now());

And it gives me error 'unknown'.

And then I tried this:

Private Sub Command6_Click()


        Dim CFF_ID As String, MS_ID As String, strSQL As String
 
        CFF_ID = "SELECT update_mailer_step_two.id FROM update_mailer_step_two"

        MS_ID = "SELECT mailer_states.id FROM mailer_states WHERE mailer_states.mailer_state = 'sent'"

        strSQL = "INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )VALUES ((" & CFF_ID & "),(" & MS_ID & "),NOW())"
 
        DoCmd.RunSQL strSQL

End Sub

And this gives me "Query input must contain at least one table or query".

The suggestion you provided in post above won't work because there's nothing that joins the tables.

Thanks for response.

[johnmerlino]

@NoellaG

None of the attempts work and probably cannot work. Your inner join thing may work, but I don't know how to apply that in my situation. Can you please elaborate on this:


INSERT INTO Tablename (Field1, Field2, ..., Fieldn)
SELECT F1,F2,.... Fn
FROM Table1 INNER JOIN (or other join expression) Table2 on JoinFieldA = JoinfieldB
WHERE .....

[johnmerlino]

If this is what you mean:

INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )
SELECT update_mailer_step_two.id, mailer_states.id, Now()
FROM update_mailer_step_two INNER JOIN mailer_states ON update_mailer_step_two.id = mailer_states.ID
WHERE mailer_states.mailer_state = 'sent';

while it doesn't produce any errors, it it appends 0 rows, presumably because there is no INNER JOIN link between update_mailer_step_two and mailer_states.
mailer_states table is just a table that contains a mailer_state field with text. It's pretty much just a lookup table that never changes.
update_mailer_step_2 contains one field called id, which contains a bunch of ids associated with contacts in database. Hence, there's no relationship at all between mailer_states and update_mailer_step_2.

[NoellaG]

Hi,


maybe try something like:

INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )
SELECT DLookUp("ID","update_mailer_step_two") AS MailerID, mailer_states.ID, Now() AS DateTimeInserted
FROM mailer_states
WHERE mailer_states.mailer_state = "sent";

[johnmerlino]

Thanks for response.

While this works:

INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )
VALUES (DLookup("id","update_mailer_step_two"), DLookup("id","mailer_states", "mailer_state = 'sent'"), Now());

This only creates a single record in mailers table. I want it to go the length of update_mailer_step_two table, so if there are 20 records in update_mailer_step_two table, then 20 records will be created in mailers, each having the current time, a mailer_state of sent, and the current value of id of update_mailer_step_two table. Thanks for response.

[johnmerlino]

I tried using INNER JOIN but while this produces no error, it appends 0 rows, presumably because there is no INNER JOIN link between update_mailer_step_two and mailer_states:

INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )
SELECT update_mailer_step_two.id, mailer_states.id, Now()
FROM update_mailer_step_two INNER JOIN mailer_states ON update_mailer_step_two.id = mailer_states.ID
WHERE mailer_states.mailer_state = 'sent';

mailer_states table is just a table that contains a mailer_state field with text. It's pretty much just a lookup table that never changes.

update_mailer_step_2 contains one field called id, which contains a bunch of ids associated with contacts in database. Hence, there's no relationship at all between mailer_states and update_mailer_step_2.

INNER JOIN won't work. Any other solution?

[NoellaG]

see my last response, no inner join there

[johnmerlino]

see my last response, no inner join there

Thanks for response, but that only creates one record as well. I want it to go the length of update_mailer_step_two table, so if there are 20 records in update_mailer_step_two table, then 20 records will be created in mailers, each having the current time, a mailer_state of 'sent', and whatever the current value of id (each record has a different id value) of update_mailer_step_two table.

[johnmerlino]

Thanks for response. I attached an example of the database.

The ultimate goal is to have the mailers table record mailers that have been sent and who they been sent to. What makes it a little complicated is that two tables have the same names but one contains initials and one doesn’t. However, UpdateQuery will contain some from each table, even though they refer to same person. So initial objective is to grab the ids of the records from both tables and then remove repeats. Hence, I will only need to focus on one table (contacts_first_filter), since both tables refer to the same people. So by this point update_mailer_step_two is created with the ids which correspond to the contacts located in contacts_first_filter. Then the query update_mailer_fourth_step is supposed to grab the ids located in update_mailer_step_two and place them in contacts_first_filter_id field of mailers table. This will ultimately happen in a button click so when button is clicked, the user is expecting to not only write those ids to mailers table, but to also have a corresponding 1 (since they are clicking button to indicate mailer sent) written to the mailer_states_id field. Also, the current time is written to created_at field. Note that while this may seem like four queries to run, I intend to have all those queries part of one VBA function for when the user clicks button.

Thanks for response.