Append and update simultaneously within the same table

[jllortega]

I am using a DB to, among other things, calculate the monthly salary of my service users. Obviously, I have to do it every moth. Most of the data from the previous month will be the same in the next month, except date. Even if some of it is different, having the previous data on sight helps calculate the new salaries.

Every month I have to select all records with the month date (say 07/2015) I want to copy and (1) copy paste the records into the salaries table, and (2) search and replace the new records' dates. For example, records with 07/2015 with say 08/2015 (I have to replace downwards to ensure I don't change the record of the month I need to keep).

It can't be that difficult, but I have tried append and update queries to make this automatically (say by clicking a button) but so far no luck.

Is there a way to do this, better if simple, but can use VBA code if necessary.

Any ideas?

 

[CJ_London]

you can change the date at the same time as the append. Something like

INSERT INTO tblSalaries (payDate, PayrollNo....)
SELECT dateadd("m",1,payDate), PayrollNo...
FROM tblSalaries
WHERE payDate=#07/31/2015#

 

[jllortega]

Many thanks for your prompt answer.

I have simplified my DB so that it has only one table (tblSalaries) with three fields (Id, payDate, Payroll), so as to make it work with the code you provide, but I can't make it so.

Could you provide the code for my DB?

 

[plog]

Is there a way to do this, better if simple,

I don't entirely follow your objective nor method, but I don't think you should be running any action queries (DELETE, UPDATE, APPEND) but should be doing the calculation wiht a straight SELECT query.

Its never really a good idea to replace or delete data. You wouldn't replace July data with August data, but would configure your query to look at just August records.

 

[jllortega]

The goal is to calculate the payroll of number of people. To simplify, let's say three people: A, B, and C.

Tha table would contain the following records for July:

A, 7/25/2015, 1000
B, 7/25/2015, 1200
C, 7/25/2015, 1400

For August, I need to add the following records:

A, 8/25/2015, 1000
B, 8/25/2015, 1200
C, 8/25/2015, 1400

What's the best way to do it automatically?

 

[plog]

What's the best way to do it automatically?

First, nothing is automatic. Second, what is the "it" you are refering to? Importing data to your table? Or calculating salaries?

 

[MarkK]

What's wrong with the insert query CJ provided in post #2? The description of the problem is. "I can't make it so." That query looks good to me for a solution to this. Maybe elaborate on how it fails.

 

[jllortega]

Just adding the new records with the new date to the table. I have to do it every new moth. After that, I can change the salaries (or the values of the many fields the actual table has).

 

[jllortega]

What's wrong with the insert query CJ provided in post #2? The description of the problem is. "I can't make it so." That query looks good to me for a solution to this. Maybe elaborate on how it fails.

The code needs to be adapted. Just copying it won't word. But I am unable to get the right code. This is why I have simplified the table and given tables and fields the same names as in the post.

 

[MarkK]

You've said nothing about how it fails. Imagine you are sick and you go to a doctor. What does the doctor need to know in order to help you?

If code needs to be adapted, what do we need to know to help with that? OK, you've simplified the table, changed field names, and run the suggested SQL. And now, what you haven't described: what are the exact symptoms of the failure?

 

[jllortega]

Syntax error in the instruction INSERT INTO. I am translating from Spanish.