Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003

    Exclamation Unanswered: Variable column identities or something like it

    I am not sure how to title this, what I need is some method of being able to impliment a table that has a reference table to allow users to add values to the column names. Here is what I am doing, I am working on a database that tracks medical test data, then the reports are run from this data, there are 5 fields of data that are the same for every test table, and the results of the test may vary depending on what tests they wish to track. I also run reports and graphs from this data. most are the same with the exception of the table and column referances. every time they add a new test I have to recode new tables and new reports and forms to enter data.

    There has to be a way to use a generic table with the 5 common fields and say 50 generic fields then establish a reference table to allow the useres to use the same forms and reports and just call up different test data as well as add new ones themselves.

    Any Ideas

  2. #2
    Join Date
    Sep 2003
    Caldes de Malavella, Spain
    What I would do is to create a table of field names that can be used (plus a form to edit this list for when users wish to add new fields.) I would then present a list of those fields in a list box, with its MultiSelect property set to either Simple or Extended so that the user can select multiple entries in the list box. Then you can loop through the ItemsSelected collection and construct an SQL string to select the correct records from your table or query, concatenating it for each item. For example, if the user has selected Field1, Field5 and Field9 in the list box, write a loop like this:

    Dim SQLString as string

    SQLString = "SELECT "

    Dim varItem as Variant

    For Each varItem in YourListBoxName.ItemsSelected

    SQLString = SQLString & YourListBoxName.ItemData(varItem) &","

    Next varItem

    The value of SQLString after running this would be "SELECT Field1,Field5,Field9,". You can easily chop the final comma off by using the Left and Len functions:

    SQLString = Left(Len(SQLString)-1)

    Now you could add to the SQL string like this:

    SQLString = "INSERT INTO TempTableName " &SQL String & " FROM TableOrQuery "

    This would copy data from your source into a table called TempTableName, but only those fields that the user has selected. You can then use this table as a basis for your reports.

    I hope this helps and isn't too confusing!! Good luck.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy

Posting Permissions

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