Dyanimcally select a Table Field to Update 1

[accessjack]

I have two tables:
tblBalance has 2 fields (Account & Balance)
tblCrosstab has 13 fields (Account, Jan, Feb, ..., Dec)

tbl balance always has the current months balance. At the end of each month, I have an update query which says to update [Mth] in the tblCrosstab using the latest balance from the tbl Balance. This works perfect, however, each month I have to manually go into the update query and change the month field to the appropriate month, ie. Jan to Feb to make the query work. I'd like to have the field be a parameter, so at run-time, it prompts, "Please enter the month you want to update". Remember, the parameter is not a criteria, rather, it needs to be the actual field name.

Since I'm handing this database off to another colleague, I'm worried they'll accidentilly forget to update the query, and in effect, overwrite last months balances in the crosstab table.

Any help is appreciated.
Accessjack

 

[dhookom]

I'm not sure why you need tblCrosstab structured like a spreadsheet...
However, you could have a drop down (cboField) on your form that has the field names (there is a combo box type that displays fields). Then add a command button that builds and runs the SQL like:

Dim strSQL as String
strSQL = "UPDATE tblCrosstab SET [" & Me.cboField & "] = " & _
    Me.txtBalance & " WHERE Account =""" & Me.txtAccount & """"
DoCmd.RunSQL strSQL

This code assumes Account is text and balance is numeric.

You will want to add code to verify the user has selected the appropriate field.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[accessjack]

Thanks, Duane, I will give this a try today and let you know how it worked. With respect to setting up the table like a crosstab, versus setting a table up with 3 fields (account, month, amount)...I thought it would save space. I have approximately 100,000 accounts, as time went on, my table would continue to grow and grow (100K accounts x 12 months is 1.2 Million records each year). Using a table set up like a crosstab, I am pretty much assured that the number of records in my table will always be close 100,000 with only 12 month columns. I also felt it would be easier to query off of this table. As storage was a big concern, please let me know if I am flawed in my thinking. I'm sure you would know better than I. Thanks.

 

[dhookom]

I'm not sure which would be the better solution. I expect this would depend on the number of users, indexing of the appropriate fields, and how much historic records might change.

If your solution is working for you now, I probably wouldn't change it. I guess I would have begun with the normalized solution with more records rather than more fields. When/if the performance became un-acceptable, I would move the data to the "crosstab table".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[accessjack]

Your solution worked. I tweaked things a bit. Here is my final button code on a form with 2 combo boxes: cboMonth (Jan thru Dec) & cboYear (2007 thru 2008) as drop downs. I added the year as a field to the matrix table so this will work for multiple years as well. Works perfect. Thanks.

Dim sql As String

sql = "UPDATE [tblMatrix] A " & _
"INNER JOIN [tblBalance] B " & _
"ON A.AccountId = B.AccountId " & _
"SET [A].[" & Me.cboMonth & "] = B.Balance " & _
"WHERE A.Year = '" & cboYear & "'"

DoCmd.RunSQL sql

 

[dhookom]

Thanks for the reply back.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]