Syntax error with INSERT INTO

[Luigi_Cortisone]

HI there

Can someone help with my syntax error. There seems to be a problem on the 'INSERT INTO...' line

I'm going to build quite a complex series of nested if statements here. Would I be better with a CASE statement? Can someone direct me to some good examples that would work in this situation

Thanks, Luigi

Sub Build_Table2() DoCmd.RunSQL "DROP TABLE table2;"DoCmd.RunSQL "CREATE TABLE table2([id] integer, [category] TEXT(80));"DoCmd.RunSQL "INSERT INTO table2 SELECT table1.id, &_" If table1.posn = "OH" And table1.level = 2 Thentable2.Category = "Heavy"Elsetable2.Category = "Light"End If End Sub

 

[pbaldy]

Welcome to the site. Something got goofed with the formatting of your code. Can you repost it with the code tags so it's more readable?

 

[Luigi_Cortisone]

How does this look?

[SIZE=2]
Sub Build_Table2()

DoCmd.RunSQL "DROP TABLE table2;"
DoCmd.RunSQL "CREATE TABLE table2([id] integer, [category] TEXT(80));"
DoCmd.RunSQL "INSERT INTO table2 SELECT table1.id, &_"

If table1.posn = "OH" And table1.level = 2 Then
table2.Category = "Heavy"
Else
table2.Category = "Light"
End If

End Sub
[/SIZE]

 

[pbaldy]

Molto bene. Try this:

DoCmd.RunSQL "INSERT INTO table2 SELECT table1.id, IIf(posn = 'OH' And level = 2, 'Heavy', 'Light') FROM Table1"

Which is a bit of a guess as to what you want inserted, and is total air-code so may not work. I suspect you'll have to specify the destination fields.

 

[Luigi_Cortisone]

Gracie, a few questions
Where do you put the destination field? This is table2.category
Note you've used the query syntax for 'If' (IIf), I thought vba required 'if, else' type code
Are you able to give an alternative based on a case statement rather than ifs

Cheers, Luigi

 

[pbaldy]

The destination fields go like so:

INSERT INTO table2(id, category) SELECT...

VBA is passing the IIf() to JET to process the SQL, which can handle it. If it's getting more complicated, I'd build the string before executing it. In your case it would be tricky, since you don't know the values needed to determine the inserted value at this point, since they come from the table. Generically it would look like:

Dim strSQL As String

strSQL = "INSERT INTO table2(id, category) SELECT id, "

Select Case Whatever
  Case 1
    strSQL = strSQL & "'Heavy'"
  Case 2
    strSQL = strSQL & "'Light'"
End Select

strSQL = strSQL & " FROM Table1"

CurrentDb.Execute strSQL

Which is totally off the top of my head, so beware. Maybe if you can describe in more detail what you're trying to accomplish, we can come up with a better solution. Since your values are coming from a table, other options might include storing the various options in another table so you can just join that table into the query and get the insert value that way, or creating a function that is called from the query and returns the appropriate value.

I'm off to meet my wife at an Italian restaurant, so I'll check back later. Chianti will probably have been consumed at that point, so consider this fair warning of nonsensical replies.

 

[Luigi_Cortisone]

Hey Paulo, watch that Chianti!

Basically I'm trying to build a second table with two fields (id, category) the id direct from the first table and a derived value 'category' based on the combination of two values (posn and level) in the first table.

Caprice?

Gracie

 

[pbaldy]

How many combinations are there? Is it possible to build a table with the possible combinations, like:

posn   Level    Value
OH       2       Heavy
OO       3       Light

Long term, having them in a table makes the db more flexible than building them into code.

 

[Luigi_Cortisone]

Hi there
I may not have made this clear
The fields I want are in an access table, in this example table1. These fields in combination will be used to add the value 'category' to the field in table2
I'm not hard coding anything, just using a nested 'if' or a case statement to populate the 'category' field in table2, which is created by the 'create table' method
Thanks for your help

 

[pbaldy]

No, it's me who hasn't been clear. When you create the nested If or case statement, I would call that hard coding the solution, because if anything changes you have to change your code. My thinking was that if we put the options in a "lookup" table like the above, your can use that table along with the values from table1 to populate table2. That way, if/when the options change, you just change the values in the lookup table, and everything else just flows with it. Clear as pesto sauce now?

 

[Luigi_Cortisone]

Hey that sounds potentially quite exciting, just update the lookup table
Any help with this approach would be appreciated
Like looking into the bottom of a tequila bottle!
Gracie

 

[pbaldy]

Easiest if you can post a sample db with your table1 and this new table in it, with the appropriate fields filled out. By the time I see the bottom of the tequila bottle, I can't see the bottom of the bottle...or anything else.

 

[Luigi_Cortisone]

Hey there

I've been battling bravely to upload a small snapshot of the dB without success.

Can I give a few more details in the hope you can stitch something together?

The ‘id’ field is to be inserted into the new table (table2) together with its category which is derived from a combination of the ‘class’ and the ‘size’ fields according to the following rule

Class = Pole or XArm and size 25 or less, then category = ‘small overhead’
Class = Pole or XArm and size over 25 and less than or equal to 50 then category = ‘medium overhead’
Class = Pole or XArm and size over 50 then category = ‘large overhead’
Class = Berm or Room and size 25 or less, then category = ‘small underground’
Class = Berm or Room and size over 25 and less than or equal to 50 then category = ‘medium underground’
Class = Berm or Room and size over 50 then category = ‘large underground’


Muchos Gracie, Luigi

 

[pbaldy]

Maybe this will help you:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[Luigi_Cortisone]

Hey Paulo
No luck zipping the file. I'll try from my other poota tonight
Cheers, Luigi

 

[Luigi_Cortisone]

Hi there Paul

Here I go uploading the database. Hopefully you can make sense of it. Its just a small snapshot but should give you enough to guide me through

Gracie again

Luigi

 

[pbaldy]

Is this what you're after?

 

[datAdrenaline]

Hello Luigi ...

So ... how are you going to use this table? ... Why do you need it? ... Is it a temp table? ...

Seems to me that you should add a column to Table2 named CategoryID and have it be the ForeignKey to a table with a structure like this ..

tblCategories
-------------
CategoryID (Autonumber/PrimaryKey)
Category (Text/50) {Values like: "small overhead";"medium overhead" ... }

Then as you have a type, class, and size, you can look up the category through a SELECT statement with table1 and tblCategories JOINED to eachother and filtered to retrieve the what you are looking for.

....

 

[raskew]

Hi -

Or another option:

INSERT INTO table2 ( id, category )
SELECT table1.id, Switch([Size]<=25,"Small ",[Size]<=50,"Medium ",[Size]>50,"Large ",True,"xxx") & IIf([type]="OH","Overhead","Underground") AS Category
FROM table1;

Tested it and it works.

HTH - Bob

 

[Luigi_Cortisone]

Grazie molto guys

I used Paul's model and it worked come un fascino! I'm building those lookup tables like theres no tomorrow.

Cheers Luigi