Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2009

    Unanswered: Search Column Header retrieve data for found Header

    I need help with an Access query. I have a table that is set up like this
    Customer ID - Number
    Step 1 Time - Date/Time
    Step 2 Time - Date/Time
    Step 3 Time - Date/Time
    Step 30 Time - Date/Time

    These are all of my column headings. What I am trying to do is write a query so that when some one opens a form I have created and chooses the Start time stamp and end time stamp (ex. Start - Step 1 Time...End - Step 3 Time) the query will just bring in Customer ID, Step 1 Time and Step 3 time columns. Then I would put in the query some sort of function similar to Networkdays in Excel to calculate the days and hours between the two time stamps.
    How do I go about writing the query to search for the column header and bring in the associated data with it?

    I have tried a nested if statement and that worked for about the first 7 or 8 then I ran out of characters.

    Any help would be appreciated

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    If you need to create a persistent query in the database, you can create it on the fly. In this example a query MyQuery is created that returns three columns named CustomerID, StartTime and EndTime from the table MyTable. If MyQuery already exists it is deleted and replaced by a new one. The actual names of the columns StartTime and EndTime are specified by the arguments passed to the function, while the CustomerID actually exists in MyTable.
    Function CreateMyQuery(ColumnHeader1 As String, ColumnHeader2 As String)
        Dim dbs As DAO.Database
        Dim strsql As String
        Set dbs = CurrentDb
        If Not IsNull(DLookup("Name", "MSysObjects", "Name = 'MyQuery' AND Type = " & CStr(5))) Then
            dbs.QueryDefs.Delete "MyQuery"
        End If
        strsql = "SELECT CustomerID, [" & ColumnHeader1 & "] AS StartTime, [" & ColumnHeader2 & "] AS StopTime FROM MyTable;"
        dbs.CreateQueryDef "MyQuery", strsql
        Set dbs = Nothing
    End Function
    You can use this function to create the query, for instance like this:
    CreateMyQuery "Step 1 Time", "Step 22 Time"
    If you don't need a persistent query in the database, you can simply build the SQL statement and use it to open a recordset.
        Dim rst As DAO.Database
        Dim strsql As String
        strsql = "SELECT CustomerID, " & ColumnHeader1 & " AS StartTime, " & ColumnHeader2 & " AS StopTime FROM MyTable;"
        Set rst = CurrentDb.OpenRecordset(strsql)
    A more fundamental problem is that your database is not normalized. A canonical approach would yield a table with a different organisation that would possibly be similar to this:

    Customer ID - Number
    Step Time - Date/Time
    Step Number - Number

    That way you could retrieve the data you need by specifying the Step Number you want in a WHERE clause; but this would need a total reorganization of your application.
    Have a nice day!

Posting Permissions

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