Help with coding needed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there. I need to find the code to do the following. I don't have much of
an idea... if someone could help that would be fantastic.

OK... onto the nerding.

I have 3 tables of information

Table 1 | Table 2 | Table 3 |

I want to make the database, so that EVERY YEAR on July 31st, the database
will:

- Delete all the current data in Table 3
- Transfer the data from Table 1, to Table 2
- Transfer the data from Table 2, to table 3

If you don't know how to do it using a date, alternatively, you could make
the above happen at a click of a button?

If anyones clever enough to know the right code, and how to do this please
tell me... it will be mightily appreciated.
 
Well, you can do this a number of ways:

One is to simply delete Table 3, then rename Tables 2 and 1, and create
another Table 1.

Another is to execute a series of SQL statements:
Delete * from [Table 3]
Insert into [Table 3] Select * from [Table 2]
Delete * from [Table 2]
Insert into [Table 2] Select * from [Table 1]
Delete * from [Table 1]

HOWEVER... the fact that you have three tables with the same data fields,
distinguished only by date, is an indication of poor database design.

You should have only one table (and if necessary add a date or a
"RecordYear" field). You can then easily filter to return records from a
particular year, and if required, you can delete old records from the table
after three years.
 
Try this, No Guarantees!!!
it worked on mine, but make sure you try it on data that doesn't matter!

Create a delete query and using all of the fields in table3 and name it
deletetable3

Create an append query using table2's fields and append to table3 fields(put
the data in the corresponding fields)then name it updatetable3

Create a delete query and use all of the fields in table2 and name it
deletetable2

Create an append query using table1's fields and append to table2 fields(put
the data in the corresponding fields)then name it updatetable2

if you need to clean out table1,do the following
Create a delete query and use all of the fields in table1 and name it
deletetable1

Then on the form(what ever form you want to use) put the following in the on
open event:

If DatePart("m", date) = 7 Then 'seventh month of the year
If DatePart("d", date) = 31 Then '31st day of the month
'To hide the warnings use
'If not delete the next line with theses messages
DoCmd.SetWarnings False

DoCmd.OpenQuery "deletetable3"
DoCmd.OpenQuery "updatetable3"
DoCmd.OpenQuery "deletetable2"
DoCmd.OpenQuery "updatetable2"
'if you want to clean out table1 use the next line
DoCmd.OpenQuery "deletetable1"
DoCmd.SetWarnings True
End If
End If

Like I said this worked on mine.
Use data that doesn't matter.

I am still learning about Access 2003,
David W
 
Back
Top