Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Calculate value from table name (A2K SR1)

    I am looking for a way to calculate a value in a query to extract part of the source table's name. We have information that is kept in tables, one week's information per table. Unfortunately, there is no date information kept in the table itself, but each table incorporates the week end date into the table name -- SOF011231 is the name for the week ending December 31, 2001 -- the last six characters of the table name are the date. I have to combine information from multiple tables, but keep a breakdown of detail by dates.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Calculate value from table name (A2K SR1)

    I think this illustrates the pitfalls of a non-normalized database. You have now have to jump thru all sorts of hoops to get the data in a useful form. I think the only way to bring together data from several tables is to use a Union query, in which you add a field for the week ending date and "hardcode" it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Lounger
    Join Date
    Oct 2001
    Location
    Upper NY State, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate value from table name (A2K SR1)

    An idea I put together real fast. Go though the table collection eliminate system tables. Trim the right 6 characters off the table name. See if it's numeric. It may not be pretty, but it works. The dates can then be passed maybe into an append query .

    Dave

    Public Sub TableList()

    Dim db As Database
    Dim tdf As TableDef
    Set db = CurrentDb()
    Dim tblName As Double

    On Error GoTo Err_TableList



    For Each tdf In db.TableDefs
    If (tdf.Attributes And dbSystemObject) Then
    ' This is a system object. Leave it alone
    Else
    ' The following will print only table names with
    'the last six characters being numeric
    tblName = CLng(Right$(tdf.Name, 6))
    ' Your sql here: Insert Into...
    End If
    Next

    Exit_TableList:
    Set tdf = Nothing
    Set db = Nothing
    Exit Sub

    Err_TableList:
    If Err.Number = 13 Then
    Resume Next
    Else
    Debug.Print Err.Number & " Error Description: " & Err.Description
    Resume Exit_TableList
    End If

    End Sub

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Calculate value from table name (A2K SR1)

    I was somewhat afraid of that, but it's someone else's data that I have to work with. Actually it doesn't present that much more of a problem as. Once the query is written, I should be able to take the SQL code complete with the hardcoded date field, copy it into Word, and the same "Find and Replace" that I would use to switch from one source table to another would also update the forced date field. This operation is something I had planned for from the beginning, so it proves to be no additional burden. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    I would definately prefer if all of the data were being dumped into one large table complete with a date field, instead of being fragmented into different date-named files, but the existing system is so thoroughly embedded that it would be a major task to revamp. And I can adapt & overcome much easier.

Posting Permissions

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