I have a MS Access 2000 select query that querys a payroll table and has 36 columns of payroll data, My 37th column is a calculated column which adds all the 36 columns up and reads like TOTAL:[field1]+[field2]+......
The problem is the no of fields in the source table or the field names could change each month casuing my calculated column to show the incorrect total and not add up all teh fields. Currently I have to check each month manually and adjust the calculated column formula.
How do I change my total field to pick all fields (somekind of wildcard) from the source table regardless of field names instead of specifically naming the fields in the total column
The table is a linked table we receive from a 3rd party, although it does not literally change every month but 3rd party do add additional columns of data etc which I need to include in total column, we have no control over table structure but these additional columns do need reporting within the final total
Any help with the structure and statements of the code will be appreciated, ivon
I'm afraid I haven't used Access in a few years, but it would go something like this:
- open a recordset with a query like select * from ThatTable where 0=1. it will give you no records, but looping through the fields of the recordset you can get their names. (Access experts, please back me up here. I'm not sure if a record of data is needed to do this.)
- build your SQL statement in a string variable, like: strSQL = "select " & yourrecordset.fields(0).name & ", " & yourrecordset.fields(1).name & "from ThatTable where " & ... etc.
- create a new QueryDef and use the string variable as its SQL command.
The experts here can probably help you with the actual VBA code. Or they might have a better solution.
Basically, It is a table from outsourced Payroll Services provider, The table contains Payroll elements like NI, Tax, Emplorys NI, Childcare Pay etc. My Access query displays all the fields from this table as well a Total column adding all the fields up. Situation is if the Payroll company adds a new column of data for example "student loan payments", my query will not include it in the total as it will not know about it unless I manually add it to the Total column.
hence the need to add ALL the columns from the table using somekind of wildcard regardless of the number of columns or there names.
Is there any way you can find out from the 3rd party if they can provide you with some sort of data dictionary (if they still call it that) containing all of the possible field names that they would ever use in a potential billing file? They should hopefully have this data somewhere and I would not think that there would be too much of a problem providing it to you.
If they could get you this data, you could then build your query around it.
(Quite honestly though, I think Ivon's qrydef idea is better, but I've never done one of those... figured that this might be a roundabout way of tackling the problem)
Here is a routine, using DAO, that will print all your field names to the immediate window (use Ctrl G to get the immediate window). You can modify this code to get all the field names from the table you pass to this sub. I would suggest just using this code, not the sub itself, to get the field names as you put them into the SQL that will be your query.
Dim x As Integer
Dim td As DAO.TableDef
Dim db As DAO.Database
Set db = CurrentDb()
Set td = db.TableDefs(MyTableName)
For x = 0 To td.Fields.count - 1