Conditional Make Table Query

  • Thread starter Thread starter KHogwood-Thompson
  • Start date Start date
K

KHogwood-Thompson

Hi,

I have a table called SubbieTrans. I run a "Make-Table" query that creates
this table, this runs automatically via a macro attached to a button on the
opening form. I would like only to make the table if the last time it was
created was greater than 7 days ago.

Is this possible? And if so, how can I do it?

Many thanks
 
It would be better using a small code rather than a macro.

But if you are happy with you macro may as well keeping using it.
You can add a field to the make/table table (hope that make sense). In your
make table query add a calculation to add Date() to this field.

Next in your macro a the criteria (in the condidtion column) using if more
than 7 days before today (use DateAdd -7) then run if not, then have a
message saying it's not more than 7 days old
 
Hi,

I have a table called SubbieTrans. I run a "Make-Table" query that creates
this table, this runs automatically via a macro attached to a button on the
opening form. I would like only to make the table if the last time it was
created was greater than 7 days ago.

Is this possible? And if so, how can I do it?

Many thanks
Click to expand...

My first question would be... WHY?

In practice make-table queries are rather rarely needed. Most things that you
can do with a new Table can be done just as well with a Select Query: export,
forms, reports, etc. And a Select query can have parameters that search for a
seven-day date range (or any other desired date range, or any other
combination of fields); and do so without the overhead, poor performance and
bloat of a maketable query.

What purpose does this new table serve?
 
Hi John,

Many thanks for the advice, and yes I understand your point, however the
database that I am using has linked tables to a finance package that is very
poorly written, if you run select queries on certain linked tables, then it
takes an absolute age to return records. I have found if I make a table using
the criteria that I need, then the reduced record and column table is easier
and quicker to work with for the end-user.

The purpose of my enquiry was to establish whether there was a way for the
make table query only to be run if new records had been added to the linked
tables, and the way to make this easier was just to run it every 7 days
rather than everytime the database is opened.

Hope this now makes a bit more sense, long-winded I know but this is not the
easiest of databases to work with!
 
Hi John,

Many thanks for the advice, and yes I understand your point, however the
database that I am using has linked tables to a finance package that is very
poorly written, if you run select queries on certain linked tables, then it
takes an absolute age to return records. I have found if I make a table using
the criteria that I need, then the reduced record and column table is easier
and quicker to work with for the end-user.

The purpose of my enquiry was to establish whether there was a way for the
make table query only to be run if new records had been added to the linked
tables, and the way to make this easier was just to run it every 7 days
rather than everytime the database is opened.

Hope this now makes a bit more sense, long-winded I know but this is not the
easiest of databases to work with!
Click to expand...

Ok, this is a good case for make-tables <g>.

If your datasource has some sort of identifying field, you could base the
MakeTable query on an "Unmatched" query, joining the local table to the remote
table with a Left Join, and only attempting to add *new* records. This would
probably work better if you had a permanent local table and used an Append
query rather than creating an all new table each time.

But if the remote table is that bad, you may be stuck.
 
Back
Top