How to copy data to the same table

[Emmanuel]

Am making an payroll system
The system has the following fields in the tables:

Payroll_month, staff_name, rank, basic_salary, ssf, tax, other_deductions and net_salary.

When am doing an entry, I will have to enter the details one by one for each staff.

Is there a way to copy data from one month to the other?

For example: if I have payroll data for Mar-2023 and data for April-2023 will be the same data for Mar-2023.

Is there a vba code to copy all data from Mar-2023 and append to data for April-2023 without having to enter everything one after the other which will be much tedious.

Any help with this will be greatly appreciated

[ranman256]

use an APPEND query. Use the key value as the source data, and append to the target staff member key .

[Emmanuel]

use an APPEND query. Use the key value as the source data, and append to the target staff member key .

Can you give me a sample demonstration so I can understand what you mean

[Micron]

Create a select query that gives you what you want (i.e. for a particular month) then in query design view, change it to an append query. Make a backup of your original table before testing anything on it.

[ArviLaanemets]

You must have a staff registry table like: tblPersons: PersonID, ForeName, LastName, ..., EmployedAt, LeavedAt;
Unless you have same staff for all life of your app, and all your personal remaining with same rank, you also need a table like tblPersonRanks:PresonRankID, PersonID, Rank, ValidFrom, ... (You may consider having possible ranks defined in another table here, and having RankID in this table instead of rank value);
You need a table, where you register all current monthly payments for every person, like tblPersonPayments: PersonPaymentID, PersonID, PaymentTypeID, PaymentSum, IsValid (And probably a table where all possible payment types are registered);
In your payroll table, you will have a row for every payment type valid at date the payment entry was created. Instead persons names and ranks, you'll have PersonID only. Like: tblPayroll: PayrollID, PayrollDate, PersonID, PaymentTypeID, PaymentSum

Every month, you ran a procedure, which:
In case there were any payments for this months registered, either simply deletes them, or asks user must those payments be deleted. When user is asked, and existing entries weren't deleted, the procedure must be aborted;
When the procedure wasn't aborted, it runs an INSERT query, which reads from tblPersonPayments all valid payments for all persons employed currently, adds payroll date, and inserts new payments into tblPayroll.

You need a saved parameter query, which calculates payments for persons for given month. Depending on payment types in tblPayroll, different entries in tblPayroll can be summarized to calculate basic salary, or used to calculate taxes, deductions, etc. down to net salary.

[Bob Fitz]

Emmanuel

I have attached a very simple example that you may find of use.
It has one table which has details of how much was paid (field "Gross"), the date it was paid (field "DatePd"), who it was paid to (field "EmpID") which would be a numeric Foreign key to a table of employees. It also has a Primay Key (field ("PaidID").

It has 2 queries:
qryCopyThis: This returns all the latest records
qryRunPayroll: This uses qryCopyThis and appends those records to tblPaid with todays date

It opens at a Main form which has a subform (showing records in tblPaid) and a button to generate the next set of data by running qryRunPayroll

[Emmanuel]

This is a copy of the database am working on. Please check and see how best it can be worked on. Am able to currently search payroll records for a specified month but i want to be able to copy to another month

[Emmanuel]

Emmanuel

I have attached a very simple example that you may find of use.
It has one table which has details of how much was paid (field "Gross"), the date it was paid (field "DatePd"), who it was paid to (field "EmpID") which would be a numeric Foreign key to a table of employees. It also has a Primay Key (field ("PaidID").

It has 2 queries:
qryCopyThis: This returns all the latest records
qryRunPayroll: This uses qryCopyThis and appends those records to tblPaid with todays date

It opens at a Main form which has a subform (showing records in tblPaid) and a button to generate the next set of data by running qryRunPayroll

Alright. Will take a look at it. Thanks

[Emmanuel]

Emmanuel

I have attached a very simple example that you may find of use.
It has one table which has details of how much was paid (field "Gross"), the date it was paid (field "DatePd"), who it was paid to (field "EmpID") which would be a numeric Foreign key to a table of employees. It also has a Primay Key (field ("PaidID").

It has 2 queries:
qryCopyThis: This returns all the latest records
qryRunPayroll: This uses qryCopyThis and appends those records to tblPaid with todays date

It opens at a Main form which has a subform (showing records in tblPaid) and a button to generate the next set of data by running qryRunPayroll

I checked your file and I must say it does a great job.
But I have a bit of a challenge.
I want it to append the data using the month I choose from the drop down as illustrated in the file I posted

So maybe if a condition may be added. Something like ..”where payroll_date is the value of combo106

[Emmanuel]

Would be glad if I will be able to append only a specified date.
So I can search for only march,2023(at the from section) which is my current payroll data and copy it to April. 2023 when I drop down the “To” combo date.

By this way, I can be April,2023 and be able to prepare payroll for May, 2023 ahead of time when the need arises

[Bob Fitz]

Try the attached db. Post back with any questions.

[Emmanuel]

Try the attached db. Post back with any questions.

Thanks for your feedback and your modified db.
Tried to run payroll from March. 2023 to April. 2023 but it gave me this error…

“Run-time error 3061”
“Too few parameters. Expected 2”

And when I try to debug,
It shows the error is from
“CurrentDb.Execute “qryRunPayroll”

[Emmanuel]

I think it will be easier if there is a code that can copy the filtered results from the payroll date which is displayed in the data sheet when I click on the search and append that filtered data to the month I select in the new month that am preparing the payroll

[Emmanuel]

Create a select query that gives you what you want (i.e. for a particular month) then in query design view, change it to an append query. Make a backup of your original table before testing anything on it.

Would be glad if you could use my db to demonstrate that for me

[Emmanuel]

You must have a staff registry table like: tblPersons: PersonID, ForeName, LastName, ..., EmployedAt, LeavedAt;
Unless you have same staff for all life of your app, and all your personal remaining with same rank, you also need a table like tblPersonRanks:PresonRankID, PersonID, Rank, ValidFrom, ... (You may consider having possible ranks defined in another table here, and having RankID in this table instead of rank value);
You need a table, where you register all current monthly payments for every person, like tblPersonPayments: PersonPaymentID, PersonID, PaymentTypeID, PaymentSum, IsValid (And probably a table where all possible payment types are registered);
In your payroll table, you will have a row for every payment type valid at date the payment entry was created. Instead persons names and ranks, you'll have PersonID only. Like: tblPayroll: PayrollID, PayrollDate, PersonID, PaymentTypeID, PaymentSum

Every month, you ran a procedure, which:
In case there were any payments for this months registered, either simply deletes them, or asks user must those payments be deleted. When user is asked, and existing entries weren't deleted, the procedure must be aborted;
When the procedure wasn't aborted, it runs an INSERT query, which reads from tblPersonPayments all valid payments for all persons employed currently, adds payroll date, and inserts new payments into tblPayroll.

You need a saved parameter query, which calculates payments for persons for given month. Depending on payment types in tblPayroll, different entries in tblPayroll can be summarized to calculate basic salary, or used to calculate taxes, deductions, etc. down to net salary.

I very well understand this explanation. But I just need this my db to work the way I want it and the rest won’t be so much of a problem