make-table query, naming the table

[Guest]

Can I have a make-table query name the resulting table based on the search
criteria. For example, my query searches by [Forms]![Form1]![Combo7]. I
tried pasting this value directly into the SQL languange but to no avail.
Ultimately, I'd like to create multiple tables using the same make-table
query, with each table named based on my ComboBox value. Is this even
possible? Or will I have to create multiple make-table queries?

Thanks!

 

[Ken Snell [MVP]]

You can do it if you build the SQL statement in VBA code and run the query
from there. You cannot do it in the query designer itself.

 

[Guest]

Thanks Ken. I'll give that a shot. Is it as easy as using the DoCmd.RunSQL
function? Sorry, but not very familiar with building queries in VBA.

You can do it if you build the SQL statement in VBA code and run the query
from there. You cannot do it in the query designer itself.

--

Ken Snell
<MS ACCESS MVP>

Can I have a make-table query name the resulting table based on the search
criteria. For example, my query searches by [Forms]![Form1]![Combo7]. I
tried pasting this value directly into the SQL languange but to no avail.
Ultimately, I'd like to create multiple tables using the same make-table
query, with each table named based on my ComboBox value. Is this even
possible? Or will I have to create multiple make-table queries?

Thanks!

 

[John Vinson]

Can I have a make-table query name the resulting table based on the search
criteria. For example, my query searches by [Forms]![Form1]![Combo7]. I
tried pasting this value directly into the SQL languange but to no avail.
Ultimately, I'd like to create multiple tables using the same make-table
query, with each table named based on my ComboBox value. Is this even
possible? Or will I have to create multiple make-table queries?

Thanks!

I have to wonder WHY you would want to do this. It sounds like you're
creating multiple tables of identical structure, differing only by the
table name (and the actual contents of course). This is simply *BAD
DESIGN* and is almost certainly not necessary!

What is the purpose of creating these tables? What do you plan to do
with them that you can't do with a simple Select query?

John W. Vinson[MVP]

 

[Guest]

You are correct that I am guilty of "bad design." I only had the thought of
creating multiple tables of the same structure after being stumped with the
DSum function. Perhaps you can help:

I needed a field that gave me "percent of total." But I need percent of
total BY a certain classification (eg Client Type). If there is a way to
define the 'criteria' arguement in DSum by a value in a separate field, that
would do the trick. Otherwise, I need to create multiple tables (one for
each Client Type). Does this make sense? I tried variations on the
following, but it did not work:

[tblsource.Total]/dsum("[tblsource.Total]","tblclient.Type")

Thanks!

Can I have a make-table query name the resulting table based on the search
criteria. For example, my query searches by [Forms]![Form1]![Combo7]. I
tried pasting this value directly into the SQL languange but to no avail.
Ultimately, I'd like to create multiple tables using the same make-table
query, with each table named based on my ComboBox value. Is this even
possible? Or will I have to create multiple make-table queries?

Thanks!

I have to wonder WHY you would want to do this. It sounds like you're
creating multiple tables of identical structure, differing only by the
table name (and the actual contents of course). This is simply *BAD
DESIGN* and is almost certainly not necessary!

What is the purpose of creating these tables? What do you plan to do
with them that you can't do with a simple Select query?

John W. Vinson[MVP]

 

[Guest]

Sorry, forgot to include the domain:

[tblsource.Total]/dsum("[tblsource.Total]","tblsource","[tblclient.Type]")

You are correct that I am guilty of "bad design." I only had the thought of
creating multiple tables of the same structure after being stumped with the
DSum function. Perhaps you can help:

I needed a field that gave me "percent of total." But I need percent of
total BY a certain classification (eg Client Type). If there is a way to
define the 'criteria' arguement in DSum by a value in a separate field, that
would do the trick. Otherwise, I need to create multiple tables (one for
each Client Type). Does this make sense? I tried variations on the
following, but it did not work:

[tblsource.Total]/dsum("[tblsource.Total]","tblclient.Type")

Thanks!

Can I have a make-table query name the resulting table based on the search
criteria. For example, my query searches by [Forms]![Form1]![Combo7]. I
tried pasting this value directly into the SQL languange but to no avail.
Ultimately, I'd like to create multiple tables using the same make-table
query, with each table named based on my ComboBox value. Is this even
possible? Or will I have to create multiple make-table queries?

Thanks!

I have to wonder WHY you would want to do this. It sounds like you're
creating multiple tables of identical structure, differing only by the
table name (and the actual contents of course). This is simply *BAD
DESIGN* and is almost certainly not necessary!

What is the purpose of creating these tables? What do you plan to do
with them that you can't do with a simple Select query?

John W. Vinson[MVP]

 

[Ken Snell [MVP]]

Better to use the Execute method of CurrentDb:

Dim dbs As DAO.Database
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "your sql statement"
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken. I'll give that a shot. Is it as easy as using the
DoCmd.RunSQL
function? Sorry, but not very familiar with building queries in VBA.

You can do it if you build the SQL statement in VBA code and run the
query
from there. You cannot do it in the query designer itself.

--

Ken Snell
<MS ACCESS MVP>

Can I have a make-table query name the resulting table based on the
search
criteria. For example, my query searches by [Forms]![Form1]![Combo7].
I
tried pasting this value directly into the SQL languange but to no
avail.
Ultimately, I'd like to create multiple tables using the same
make-table
query, with each table named based on my ComboBox value. Is this even
possible? Or will I have to create multiple make-table queries?

Thanks!

 

[John Vinson]

Sorry, forgot to include the domain:

[tblsource.Total]/dsum("[tblsource.Total]","tblsource","[tblclient.Type]")

The third argument to DSum is *any* valid criterion, in the form of a
SQL WHERE clause (without the word WHERE).

Note that enclosing the tablename and the fieldname together in a
single set of brackets is incorrect - Access will look in the current
query for a field named [tblsource.total], and if the field is named
[total] it *won't* find it!

Assuming that there is a Text field named [Type] in the current query,
try

[tblsource].[Total] / DSum("[total]", "[tblsource]", "[Type] = '" &
[Type] & "'")

For example, if the current record has a Type value of "Gold Star
Client", the third argument to DSum will be

[Type] = 'Gold Star Client'

and it will sum just those records.

John W. Vinson[MVP]