Automate monthly create entry

[Kayleigh]

Hi
Can someone help me with creating an append query to create invoices once a month if child is registered. It will run when main screen opens but user may not open DB every day so how to i program it to check if last invoice was 30 days ago and create new record?
I would also like to do a similar solution on a weekly basis.

Maybe I'm thinking too much into the problem - may be a simple solution!

EDIT: Just did a quick Google and found this - https://bytes.com/topic/access/answ...end-update-queries-automatically-weekly-basis . Is it the most advisable option?

Part of DB is attached.
Many thanks

 

[pbaldy]

That could work. I'd probably create a stand alone db that did the desired process when it started and then shuts down, and fire it off from scheduled tasks.

 

[Kayleigh]

If I create a query of last invoice for all students, then query against it to filter out only those more than a month, append to create new invoice entries. Invoice date would be lastDate + 1 month. Are my thoughts on the right lines?

I'm concerned that the end date for pupil may mean an incorrect invoice is issued or missed.

 

[plog]

Agree with pbaldy and here's some more specifics:

Create a new database and link to your 3 tables
paste in your query
make a macro that runs that query and then exits access
use the task scheduler to run your database and execute your macro on the schedule you want (I suggest when no one will be in the database)

I don't think you need to look at the last time someone was invoiced--everyone gets invoiced once a month via this process that gets run. Let's say the first of every month you invoice--it doesn't really matter the last invoice date, you simply need to determine the kids who's dates line up:

StartDate <= Date() AND EndDate Is Null
OR
StartDate <= Date() AND EndDate>=DateAdd(-1, Month, Date())