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

    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
    Gator

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    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.
    Code:
    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:
    Code:
    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.
    Code:
        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
  •