Stuck with SQL in VBA

[Steph]

Hello

I am trying to use SQL to run queries in our access database in order to (hopefully) speed things up. I'm trying to create code that basically takes data from one table and inserts it into another whilst doing calculations on the data.

However I can't get past this:

 Private Sub Test_Click()
Dim strSQL As String
 
strSQL = "CREATE TABLE [TempRedAmberGreen]" & _
"AS (SELECT " & _
"[ID_CHK] String," & _
"[Red] String," & _
"[Amber] String," & _
"[Green] String)" & _
"FROM [035 - Meter Point HH Data];"
 DoCmd.RunSQL strSQL
 
End Sub

Can anyone tell me where I'm going wrong? It keeps saying "Run-time error '3292': Syntax error in field definition.

Thanks

 

[namliam]

strSQL = "CREATE TABLE [TempRedAmberGreen]" & _
"AS (SELECT " & _
"[ID_CHK] ," & _
"[Red] ," & _
"[Amber] ," & _
"[Green] " & _
"FROM [035 - Meter Point HH Data])"

Note the different place of the last )

Please dont just copy data because there needs to be some calculation done, you have the data already in your query dont you? so why store it (again) in a table? This goes against all forms of good design of your database.

Also may I suggest you have a read on Name conventions:
http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=name+convention

 

[Steph]

Thanks however this is still throwing up an error!

Sorry I may not have explained myself properly (and I might still be doing it wrong!) The table which the data is coming from is stored in another database. The idea is to create a temporary table in which the user can decide whether to update the main table (then the temp table will be deleted).

Basically the Red Amber and Green columns are going to be updated from another set of data, this is currently running on a query however the criteria for this query is getting really long. There are 48 time periods in columns and each RAG category sums different sections depending what area it's in. We kind of ran out of room! So I was hoping to do this with some script but as I am quite new to this I am obviously struggling already!

Thanks

 

[namliam]

Hrmph, I assumed your sql was valid.... wihtout checking it...
Create table is Oracle SQL I think, where as in access and SQL Server it is:
SELECT *
INTO TargetTable
FROM SourceTable;

48 time periods, every 30 minutes the whole day thru? May not be the best solution to split those into columns, perhaps instead you may want to split them into rows.
StartTime endTime value
Or something along those lines, far easier in general and creating columns per date/time period usually is a bad idea... There is the 1% where it actually works best and this could very well be one of those times, but generaly a bad idea.

 

[Uncle Gizmo]

namlian said:

>>> 48 time periods, every 30 minutes the whole day thru? May not be the best solution to split those into columns, perhaps instead you may want to split them into rows. <<<

This is why he said it: http://www.access-programmers.co.uk/forums/showthread.php?p=671226

And in most cases it's the best solution for these reasons:

Extract from "Uncharted Territory" and "Maintenance" in above link
find it difficult to extract useful information, and this is one of the major reasons that you should consider constructing your new database in a different manner to your spreadsheet, no doubt you will be able to come up with your own unique methods of extracting the data, however you will find there is little help available, not because no one wants to help you, but because you are embarking into uncharted territory, “where no man has gone before”.

Maintenance
Another reason, (not in any particular order of importance, the significance will change from project to project) what happens if you need to add an extra subject? Let’s say the school starts offering French lessons, in the spreadsheet all you would do is add a new column “French” and indeed in your MS Access database you could also add a new column “French”, however if the construction of your database is well advanced, in other words you have many queries and forms based on the table you are adding a new field to, then you will have to modify every single query and form that extracts information from this table, not something you want to do often!