Using a tablename in a query

[Richhol65]

Hi

I am trying to create SQL within a sub that uses the sum of three elements in a table and the current date but I then want the name of the table that these entries relate to to be inserted into the table also so the Totals for a table called C5_1 would show in the table as:

BWS Date BASIC BONUS COMMISSION
C5_1 18/12/2014 2000 500 700

But there could be multiple tables and I am using a recordset to give me the lsit of tables. I just cant seem to get the SQL of the SELECT for the tablename to work.

Below is what I am currently using:

Dim db As Database
Dim rs As Recordset
Dim strSQLText As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblofC5_1Tables", dbOpenDynaset)

DoCmd.SetWarnings False
    
    Do Until rs.EOF
    strSQLTbl = "(SELECT MSysObjects.Name FROM MSysObjects WHERE MSysObjects.Name = " & rs!TableName & ")" 
    strInsertText = "INSERT INTO tblC5_1TotalCurrent ([BWS], [DateImported], [BASIC CASH], [BONUS CASH], _
    [COMMISSION]) "
    strSelectText = "SELECT Now()as DateImported, sum([" & rs!TableName & "].[BASIC CASH]) as [BASIC CASH], _
    sum([" & rs!TableName & "].[BONUS CASH]) as [BONUS CASH], sum([" & rs!TableName & "].[COMMISSION]) as _
    [COMMISSION]," & strSQLTbl & " as BWS  "
    strFromText = "FROM " & rs!TableName & ";"
    
    strSQLText = strInsertText & strSelectText & strFromText
    
    Debug.Print strSQLText
    DoCmd.RunSQL strSQLText
    
    rs.MoveNext
    Loop

There is currently only one TableName in the Table and that is C5_1

What am I getting wrong in the code

Thanks

Rich

 

[pbaldy]

Do you get an error? If so, what is it? For starters, the fields in the SELECT statement must match the fields in the INSERT INTO statement (be in the same order). My guess would be that you need to surround the table name with single quotes. Check what's coming out in the Immediate window too.

By the way, I don't see the purpose of strSQLTbl. You already have the table name from your recordset.

 

[Richhol65]

Hi Paul

The only thing I keep getting is a box popping up asking me to input into it and on it it has C5.

When I tried to use rs!TableName I got the same thing.

Currently if I don't type anything in I get:

DateImported BASIC BONUS COMMISSION BWS

18/12/2014. 2000 500 1500

So what would be a way to do it that populated the BWS column

Thanks

Rich

 

[pbaldy]

Like I said, you would need single quotes around the table name in the SELECT:

...'" & rs!TableName & "' as BWS

 

[Richhol65]

Hi Paul

Thanks for that - I am just going to try it now

Cheers

Rich

 

[pbaldy]

No problem, post back if you get stuck.

 

[Richhol65]

Hi Paul

That worked perfectly - but I have messed up slightly as the tables have a row at the bottom (they were imported from excel) that is a total so when I am summing the columns they are doubling up.

The total row is blank in column one which in the original table is called Emp ID so I added the line

strWhereText = "WHERE " & rs!Tablename & ".[Emp ID]) is not null));"

to the routine and changed the strSQLText to

strSQLText = strInsertText & strSelectText & strFromText & strWhereText

But this is now doing nothing at all

I am not sure what I am doing wrong this time

Sorry to be a pain

Rich

 

[pbaldy]

One of the primary tools is checking the result of the Debug.Print. If you don't spot the problem, post the SQL here.

 

[Richhol65]

Hi Paul

I have just done that and when I tried to create a query with it and it seems to be generating the SQL twice

Here is the SQL Generated:

INSERT INTO tblC5_1TotalCurrent ([BWS], [DateImported], [BASIC CASH], [BONUS CASH], [COMMISSION]) _
SELECT 'C5_1' as BWS, Now()as DateImported, sum([C5_1].[BASIC CASH]) as [BASIC CASH], _
sum([C5_1].[BONUS CASH]) as [BONUS CASH], sum([C5_1].[COMMISSION]) as [COMMISSION] _
FROM C5_1;INSERT INTO tblC5_1TotalCurrent ([BWS], [DateImported], [BASIC CASH], [BONUS CASH], _
[COMMISSION]) SELECT 'C5_1' as BWS, Now()as DateImported, sum([C5_1].[BASIC CASH]) as [BASIC CASH], _
sum([C5_1].[BONUS CASH]) as [BONUS CASH], sum([C5_1].[COMMISSION]) as [COMMISSION] FROM _
C5_1 WHERE [C5_1].[Emp ID]) is not null));

Thanks

Rich

 

[pbaldy]

The stray underscores within the SQL are also a problem. You need to have your line continuation characters outside your strings. What is your code now? I don't see why it would double up.

 

[Richhol65]

Paul

I just put the underscores in the code to make it easier for you to read as it was just one long line of code

I am probably confusing things more

Rich

 

[pbaldy]

Yes, but I'm easily confused. You can post it as is and I'll figure it out. Still would like to see the code to see why it's doubling up.

 

[Richhol65]

Hi Paul

Here is the code

INSERT INTO tblC5_1TotalCurrent ([BWS], [DateImported], [BASIC CASH], [BONUS CASH], [COMMISSION]) SELECT 'C5_1' as BWS, Now()as DateImported, sum([C5_1].[BASIC CASH]) as [BASIC CASH], sum([C5_1].[BONUS CASH]) as [BONUS CASH], sum([C5_1].[COMMISSION]) as [COMMISSION] FROM [C5_1] WHERE C5_1.[Emp ID]) is not null));

I cleared the immediate window and it seemed to just run once

Still totalling double though

Any help would be appreciated

Cheers

Rich

 

[pbaldy]

Not sure what you mean by "totalling double". Have you created an append query that correctly does what you want? I would do that, and then make sure the finished SQL here follows the same syntax.

 

[Richhol65]

Hi Paul

What I mean is that the table has a total at the bottom as it is imported from excel. But that row doesn't have an Emp ID. That is why I added the where clause I have tried what you suggest and the code looks correct. But the total row still seems to be getting added in

Totally confused now

Any ideas

Rich

 

[pbaldy]

Ah. Might not be Null. Try

WHERE Nz(C5_1.[Emp ID], '') <> ''

that's 2 single quotes together, which should catch both Null and a zero length string.

 

[Richhol65]

Hi Paul

Thank you so much it now works - you are a life saver.

There was a stray comma in there but I removed it and it is calculating perfectly

Thank you

Rich

 

[pbaldy]

Happy to help Rich!