sub query in an update query

[accessdave]

Hi everyone!

I've been stuck on this query for hours and I probably only need a couple lines of code to solve the problem. I'm trying update the values in one of the columns of a permanent table (tblGrossReciepts) from a temporary table (tblImportTable). There will be more temporary tables in the future that will be used to update the permanent table. I will also write a couple of other queries that are similar to this once I get it working. The query written below works fine if I hard code the column name, but I need the column name to be automatically inserted so that the user won't have to do it manually.



UPDATE tblGrossReciepts INNER JOIN tblImportTable ON tblGrossReciepts.[Licence Id] = tblImportTable.[Licence ID]
SET tblGrossReciepts.[columnName] = [tblImportTable].[Gross Receipts]
WHERE (((tblImportTable.[Licence Id]) Is Not Null) AND ((tblGrossReciepts.[Licence Id]) Is Not Null));


I've already set up a separate query that will return only the name of the needed column, but I don't know how to replace the hard coded [columnName] with the result of the query. Is there a way to run a sub query in the SET statement to do this? Or, if not, is there a way to use VBA to insert the column name?

I'd really appreciate your help. Thanks!

[June7]

I don't understand. Why don't you 'hard code' the field name to be updated in the query? Why would the field to be updated be variable?

[accessdave]

Actually, the columns I'm querying represent the end of a month. Each monthly report has a list of over 3,000 rows representing accounts and has to be imported from an excel file. I'm breaking up the reports into separate tables such as gross receipts, deductions, total amount, etc. so that I can access data for a given month, quarter, or year for each of those categories and combine them in a single report. What I had in mind was to create a form that would allow the user to enter a date or a range of dates for each table and then have the data queried and put into a report. I put in the dates for the end of the month for the last year and for the next five years as column heading since you can't use an append query to add columns. Then I was going to use a temporary table to import the raw data for the month, use a query to import the data into permanent tables, and then use a delete query to clear the data in the temporary table. That's why I created a select query that will return the date that is being represented by the raw data in the temporary table so that I can match the date with the columns in the permanent table. I figured that if I could use a sub query to put in the right date (for the column name) that could also use a sub query to select the right columns when a user selects a range of dates in a form.
So, maybe I'm taking the wrong approach when I'm dealing with dates, but I can't use a current date function since it will always be a previous date, and I need a way to access historical data and put them into reports with the dates that other users need. Does that make sense?

[June7]

Can't make field names dynamic in an Access query object, I've tried, not possible. Could use VBA code to alter table or query properties, including field names, by using TableDefs/QueryDefs collections.

Can use ALTER TABLE sql action to add fields to table. However, having to routinely add fields is indication of non-normalized data structure. And your structure of multiple similar fields for the month end data for the next 5 years is not normalized. Normalize the data then the date value in the temp table can be used as filter criteria. Maybe temp table won't even be necessary if the import can be done direct to permanent table.

[accessdave]

Thanks! I've been looking into using a QueryDef to alter the query. I'm having trouble finding the right syntax to change the field name though. Could you please send me an example of the VBA code syntax that you would use to change the field in query?

[June7]

In my project, solution is to delete query and then recreate it with new SQL statement.
This procedure modifies and opens the query.

Private Sub btnRun_Click()
If IsNull(Me.tbxFilter) Then
Me.tbxFilter = "SELECT * FROM ProjectRatesMainSub WHERE grading Like '*' " & GetMisc() & " ORDER BY projects.proj_num, grading;"
End If
Dim qdfUser As DAO.QueryDef
CurrentDb.QueryDefs.Delete ("UserQuery")
Set qdfUser = CurrentDb.CreateQueryDef("UserQuery", Me.tbxFilter)
DoCmd.OpenQuery "UserQuery"
End Sub

[accessdave]

Thanks! I ended up just using VBA to input the correct date into the query object. I got the correct date from a separate query (qryCurrentMonth) and then assigned it to a variable (currentMonth) in VBA. Then I used the variable to specify the correct column/field name. Here's the code that I used.

Function modifyQryUpdateGrossReciepts()


Dim db As DAO.Database
Dim qdef As DAO.QueryDef
Dim currentMonth As Date
Set db = CurrentDb
currentMonth = DFirst("[Report Period]", "[qryCurrentMonth]")
Set qdef = db.QueryDefs("qryUpdateGrossReceipts")
qdef.SQL = "UPDATE tblImportTable INNER JOIN tblGrossReceipts ON tblImportTable.[Licence Id] = tblGrossReceipts.[Licence Id] SET tblGrossReceipts.[" & currentMonth & "]= tblImportTable.[Gross Receipts]WHERE (((tblImportTable.[Licence Id]) Is Not Null) AND ((tblGrossReceipts.[Licence Id]) Is Not Null));"
qdef.Close
db.Close


End Function

[June7]

Great! Just set the SQL property of the QueryDef - Doh! I should use that instead of the delete.