Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Feb 2012
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query from multiple tables

    Data comes from a propriatry source. It can be exported to Excel. Then it is imported into Access. Currently ther are over 70 individual tables. All tables have the same number of fields, alot. We only need 5 or 6 fields..
    The question is can you run a query which ask for the TABLE name first, then uses the 5 fields that we really need.
    Paul

  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
    No. You need a form that will ask the user for the table(s) needed and then form the query for you.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Feb 2012
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A form to ask for the table

    Any helpful hints or recommendations to accomplish the task suggested?

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I am not sure, but it sounds like each query only actually uses one table.
    If so there are two questions.
    1. How do you put a list of tables onto a form
    2. If you choose a table from the list, how do you have the query use that table.

    Neither task is trivial

    1
    This SQL will produce a list of tables. You could use this to create a query called qryTables, then put a combo on a form with a RowSource of qryTables. Call the combo comboTables
    Code:
    SELECT MSysObjects.Name
    FROM MSysObjects
    WHERE (((MSysObjects.Type) In (1,4,6)) AND ((Left([Name],4))<>"MSYS")
    AND ((Left([Name],1))<>"~"));
    If you don't actually want all the tables listed then you would need some way of restricting this list.

    2
    First create a query using any of the tables. It does not matter which. Save it as something...qryMyQuery.
    Then use code like this. Put this behind a command button.
    Code:
    Dim strTable as string
    Dim SQL as string
    
    If not isnull(me.comboTables) then
      strTable = me.comboTables
      SQL = "Select fieldname1, fieldname2, fieldname3, fieldname4, fieldnamet5 from " & strTable & "Order by fieldname1"
      Currentdb.queryDefs("qryMyQuery").sql = SQL
      Docmd.Openquery "qryMyQuery"
    else
     msgbox "Select a table from the list"
    
    end if
    You need to replace the fieldnames in the above with your actual fieldnames.
    This code rewrites qryMyquery each time to use the table you want. You only ever have one query, but it keeps changing.

    (I have written this code without testing it as I write, so I may have made a typo or some other error. Post back if something does not work.)
    Regards
    John



  5. #5
    New Lounger
    Join Date
    Feb 2012
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    using the SQL code

    please help me here.
    Not sure how to accompish the first task. Not sure where to put the code. Select.. Seems where ever I put it does not work. So could it be the code or whre i'm putting it. Where do you put the SQL and how do you call it?
    thanks paul

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Whenever you create a query, Access stores the query as a piece of SQL. In normal use you don't see it, but you can look at it ( and edit if you want) by switching to SQL view of a query. You don't say what version of Access you use...the method of viewing the SQL is slightly different in different versions. In 2007 and 2010 it is under View on the Home tab.

    ViewSQL.gif

    So start to create a new query the normal way. The Show Table dialog pops up for you to add tables to the query. Close it without adding any tables, then immediately switch to SQL view of this query.
    You will just see Select; Delete this and paste in the SQL I posted.
    Test the query works by switching to datasheet view. Save it as (say) qryTables.
    Regards
    John



  7. #7
    New Lounger
    Join Date
    Feb 2012
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I get a compile error with the SQL.. I'm using 2010
    asseccerror.JPG

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I suspect you did not paste all the SQL I gave you. It looks like you left out the last bracket. The SQL I posted works for me in 2010.
    Regards
    John



  9. #9
    New Lounger
    Join Date
    Feb 2012
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    new challenge. create a blank database and imported tables only. The query above now works.
    At the step of creating a FORM ......then put a combo on a form with a RowSource of qryTables. .......
    using the combo box wizard I get " no valid fields can be found in qryTables, please select a new source"

    Also, the first Record is showing f_4F9F14BD3CC24FC9BD1EEA8B81596AC8_Data" instead of a actual table
    Last edited by paweaver; 2012-03-16 at 10:31.

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by paweaver View Post
    then put a combo on a form with a RowSource of qryTables. .......
    using the combo box wizard I get " no valid fields can be found in qryTables, please select a new source"
    I get the same thing. I had not tested this as it did not occur to me that there would be a problem with this.
    Here is a workaround. Change the Row Source Type of your combo to Value List then use the following code to fill the values from the query. The code goes in the On Load event of the form. Don't repeat the Private Sub Form_Load() and the End sub.
    Code:
    Private Sub Form_Load()
        Dim strTableList As String
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
    
        Set db = CurrentDb
        Set rs = db.OpenRecordset("qryTables", dbOpenSnapshot)
        If Not rs.EOF Then
            rs.MoveFirst
    
            Do While Not rs.EOF
                strTableList = strTableList & rs("Name") & ";"
                rs.MoveNext
            Loop
            strTableList = Left(strTableList, Len(strTableList) - 1)
            ' Substitute the name of your combo box
            Me.Combotables.RowSource = strTableList
        End If
        ' Clean up
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End Sub
    Also, the first Record is showing f_4F9F14BD3CC24FC9BD1EEA8B81596AC8_Data" instead of a actual table
    Don't know what to make of that. Get your database to show you Hidden and System objects, so you can see the full list of Tables.

    Right Click on the top of the Navigation Bar and choose Navigation Options.
    NavOptions.gif
    Then tick the boxes for Show Hidden Objects and Show System Objects. See what you can find.
    Look in MySysObjects (That is the table the query uses.)
    Regards
    John



  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I attach a little working demo.
    Attached Files Attached Files
    Regards
    John



Posting Permissions

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