With the statement in SQL

SELECT * FROM firstdatatable

I can use the query to pull out data from firstdatatable. Say I also had:

firstdatatable
seconddatatable
thirddatatable
fourthdatatable

And a combo box that put into a Global Variable declared as a string the valuw "first","second", "third",
fourth.

Could I add the element datatable to the stored element first, second etc to call up the table required, so
that if the combo box allocated "third" the SQL would be:

SELECT * FROM "GBL_Combo_Output & 'datatable'"

Such that I could use this query and drag data from the four different tables depending on the setting of the
combo box?

I have had a google, but most of the answers revolve around doing this sort of operation using the WHERE
Criteria as opposed to just bringing up the whole table. I want to use this select query for reporting and
data entry on completion, to read from and amend the table which corresponds to the combo output.

Thanks J



--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

bezz wrote:

After reading your request several times what you want to do is still
unclear.

I'll take a stab at it but is most likely not what you want.

In the afterupdate event of the combo and assuming the combo name is
MyCombo and the value you want is in the second column (indexes start at 0).
Dim strSQL As String 'define string
strSQL = "Select * From " & Me.MyCombo.Column(1) 'tbl name 2nd col
Me.Recordsource = strSQL 'change this forms recordsource
or
Me.AnotherComboName.Rowsource = strSQL 'change a rowsource in a combo

Check out Column, Rowsource, and Recordsource in help.

Hi,

I don't want to use the combobox directly to drive the query, only to set the global variable so that any
query run will use the Global Variable. The explanation was generic.

To be more specific say I had:

SwiftsureDataTable
SovereignDataTable
SceptreDataTable

The drop down box will have as its bound column, Swiftsure, Sovereign Sceptre etc, and I wish to pull the
data from the relevant table and use the SQL query as a source query for generating reports,updating data, as
each table, although having the same structure will e at different levels of maturity

I am asking in effect, how to add "Swiftsure" to "DataTable" in the SQL Statement so I get:

SELECT * FROM 'Swiftsure' & 'Datatable'

If that is indeed possible, I can't use the strSQL as I want to make the query available to other queries,
and want to just make the combo box output the variable.

I know in query parameters to get a Global Variable as a criteria I have to use a function GetBoatName() for
example.

Thanks

J





--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

bezz wrote:

Sub q()
Dim num As String
Dim q As QueryDef
Dim strSQL As String
Dim strVar As String
Dim dbs As Database


num = InputBox("Enter 1, 2 or 3")

Select Case num
Case "1"
strVar = strSQL & "Table1"
Case "2"
strVar = strSQL & "Table2"
Case "3"
strVar = strSQL & "Table3"
End Select

If strVar > "" Then

Set dbs = CurrentDb
dbs.QueryDefs.Delete "QueryTest" 'create a query QueryTest

strSQL = "Select * From " & strVar & "Datatable"

Set q = dbs.CreateQueryDef("QueryTest", strSQL)

MsgBox q.SQL 'display the query's SQL statement

End If

MsgBox "Done"

End Sub

Here's one way:
Create a saved union query to get the data from all 3 tables, adding a
column to identify which table the rows are coming from:

select 'SwiftsureDataTable' As Datasource,*
FROM SwiftsureDataTable
UNION ALL
select 'SovereignDataTable' As Datasource,*
FROM SovereignDataTable
UNION ALL
select 'SceptreDataTable' As Datasource,*
FROM SceptreDataTable

Call it UnionedBankData

Then you can filter the results by the Datasource column:

Select * FROM UnionedBankData
WHERE Datasource=[Forms]![formname]![comboboxname]


The drawback is that the results will not be editable. The only alternative
is creating the query in code via dynamic sql, i.e., concatenating the name
of the table from the combo into the string.


bezz wrote:

I don't want to run this from code. I want to set up one query which is an SQL query that will work from
anywhere in the database, and exists as a standalone select query, but will access the table that is referred
to by the Global Variable, which would be strVAR in your example below.

What I would like to do is in effect have a query that does the same as your strSQL below, but is not
required to be generated from code. I already have the base tables, and I have set the first part by setting
up the Global Variable for Boatname, which will stay live until another Boatname is selected.

Using your strSQL as a basis:

strSQL = "Select * From " & strVar & "Datatable"

In effect I just want an SQL query that is

Select * From " & strVar & "Datatable"

Where strVar is my global variable. I relaise this may not be possible, or I might have to use
GetGlobalBoatname() in the SQL statement as strVar cannot be a Global Variable.

I do not want to drive it from code or an event as I want to just pick the query up, allocated to the right
table anywhere within the database.

If I was going to use code and limit my options, I would just use Select...Case and have a command button for
all options, calling the table directly from the Form I use to select the Boat Name. Using a Global Variable
and SQL to build the query dynamically saves the requirement to have any code, and gives maximum flexibility
for further development.

J






--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

Bob,

Reading your response, it would appear that there is no way to concatenate the Global Variable with the fixed
part of the table data i.e. Swiftsure & DataTable to generate the query?

I can't use union as I do need to edit the table, so it looks as if I will need to build a more complex form,
get rid of the combo control and just allocate a command button to address each table directly.

I have been googling heavily in between posts and I have seen loads of examples where the WHERE function is
concatenated, but none at all where the table name is, so I guess it isn't possible.

Thanks

J


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

bezz wrote:

I do not understand your refusal to use VBA to do this job.

It is not possible to replace/substute/parametize a table
name in a query. OTOH, it is easy enough to do something
like what Salad suggested. Use a little VBA code in the
combo box's AfterUpdate event procedure to change the
definition of a query named MyQuery:

strSQL = "SELECT * FROM " & Me.thecombobox
CurrentDb.QueryDefs!MyQuery.SQL = strSQL

Then any report/form/query can use MyQuery as if it were the
table selected in the combo box.

I hope you realize that all this fooling around is because
you have separate tables for the same type of data. A
properly normalized table structure would have ONE table
with all your existing fields plus a field that specifies
the boat (essentially an updatable version of Bob's UNION
query). With this normalized table, you could retrieve the
data for any boat simply by filtering the boats table.

--
Marsh

bezz wrote:
of "concatenate" - see below

the examples you found that used VBA to concatenate a sql string. I think
you are missing the difference between using VBA to concatenate strings
together to form sql statements (google dynamic sql) and parameterizing the
query. Two different concepts entirely. You can parameterize data values:
you cannot parameterize objects (tables and columns).

Parameterization:
select ... from ... where fieldname = [enter parameter value]
or
select ... from ... where fieldname = Forms!formname!combobox
There is no concatenation being done in the above statements. Concatenation
involves VBA ... you will typically see the concatenation operator (&)
involved.


Concatenation (dynamic sql)
See salad's example.

I should echo Marsh's point: you should have a single table containing the
data from all the sources, identified by a column to distinguish the
sources.Even if you are importing data from these different sources, your
final step should be to append the imported data into this concolidation
table so as to make your querying easier.