Query from multiple tables

Topic

New Reply

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

Reply | Quote

Replies

No. You need a form that will ask the user for the table(s) needed and then form the query for you.

Reply | Quote

Any helpful hints or recommendations to accomplish the task suggested?

Reply | Quote

I am not sure, but it sounds like each query only actually uses one table.
If so there are two questions.

[*]How do you put a list of tables onto a form
[*]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.)

Reply | Quote

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

Reply | Quote

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.

30386-ViewSQL

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.

Reply | Quote

I get a compile error with the SQL.. I’m using 2010
30390-asseccerror

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.
30397-NavOptions
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.)

Reply | Quote

this is the exact code I’m pasting in from above and i get the comile error:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type) In (1,4,6)) AND ((Left([Name],4))”MSYS”)
AND ((Left([Name],1))”~”));

Reply | Quote

I attach a little working demo.

Reply | Quote