Create Conditional Make-Table Query

[Guest]

I want to create a make table query that will only create a new table if the
results from its related select query returns any records.

For instance, i am running a select query that will compare two tables to
check to see if any records have changed. that query may of may not generate
records. if is DOES generate records, then when I run the related make-table
query, i want the query to make the select query table based on the condition
that the select query generated results.

if anyone has any suggestions i would greatly appreciate hearing them. thank
you, ruben.

 

[John W. Vinson]

I want to create a make table query that will only create a new table if the
results from its related select query returns any records.

For instance, i am running a select query that will compare two tables to
check to see if any records have changed. that query may of may not generate
records. if is DOES generate records, then when I run the related make-table
query, i want the query to make the select query table based on the condition
that the select query generated results.

if anyone has any suggestions i would greatly appreciate hearing them. thank
you, ruben.

MakeTable queries are, in my experience, very rarely needed. You're using the
term a "select query table" - do you mean a Select Query? or do you mean to
actually create a new, redundant, permanently stored table in your database?

It *sounds* like - and I may be mistaken here! - that you would be able to use
just a Select query bound to a Form. The user would click some button to open
the Form. In the Form's Load event you could check to see if the form's
recordsource contains any records; if not you could use Msgbox to issue a
message and quit, or if so, just proceed to display the form.

John W. Vinson [MVP]

 

[dbahooker]

this is easy in Access Data Projects

Create Procedure myConditionalMakeTable
AS
IF EXISTS (Select Records From vMyView)
BEGIN

Select *
Into tblMyView

 

[Guest]

John,

Thanks for your reply. What i'm actually doing is running queries in an
existing database. i want to "package" results from those Select queries into
a database that is "shipped" off for our reporting purposes.

So I'm running the Select queries in my existing database and then I want to
run the Make Table queries (and have the table(s) written to the the
reporting DB) IFF the Select queries return results. So the make table
queries will be almost exactly like the Select queries but will first check
to see if there are any results in the Select queries before writing to the
reporting DB (ideally).

I don't actually need the tables to be made in my existing DB, but I'd
rather have the make table query run if there were results in the Select
query. This way I don't have to individually export each query--it will be
written automatically to the reporting DB. Thanks, Ruben.

 

[John W. Vinson]

So I'm running the Select queries in my existing database and then I want to
run the Make Table queries (and have the table(s) written to the the
reporting DB) IFF the Select queries return results. So the make table
queries will be almost exactly like the Select queries but will first check
to see if there are any results in the Select queries before writing to the
reporting DB (ideally).

I don't actually need the tables to be made in my existing DB, but I'd
rather have the make table query run if there were results in the Select
query. This way I don't have to individually export each query--it will be
written automatically to the reporting DB. Thanks, Ruben.

In that case, I'd suggest creating a "shipping" template database with all the
needed tables, empty. Link to this database from your production database, and
use Append queries to populate the empty tables.

This will avoid a lot of the annoyances (255 byte size for all Text fields,
missing or incorrect indexes, etc.) of make-table queries.

John W. Vinson [MVP]