Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2007
    Posts
    5

    Unanswered: Access Query - Calculated Column

    My 1st forum thread ever

    URGENT HELP REQUIRED,

    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

    Thanks in Advance

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why on earth are your tables changing month on month?
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2007
    Posts
    5
    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

  4. #4
    Join Date
    Oct 2007
    Posts
    5
    BTW thanks for reponse georgev

  5. #5
    Join Date
    Nov 2002
    Posts
    272
    You could write some VBA code that analyzes the fields in the linked tables and builds a QueryDef accordingly.

  6. #6
    Join Date
    Oct 2007
    Posts
    5
    Any help with the structure and statements of the code will be appreciated, ivon
    Last edited by khan420; 10-16-07 at 08:05.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why on earth does the 3rd party change their table definitions so often? Are you sure it's a table and not a file (e.g. csv)?

    What sort of changes are we looking at - are the fields really named field1, field2, ... fieldN?
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by khan420
    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.

  9. #9
    Join Date
    Oct 2007
    Posts
    5
    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.

    I hope I have clarified it further.

    Thanx for help

  10. #10
    Join Date
    Oct 2007
    Location
    Washington D.C.
    Posts
    24
    Khan,

    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)

  11. #11
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    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.
    Code:
    Sub PrintAllFields(MyTableName)
     
    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
        Debug.Print td.Fields(x).name
      Next x
     
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •