Okay, I'm stumped again......

[edsac64]

Hi, I'm trying to get the following SQL code to run in VBA, but I'm receiving an error message stating "Query Input must contain at least one table or query"

mySQL = "INSERT INTO tblUnbilled_PreCutoff_Counts ( LOB, [Import PreCutoff Count] ) " & _
"SELECT impUnbilled_PreCutoff_Temp.LOB, Count(impUnbilled_PreCutoff_Temp.[CBS Id])" & _
"AS [CountOfCBS Id] " & "'" & crntCBSUnbilledCycle & "'" & _
"FROM impUnbilled_PreCutoff_Temp " & _
"GROUP BY impUnbilled_PreCutoff_Temp.LOB" & _
" Where ((impUnbilled_PreCutoff_Temp.LOB) = [COLOR=seagreen]" '" & strBusUnit & "' "[/COLOR]);"

Neither variable "'" & crntCBSUnbilledCycle & "'" nor " '" & strBusUnit & "' " seems to be getting passed through and the latter variable is actually showing up as green in the VBA window.

Here is the Debug.Print, should it help:

INSERT INTO tblUnbilled_PreCutoff_Counts ( LOB, [Import PreCutoff Count] ) SELECT impUnbilled_PreCutoff_Temp.LOB, Count(impUnbilled_PreCutoff_Temp.[CBS Id])AS [CountOfCBS Id] ''FROM impUnbilled_PreCutoff_Temp GROUP BY impUnbilled_PreCutoff_Temp.LOB Where ((impUnbilled_PreCutoff_Temp.LOB) =


As an aside, the variable " & "'" & crntCBSUnbilledCycle & "'" is being used successfully in other SQL statements.

 

[Beetle]

What are trying to do with this portion of the SQL;

"AS [CountOfCBS Id] " & "'" & crntCBSUnbilledCycle & "'" & _

It's constructed as though you are trying to take a string variable and add it to the end of your field alias name outside the brackets, which really wouldn't make any sense. If your intention was to insert the value of the variable into a field in the destination table, then the syntax is wrong. Furthermore, there are only two fields selected from the destination table and this variable would constitute a third value from the selection record set.

Maybe you can shed some more light on what this variable is and what you're trying to accomplish here?

 

[MSAccessRookie]

Hi, I'm trying to get the following SQL code to run in VBA, but I'm receiving an error message stating "Query Input must contain at least one table or query"

mySQL = "INSERT INTO tblUnbilled_PreCutoff_Counts ( LOB, [Import PreCutoff Count] ) " & _
"SELECT impUnbilled_PreCutoff_Temp.LOB, Count(impUnbilled_PreCutoff_Temp[COLOR=red][B].[CBS Id])" & _[/B][/COLOR]
[COLOR=red][B]"AS[/B][/COLOR] [CountOfCBS Id] " & "'" & crntCBSUnbilledCycle & "'" & _
"FROM impUnbilled_PreCutoff_Temp " & _
"GROUP BY impUnbilled_PreCutoff_Temp.LOB" & _
" Where ((impUnbilled_PreCutoff_Temp.LOB) = [COLOR=seagreen][COLOR=red][B]" '" & strBusUnit & "'[/B][/COLOR] "[/COLOR]);"

Neither variable "'" & crntCBSUnbilledCycle & "'" nor " '" & strBusUnit & "' " seems to be getting passed through and the latter variable is actually showing up as green in the VBA window.

Here is the Debug.Print, should it help:

INSERT INTO tblUnbilled_PreCutoff_Counts ( LOB, [Import PreCutoff Count] ) SELECT impUnbilled_PreCutoff_Temp.LOB, Count(impUnbilled_PreCutoff_Temp.[CBS Id])AS [CountOfCBS Id] ''FROM impUnbilled_PreCutoff_Temp GROUP BY impUnbilled_PreCutoff_Temp.LOB Where ((impUnbilled_PreCutoff_Temp.LOB) =


As an aside, the variable " & "'" & crntCBSUnbilledCycle & "'" is being used successfully in other SQL statements.

Two things to remember when creating SQL Code in VBA are the following:

1. Make sure to include all of the SPACES that are required to make the statement correct. I believe that one is missing before the word "AS".
2. VBA Strings begin and end with double Quote, so whenever an SQL Statement needs a Quoted String, you must use a single Quote alone.

Try making these fixes and get back if there are any additional questions

 

[edsac64]

Hi, Thanks Rookie. I seem to be running up against another issue.

I'm now receiving an error stating that the number of query values and destination fields are not the same.

I've tried to simplify the query and made some changes based on additional needs.

Is this issue being cause by my needing to update the CBSUnbilledCycle field with the variable string "'" & crntCBSUnbilledCycle & "'" ? Have I got these blasted quotes correct?

mySQL = "INSERT INTO tblUnbilled_PreCutoff_Counts ( Division, [Import PreCutoff Count], CBSUnbilledCycle )" & _
"SELECT tblDivisionLOBs.DIVISION, Count(impUnbilled_PreCutoff_Temp.[CBS Id]) AS [Import PreCutoff Count])" & "'" & crntCBSUnbilledCycle & "'" & _
"FROM impUnbilled_PreCutoff_Temp INNER JOIN tblDivisionLOBs ON impUnbilled_PreCutoff_Temp.LOB = tblDivisionLOBs.LOB" & _
"GROUP BY tblDivisionLOBs.DIVISION;"

Here is what the debug.print is showing in the immediate window

INSERT INTO tblUnbilled_PreCutoff_Counts ( Division, [Import PreCutoff Count], CBSUnbilledCycle )SELECT tblDivisionLOBs.DIVISION, Count(impUnbilled_PreCutoff_Temp.[CBS Id]) AS [Import PreCutoff Count])'2013/01'FROM impUnbilled_PreCutoff_Temp INNER JOIN tblDivisionLOBs ON impUnbilled_PreCutoff_Temp.LOB = tblDivisionLOBs.LOBGROUP BY tblDivisionLOBs.DIVISION;

Again, my sincerest appreciation to anyone who can lend a hand

Regards,

Ed

 

[edsac64]

@Beetle - thanks for your response. Sorry about being so nebulous in my previous post. I've cleaned it up a bit and hopefully you can understand what I'm trying to accomplish.

 

[Beetle]

As MSAccessRookie mentioned, you're still missing spaces in your SQL string. Also, the value '2013/01' that you're trying to insert into the third field cannot be understood as written because it is not preceded by a comma (therefore not understood as another field from the selection record set) and, again, missing spaces so it all just runs together between the Count(impUnbilled... and the FROM operator. You can see this if you look at the debug.print output. There are other missing spaces at the line breaks as well, although those may not be as obvious to you at first glance. Your SQL string should look more like this;

mySQL = "INSERT INTO tblUnbilled_PreCutoff_Counts ( Division, [Import PreCutoff Count], CBSUnbilledCycle ) " & _
"SELECT tblDivisionLOBs.DIVISION, Count(impUnbilled_PreCutoff_Temp.[CBS Id]) AS [Import PreCutoff Count])" & ", '" & crntCBSUnbilledCycle & "' " & _
"FROM impUnbilled_PreCutoff_Temp INNER JOIN tblDivisionLOBs ON impUnbilled_PreCutoff_Temp.LOB = tblDivisionLOBs.LOB " & _
"GROUP BY tblDivisionLOBs.DIVISION;"

Note the spaces that have been added just before the qoutes at the end of each line and the comma that has been added ahead of the variable.

 

[edsac64]

Hello again, I think we're getting closer, but I'm still getting the number of query values and destination fields are not the same error message. Could it relate to the fact that I'm trying to pass a string variable that's not part of the Selected table to the table in the Insert statement? Do I need to do something like indicate the variable AS the field in the insert table, ie.", '" & crntCBSUnbilledCycle & "' " AS CBSUnbilledCycle?

I'm using Beetle's version of the SQL from earlier:

mySQL = "INSERT INTO tblUnbilled_PreCutoff_Counts ( Division, [Import PreCutoff Count], CBSUnbilledCycle ) " & _
"SELECT tblDivisionLOBs.DIVISION, Count(impUnbilled_PreCutoff_Temp.[CBS Id]) AS [Import PreCutoff Count])" & ", '" & crntCBSUnbilledCycle & "' " & _
"FROM impUnbilled_PreCutoff_Temp INNER JOIN tblDivisionLOBs ON impUnbilled_PreCutoff_Temp.LOB = tblDivisionLOBs.LOB " & _
"GROUP BY tblDivisionLOBs.DIVISION;"

And here is the Debug.Print

INSERT INTO tblUnbilled_PreCutoff_Counts ( Division, [Import PreCutoff Count], CBSUnbilledCycle ) SELECT tblDivisionLOBs.DIVISION, Count(impUnbilled_PreCutoff_Temp.[CBS Id]) AS [Import PreCutoff Count]), '2013/01' FROM impUnbilled_PreCutoff_Temp INNER JOIN tblDivisionLOBs ON impUnbilled_PreCutoff_Temp.LOB = tblDivisionLOBs.LOB GROUP BY tblDivisionLOBs.DIVISION;

Any ideas or suggestions?

 

[Beetle]

Looking again at your SQL string, I realize now that I had previously only been looking at your spacing and syntax problems, and I overlooked another fundamental issue. Part of what you're doing here is attempting to select a field named '2013/01' from a table named tblDivisionLOBsfs, but there isn't actually any such field in that table is there?

Also, you are inserting values (including a calculated Count value of rows from another table) into a table named tblUnbilled_PreCutoff_Counts. What is the purpose of this table? Are you just storing calculated Count values of data from other tables? If so, why? This is something that should be done with a query, not stored in a table.

 

[edsac64]

Hi Sean, thanks for the quick reply. Here's a synopsis of what I'm trying to accomplish. My database runs several processes performed throughout the course of the month, one of which is this "PreCutoff" process. This process spans over a three day period, similar to an accounting closing period, and covers several divisions. Each day involves pulling raw data from text files generated by an enterprise database system; cleaning and scrubbing the data; and ultimately generating Excel files for email distribution.

My goal is to track the item counts of key files generated in each process, for audit purposes. We have audit requirements up the wazoo!! My thought of putting them in a table was to maintain a history log and to generate a report at the end of each period's processing.

The fundemental elements are the Division (an aggregate of something akin to regions); the count of the CBS IDs (similar to an account number) CBSUnbilledCycle (like a billing date), which is the calculated variable crntCBSUnbilledCycle that needs to be inserted.

Maybe I'm approaching this all wrong and should be using a form with queries for the source data of each field on the form?? I would, however, like to be able to save the metrics for audit purposes. Maybe there's something else in Access I can use.

In any event, I really appreciate all the time and attention you've given me.

Regards,

Ed

 

[MSAccessRookie]

Looking again at your SQL string, I realize now that I had previously only been looking at your spacing and syntax problems, and I overlooked another fundamental issue. Part of what you're doing here is attempting to select a field named '2013/01' from a table named tblDivisionLOBsfs, but there isn't actually any such field in that table is there?

Also, you are inserting values (including a calculated Count value of rows from another table) into a table named tblUnbilled_PreCutoff_Counts. What is the purpose of this table? Are you just storing calculated Count values of data from other tables? If so, why? This is something that should be done with a query, not stored in a table.

Actually, I believe that the statement in RED can be interpreted the same as if it said '2013/01' AS Expr1. If that is true, then Every Record that is returned will have 2013/01 for the value of that Field as opposed to any stored value from a Table. Since I do not think that is the intent, I believe that the code will need to be changed.

-- Rookie

 

[Beetle]

OK, so Rookie is correct in that the expression engine will add the alias (Expr1) for you in the case of the '2013/01' value so that value should get inserted into each row of the destination table if that's what you want.

However, I now notice that you have added an Inner Join to your query that was not there in the beginning. What is the reason for this? I would be cautious doing this because if the join is not handled properly it could create duplicate rows in the resulting record set and then your Count will be completely wrong.

I think it would be a good idea at this point if you can give us some idea of the structure of the tables that are involved here and the type of data in each. It sounds like you may have a good reason to write calculated values (in this case the Count of rows from another table) to a table, but it's still not really clear exactly which tables this data needs to come from and how those tables (if there are more than one) are related.