UPDATE into already table built with CREATE and ALTER

[Stanski21]

Hi all,

I feel I'm on the brink of fixing this one myself but seem to be meeting myself coming back and going backwards in coming forwards... Trying to remind myself that 7 months ago I'd never even HEARD of SQL or VBA, let along used them...

I have a report based on a query which is essentially a filtered temporary table.
I'm trying to add further fields into the temporary table, BASED on the data in the table. I'm getting all sorts of errors as I'm amending the SQL and VBA to get it to what I need.

In a bit more detail, after the temporary table is populated with performance measures, I want to use the SQL method of ranking each individual performance measure, then add the results to the temporary table as a new field.

Here's what I've got so far (apologies for the messy code - I will tidy it up once I get it working!). First is the run query and create table bit, which calls the first performance measure function:

Private Sub btnKPIReport1_Click()

Dim strSQL As String
Dim db As Database
Dim rs As DAO.Recordset
Dim strTbl As String

Set db = CurrentDb
strTbl = "tblTempKPI1"
strSQL = "SELECT * INTO " & strTbl & " FROM qry_AllFilterKPI1;"

On Error GoTo ErrHandler
DoCmd.DeleteObject acTable, strTbl
DoCmd.OpenQuery "qry_AllFilterKPI1"
DoCmd.RunSQL strSQL

Call KPIRank("AHT")

DoCmd.Close acQuery, "qry_AllFilterKPI1", acSaveNo

ErrHandler:
Debug.Print Err.Number
If Err.Number = 7874 Then Resume Next
If Err.Number <> 7874 Then Exit Sub

End Sub

Then the function to rank and add the results to the newly created table:

Private Sub KPIRank(ByVal Field As String)

Dim strSQLRank As String
Dim strSQLField As String
Dim strSQLAddRank As String
Dim db As Database
Dim strTbl As String

Set db = CurrentDb
strTbl = "tblTempKPI1"

strSQLField = "ALTER TABLE " & strTbl & " ADD COLUMN " & Field & "Rank NUMBER;"
DoCmd.RunSQL strSQLField

strSQLRank = "UPDATE " & strTbl & " SET " & Field & "Rank = (SELECT [Rnk1].[EmployeeNo], [Rnk1].[" & Field & "], Count([Rnk2].[EmployeeNo])+1 AS " & Field & "Rank " _
        & "FROM qry_AllFilterKPI1 AS Rnk1 LEFT JOIN qry_AllFilterKPI1 AS Rnk2 " _
        & "ON [Rnk2].[" & Field & "]>[Rnk1].[" & Field & "] " _
        & "GROUP BY [Rnk1].[EmployeeNo], [Rnk1].[" & Field & "]);"
Debug.Print strSQLRank
DoCmd.RunSQL strSQLRank
        
End Sub

the SQL I need to correct reads as:

UPDATE tblTempKPI1 SET AHTRank = (SELECT [Rnk1].[EmployeeNo], [Rnk1].[AHT], Count([Rnk2].[EmployeeNo])+1 AS AHTRank FROM qry_AllFilterKPI1 AS Rnk1 LEFT JOIN qry_AllFilterKPI1 AS Rnk2 ON [Rnk2].[AHT]>[Rnk1].[AHT] GROUP BY [Rnk1].[EmployeeNo], [Rnk1].[AHT]);

Running that as a seperate query gives me 'You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause.'
Then my forehead hits the table...

I'm thinking I've got to specify that the EmployeeNo in the new SELECT query joins with the EmployeeNo in the Temp Table... but getting all sorts of syntax errors trying to do that... any help would be greatly appreciated!

 

[Stanski21]

I should add that I cannot add the Ranked numbers as standalone queries and do it the 'old fashioned way', as the connections then exceed the 1024 (or whatever the number is) and my report fails - and apologies for the Yodaism in the title

 

[plog]

This seems like a very rube goldbergian way of doing things. When the candlestick melts to a certain level it causes a balloon to pop, which scares a chicken, which pecks at a worm which is attached to an anvil, which...and then the report runs.

I seriously question the need for this temporary table. And the use of VBA at all. My gut tells me this could be accomplished with just SQL. Can you post your database? Or at least the data you are startign with and your finished data set?

 

[Stanski21]

Thanks! I CAN post an example of the database, but I'd first need to run through all the tables cleaning them of personal employee data etc...
Is this info enough for now?

I have 9 BE tables which hold all of the performance data and employee/team data.
2 of the tables are populated with daily performance data, another 5 are populated with MTD data (and previous month end data) and the final 2 are updated manually as team line-ups change.

I have 2 forms that the user fills in (dates and department info and report type).

Then I have a query to filter the employee/team detail (so only active employees are reported upon and from the correct team number) followed by 17 queries to date and department filter and total the daily reports, and filter, average and total the Month-To-Date reports.

I've attached a table showing the output I'm looking for, detailing how they are currently calculated in the description. Hope this all makes sense...?

 

[plog]

I'd really need to see the starting tables to get a good idea. Could you post a screenshot of the relationship view with the tables fully expanded so i can see all their fields?

 

[Stanski21]

Hope this is what you're asking for? I tried to attach the DB but even when zipped, it's still too large...

 

[Stanski21]

I've attached a very smoothed down version of the DB I'm working from. Hope this makes more sense now... Thanks for your Help so far!

 

[plog]

That's pretty complex. I don't know if I can really help specifically (but I'll blather on any way):

1. I do think this could be done via SQL alone. Your VBA adds a level of complexity that isn't really necessary. You're still writing all this SQL to get the data you need. You probably just need one master query to bring all those values together instead of using VBA to do it.

2. Even if you do use VBA, there's no reason for an ALTER query. I mean, you posted what you expected your results to be--you know what the final table's structure is going to be right? Instead of making a table, clear that one out, then APPEND data to it, instead of making it dynamically and altering it.

The good news is, your table structure is pretty decent (a few minor normalization issues). Sorry, I can't help more than that, but I think your report is just too complex by its nature.