Solved Creating a button to run a Delete Query

[dullster]

I have a Payroll table that when payroll is due, i run the append query to add it to my transactions table, then I run a delete query to clear out those records and start a new pay period. I created the delete query which works and want to set up a button to run the query. That query is not an option to run with a button. Is there a way to write a macro or something on click to run the query?

 

[Uncle Gizmo]

I created the delete query which works and want to set up a button to run the query.

See my YouTube :-

VBA Beginner - Opening Another Query - Nifty Access​

 

[dullster]

I think i finally got it. Thank you.

 

[Mark_]

As you are deleting transactions relevant to payroll, you may want to instead put in a "Processed" flag that you update. This could be either a boolean or a Date/Time processed. This becomes incredibly useful when you need to show what you had prior to processing.

Personally I'd use date/time so I can archive them after a normal audit period.

This may sound like a headache now, but the first time you have to let a agency see your data for an audit you'll be glad you did.

 

[Pat Hartman]

Your process doesn't sound valid. I agree with Mark, payroll records should not be deleted. My question goes back to why you are creating a temp table to begin with when you can easily use a query to produce the payroll output. If you outline the process step-by-step we may be able to offer a different solution that doesn't involve temp tables or deleting anything.

I would think that when you create payroll "checks", the process would select the employee records using a query, calculate the amounts, probably using VBA because you have to create multiple child records per "check", and append them to a permanent "paid" table. Given that the register will contain details for several types of withholding and perhaps other additional payments like overtime, that there would be a child table with all of the details. These tables would be your audit trail that shows all the details of the final paid amount.

 

[Steve R.]

Implement the suggestion by @Mark_ .
As an added thought. Have a two step process. Before executing the "delete query". Have a button that enables the the "delete query" button. This will minimize accidentally executing the query.

 

[Pat Hartman]

Temp tables cause bloat so you will have to run a C&R more frequently than you probably are in order to clear it out. In addition, when you make them in the BE they may prevent multiple users from running the same process. There are also better methods such as using a "side end" that eliminate the bloat problem in the unavoidable situations where you have to import data periodically from other applications.