Results 1 to 7 of 7
  1. #1
    dssrun is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    83

    Changing tables based on field date

    I have a field named "Current Date" and I have an expression in a query that says

    Code:
    Switch(Year([tbl1].[Current Date])=2008, [tbl1].[Quantity]*[tbl2008].[Price], Year([tbl1].[Current Date])=2009, [tbl1].[Quantity]*[tbl2009].[Price],)
    etc etc for every year (this table gets loaded into my database on a yearly basis). Is there a way to create a function that will dynamically change the table for each record based on the year of the current date. For example create a variable for each record in a SQL statement that is substituted for the table name. Instead of specific tbl2008, tbl2009 etc just have a function that chooses the tables based on the year of the Current date in tbl1

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Can do this in VBA, construct an SQL statement with variables. I don't think it can be done in an Access query. I have tried.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    dssrun is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    83
    I have gotten this far, but I am unsure how to putall of these different SQL statements into one query

    Code:
    Function Price()
        
        Dim strSql As String, strDynaTbl As String
        Dim db As DAO.Database, rst As DAO.Recordset, qry As DAO.QueryDef, fld As Date
        
        Set db = CurrentDb
        
        strSql = "SELECT tbl1.[Current Date], tbl1.Location, tbl1.Quantity FROM tbl1"
        
        Set rst = db.OpenRecordset(strSql)
        
        Do Until rst.EOF
        
            fld = rst("[Current Date]").Value
            
            strDynaTbl = "[tbl" & Year(fld) & "]"
        
            strSql = "SELECT tbl1.[Current Date], tbl1.Location,tbl1.Quantity, " & _
                    "[tbl1].[Quantity]*" & strDynaTbl & ".[Price] AS [Total Price], " & 
    FROM tbl1 INNER JOIN" & strDynaTbl & " ON tbl1.Location= " _
                    & strDynaTbl & ".Location"
            
            rst.MoveNext
        
        Loop
        
        rst.Close
        qry.Close
        Set qry = Nothing
        Set rst = Nothing
    
    End Function

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why do you have "[tbl1].[Quantity]*" in the SQL? There is a comma following [Total Price] that shouldn't.

    Don't really need the variable fld and syntax is not what I use. Try:
    strDynaTbl = "tbl" & Year(rst![Current Date])

    Need a space after INNER JOIN inside the "

    What do you want to do with the second SQL? You are not executing it.

    Should have asked first - why separate tables for years? Why not one table with another field for a year value? Having multiple tables with same structure is not efficient design, as you are discovering.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    dssrun is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    83
    That was the original plan, to make one table with years and I have a feeling I should do that. It involves a little manipulation every time i get a table (add the year) but I can deal with that. I thought there might be easy code out there already that can do this automatically every time I get a new table because initially i had no problem with multiple tables.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by dssrun View Post
    That was the original plan, to make one table with years and I have a feeling I should do that. It involves a little manipulation every time i get a table (add the year) but I can deal with that. I thought there might be easy code out there already that can do this automatically every time I get a new table because initially i had no problem with multiple tables.
    I was going to ask the same question about why multiple tables...
    There wouldn't be much manipulation, import the new data (table), then run an update query:

    Code:
    UPDATE SomeTable SET [WhichYear] = 2009 WHERE [WhichYear] IS NULL
    (air code)

  7. #7
    dssrun is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    83
    Quote Originally Posted by ssanfu View Post
    I was going to ask the same question about why multiple tables...
    There wouldn't be much manipulation, import the new data (table), then run an update query:

    Code:
    UPDATE SomeTable SET [WhichYear] = 2009 WHERE [WhichYear] IS NULL
    (air code)
    Yup, that is what I have now. Update, append to the master table and delete the new table that was imported. I just originally thought that was too many steps and there could be a more efficient way through VBA. Thanks for the help

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-07-2011, 08:25 AM
  2. Replies: 6
    Last Post: 06-08-2011, 05:00 PM
  3. Replies: 1
    Last Post: 03-01-2011, 04:03 PM
  4. Replies: 1
    Last Post: 02-06-2011, 06:36 PM
  5. Field content based on date calculation
    By jlmnjem in forum Programming
    Replies: 6
    Last Post: 09-23-2010, 10:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums