# Thread: Calculate value from table name (A2K SR1)

1. ## 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. ## 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.

3. ## 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. ## 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
•