Run query

[prasadgov]

Hello,

I have an application where data is appended to two tables everyday. Every month, on the first working day a automated job runs at 10.00 pm to create a MTD report using the data from these two tables.
How do I identify the first working day of the month so that I can write a module to run the two delete queries which can be executed at 10.30 pm, after the MTD report is created?
How to use it in the Access application, so that when the 10.30 job runs, it will identify the first working day and run the delete queries.
On other days, the 10.30 job should fail since it is not the first working day.

TIA

TIA

 

[Gasman]

What do you class as the first working day?
Plenty of code here to identify a particular day of the month.

 

[pbaldy]

Is there a reason you don't run the deletes at the end of the process that creates the report?

 

[plog]

How do I identify the first working day of the month so that I can write a module to run the two delete queries which can be executed at 10.30 pm, after the MTD report is created?

Can the actual 1st day of the month ever be attributed to the prior month? Or could the actual last day of a month go to the next month? Is there a situation where February 1st's data would go to the January report? Or where January 31st's data could go to February report?

If not, why do you need to determine the first working day of the month? Why not just use the actual first day of the month?

 

[Gasman]

TBH I would have thought for a Month To Date report, you would want to delete before the first append of the month data?

 

[Pat Hartman]

Your process sounds illogical in the world of relational databases. It is far closer to a spreadsheet solution so maybe you are asking the wrong question.

What if someone forgets to run the report on the first working day?

If you handle the dates correctly, you can run the report on any day for any month and always get the correct result. You simply need to sit back and think a little differently about your approach. Deleting data is almost certainly not the solution either.

 

[prasadgov]

Your process sounds illogical in the world of relational databases. It is far closer to a spreadsheet solution so maybe you are asking the wrong question.

What if someone forgets to run the report on the first working day?

If you handle the dates correctly, you can run the report on any day for any month and always get the correct result. You simply need to sit back and think a little differently about your approach. Deleting data is almost certainly not the solution either.

All the raw data is through .csv files which are archived on daily basis. Only the day to data is inputted to the database and the daily report is run. Since there was a need for a MTD report, I created two tables to which the daily data is appended everyday. The data has a lag of one day. At the end of the first working day, the MTD report is run at 10.30 pm. after the report is run, I thought running the two delete queries to clear the data in the append tables would make it ready for the data from the next day.

 

[GPGeorge]

All the raw data is through .csv files which are archived on daily basis. Only the day to data is inputted to the database and the daily report is run. Since there was a need for a MTD report, I created two tables to which the daily data is appended everyday. The data has a lag of one day. At the end of the first working day, the MTD report is run at 10.30 pm. after the report is run, I thought running the two delete queries to clear the data in the append tables would make it ready for the data from the next day.

" Every month, on the first working day a automated job runs at 10.00 pm to create a MTD report using the data from these two tables."

If the report is run on the first working day of a month, the MTD (which I assume means "Month To Date") report will only show records from the 1st day of the month to that date. Is that what you want? I don't see how that fits here. Is it possible you actually want data from a different time period?