Maketable solution

[Adam]

Hi All,

Got a problem and I'm not sure how to solve it !

I have a database which connects to a SQL server via an ODBC
connection.

I've setup a query to pull the last 30 days worth of data, from
whenever the query is run. This then makes a table called 'Main'.

What I've like is everytime the queries run it takes the existing data
from main and puts it into another table labelled after whatever month
and year the data contains. This needs to happen before that main table
is re-populated with another 30 days worth of data.

Does anyone know of a way this can be done, if its even possible ?

Adam

 

[Arvin Meyer]

Assuming this is a SQL-Server solution, you can add a trigger on the SQL
table to populate in the new table as they are entered in the first table.
Do not use a delete trigger and the record will stay in the second table.
You do not need (nor should you use) tables for each month. To do that
violates good design principles. All that is necessary is to query the new
table based on Month/Year for any given month of data.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

That can be done, BUT I would add a datestamp filed to your local table..
Instead of making a new table for every query, simply add a datestamp to an
append query. Will make it a lot easier to aggregate and compare across
months too

 

[Guest]

That can be done, BUT I would add a datestamp filed to your local table..
Instead of making a new table for every query, simply add a datestamp to an
append query. Will make it a lot easier to aggregate and compare across
months too