Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2009
    Posts
    79

    Unanswered: How to pass a table name as a parameter in a access query

    Hi All,

    I have some five queries which i want to run for some 15 tables which same kind of dataset in a access database. I want to create a query which will ask for the table name for which the user wants to execute the query and run all 5 queries on that particular query.

    Thanks for your help in advance.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I'd suggest some kind of a combo box on a form to offer your user a choice of tables, rather than asking them to remember off the top of their head each time what it is. After they have chosen, use that value in the combo box to build your query in the code and then either change the SQL of your query or change the recordsource of a report that will display the queries' data (depending on what you want to do with the data after you've fetched it).

    HTH, cheers!
    Me.Geek = True

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by abhay_547 View Post
    ...15 tables which same kind of dataset in a access database...
    Is there a reason you're not leveraging the relational capabilities of the relational database you're using?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Sep 2009
    Posts
    79

    How to pass a table name as a parameter in a access query

    Code:
    PARAMETERS [Enter the Period] Text ( 255 );
    SELECT Form1.[Table Name].Period, Sum(Form1.[Table Name].[Value/obj curr]) AS [SumOfValue/obj curr]
    FROM [Form1].[Table Name]
    GROUP BY [ Form1].[Table Name].Period
    HAVING (((Form1.[Table Name].Period)=[Enter the Period]));
    As suggested i tried above code but it doesn't work. It shows a message that it can't find form1.mdb file on C:\My Documents Path.

    Please expedite.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nah, that wasn't what was suggested.

    1) I agree with Teddy - this sort of requirement suggests a design error. Why have you got 15 tables of the same structure?
    2) SQL statements are just that - statements. You cannot parameterise column names, clauses or table names. If you want to do this you need to build your SQL statement dynamically in code. Another alternative would be to create a union of all 15 tables, with the source table name in a column and then filter on that. However, that would be a bodge to make up for the design error Teddy is alluding to.
    3) (minor) move your HAVING clause to WHERE - it is much more efficient.

  6. #6
    Join Date
    Sep 2009
    Posts
    79

    How to pass a table name as a parameter in a access query

    Hi All,

    Can you please provide me a example query.

    Thanks for your help in advance.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is the dirty method. It is quick code to write, but not optimal and does not solve the underlying design problem.

    Code:
    PARAMETERS [Enter the Period] Text ( 255 ), [Source Table] Text ( 255 );
    SELECT  Period
          , Sum([Value/obj curr]) AS  [SumOfValue/obj curr]
    FROM    (
                SELECT  "Table1" AS TableName
                      , Period
                      , [Value/obj curr]
                FROM    [Table1]
                WHERE   Period=[Enter the Period]
                UNION ALL
                SELECT  "Table2" AS TableName
                      , Period
                      , [Value/obj curr]
                FROM    [Table2]
                WHERE   Period=[Enter the Period]
                UNION ALL
                SELECT  "Table3" AS TableName
                      , Period
                      , [Value/obj curr]
                FROM    [Table3]
                WHERE   Period=[Enter the Period]
            ) AS well_designed_table
    WHERE   TableName = [Source Table]
    GROUP BY Period

  8. #8
    Join Date
    Sep 2009
    Posts
    79

    How to pass a table name as a parameter in a access query

    Thanks a lot guys. It's working now.

Posting Permissions

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