Question Clear Field based on date (Jan1) (1 Viewer)

[jimbo89]

On a membership Db, I want to keep track of which members have paid their fees for the year. That bit's easy but on the 1st of January, I want the FeesPaid field to clear, the fieldname to add 1 year ie [2010feespaid] = [2011feespaid] ready for new input, and the 2010 data ie clubname to transfer to a new table [2010Members] which is created automatically.

I've just found this:

DoCmd SetWarnings False
DoCmd OpenQuery [MakeTableQuery]
DoCmd openQuery [UpdateQuery]
DoCmd SetWarnings True

Where the MakeTableQuery will create and populate the NEW Table
the UpdateQuery will rename the field and clear all data in that field

Will this work and where would that go to run the queries automatically on Jan 1?
Thanks

Jimbo89

 

[Fuga]

Hi,

I think you should consider putting your members info in one table, and the fees payments records in another table.

Give your members an ID (Primary key) (for instance with an autonumber in your members table). Then link that ID to registered payments in your payments table.

This way, you use a missmatch query to find those that are in your members table but not in your payments table (after the 1st january 2011 or whatever you choose.

This will give you a lot more options later on.

Fuga.

 

[jimbo89]

Thanks Fuga, I probably should have known to use a payments table linked to the members table.....memory lapse. I'll do that and try some variations for updating when required. Thanks again for your response.