Number increment in an Append query 1

[Atalla]

Hi all,
How to increment an ID field with "Number" as data type in an Append query?

That is, inserting fields from Table1 into Table2, relationship is 1 to many. The link between both tables is that primary key of Table1(ID)is a foreign key in Table2. Table2 has its own primary key.

Therfore, what is required, is to Append Table2 and increment the primary key.

Thanks in advace for your help.

 

[scriverb]

Is the primary key in Table2 an AutoNumber field? If so it will increment itself automatically. If not, are you asking to have the query pickup the last or Max of this primary key field and increment from that point on? Please explain a little further. Bob Scriver

 

[Atalla]

Hi Bob,
Thanks for replying. The primary key in both tables are not AutoNumber fields, they are Number fields. To explain a little further, Table1 contains all employees related info; FirstName, LastName, phone number, telephoneID, salesID, supervisor name. Now, the way my program works is that when those info are entered and saved in Table1, Table2 is updated accordingly containing the last 3 fields (telephoneID, salesID and supervisor name). Since, those 3 fields change quite often and I must keep a historical data, therefore, when changes occur, the current record in Table2 is ended (with an EndDate and EndReason) and a new record is created with new info. Thus, one to many relationship.

For further requests in my program, I am required to Update, Append Table2. All queries work fine including the Append query in request, i.e., appending Table2, the only thing is that I don't know how to append the primary key in Table2? When I run the query now, all records are appended as requested except the primary key has a Null value!
Thus, if last record was 500, then new appended record would be 501.

 

[scriverb]

Okay, I understand your situation but I am going to ask a question here about why that Table2 primary key is not an autonumber? It would be taken care of by the ACCESS and the numbers would be assigned automatically in a consecutive order. So, if the last record was 500 your append query would start at 501.

Saying that after you respond if you still need a manually mantain sequential number I am sure we can figure something out for you but it seems a little unnecesary considering that the number field characteristics that have in place sound an awful lot like an AutoNumber field type. Bob Scriver

 

[Atalla]

I absolutely agree with you, but the thing is, well, it's a long story and I am trying not to go into too much details. The tables are on a server and DBA (database adminstrators) were the ones in charge of designing it in Oracle and doing the Insert/Update and incrementing the records. I created the User Interface, i.e., I don't have right access to change the table's design and forget about contacting DBA to do that! So, we'll have to figure out a manual solution.

 

[scriverb]

Okay, I totally understand the (DBA) problem. Been there and had to deal with that before. Frustrating. Let me get back to you on how to pickup the last number and continue on with it. Be back in a few. Bob Scriver

 

[scriverb]

Atalla, I think I have this one. There has been much discussion in TT about query incremental numbers but none that answer your specific needs here but I think I have a method that will work. Rather than use Table1 and Table2 and some fictious field names why don't you identify the actual table names and field names so that I can give you specific code to do the job for you. Then you can just cut and paste into your database.

Tell me the actual names of the tables. The names of the primary keys in both tables. If you just wanted to send me the table structure for both in a database that would do the job. Don't send any data but just the empty tables. See email address in my profile.

Bob Scriver

 

[Atalla]

Appreciate very much your help. Below are the tables name:

Table1:
Name: CCADMIN_CC_EMPLOYEE
Primary key: CSR_AGENT_NO
Table2:
Name: CCADMIN_WORK_HISTORY
Primary key: WORK_HISTORY_ID
Foreign key: CSR_AGENT_NO

All names are in upper case letters.
Thanks again.

 

[scriverb]

What is the criteria for selecting the records from your first table(CCADMIN_CC_EMPLOYEE) to be used to create the new records in second table(CCADMIN_WORK_HISTORY)? Bob Scriver

 

[Atalla]

I have 3 criterias:
1) END_DATE: Is Null
2) END_REASON: Is Null
For those 2 criterias, it selects only Active employees who are still with the company, i.e., not terminated/resigned.
3) There is a field that's connected to a query containing a number ratio. If new ratio in that query is not equal to the current ratio in CCADMIN_CC_EMPLOYEE table then end current record and create new one.

 

[scriverb]

This is my plan for you:
1. Create a Make-Table query from the design of your original query with all of the selection criteria and fields intact. This new table will be overwritten each time your run the process.
2. Alter the table so that it has an AutoIncrement field. This field will start with 1 and increment throughout the number of records in your selected records. We will ADD this value to the value of the last records Primary Key field value in table CCADMIN_WORK_HISTORY. So as we process record one from this temp table and the last record is record 500 in your CCADMIN_WORK_HISTORY table we will insert the new record with a value of 500 + 1 to your tables PrimaryKey field value.

So, Please copy and send me the SQL code from your query so that I may use it to create the Make Table query SQL. I then will also use it for the final Insert.

Does this make sense to you. It is a little confusing but the Incremental value is a difficult process to create in a query. Bob Scriver

 

[Atalla]

I understood your plan but I don't know if it will work. Below is the SQL for the original query (qryNewFte) that calculates the New ratio. I guess the only field you're interested in this code is the field "NewFte".

SELECT CCADMIN_CC_EMPLOYEE.CSR_AGENT_NO, tblAdpPayroll.FileNumber, tblAdpPayroll.HoursWorked, tblAdpPayroll.Overtime, tblAdpPayroll.VacationHours, IIf([EMPLOY_TYPE_CD]=1 Or [EMPLOY_TYPE_CD]=3,[HoursWorked]+[Overtime]+[VacationHours],[HoursWorked]+[Overtime]) AS TotalHours, CDbl(Format([TotalHours]/80,"#.0&quot) AS NewFte, CCADMIN_CC_EMPLOYEE.EMPLOY_TYPE_CD
FROM CCADMIN_CC_EMPLOYEE INNER JOIN tblAdpPayroll ON CCADMIN_CC_EMPLOYEE.MISC_TEXT_1 = tblAdpPayroll.FileNumber
WHERE (((CCADMIN_CC_EMPLOYEE.EMPLOY_END_REASON_CD) Is Null));

Another SQL code of the Append query I created (except for the ID field that we are trying to solve):

INSERT INTO CCADMIN_WORK_HISTORY ( CSR_AGENT_NO, JOB_FUNCTION_CREATE_DATE, CC_TEAM_MANAGER_ID, LOCATION_CD, CSR_MERIDIAN_ID, SALES_REP_NO, CSR_STATUS_CD, TEMP_ASSIGN_ID, EMPLOYER_CD, EMPLOY_TYPE_CD, JOB_FUNCTION_CD, CC_GROUP_CD, POSITION_EFFECTIVE_DATE, LAST_UPD_DATE, LAST_UPD_USERID, JOB_FUNCTION_CREATE_USERID, OPER_ID, FTE_RATE )
SELECT CCADMIN_CC_EMPLOYEE.CSR_AGENT_NO, Date() AS JOB_FUNCTION_CREATE_DATE, CCADMIN_CC_EMPLOYEE.CC_TEAM_MANAGER_ID, CCADMIN_CC_EMPLOYEE.LOCATION_CD, CCADMIN_CC_EMPLOYEE.CSR_MERIDIAN_ID, CCADMIN_CC_EMPLOYEE.SALES_REP_NO, CCADMIN_CC_EMPLOYEE.CSR_STATUS_CD, CCADMIN_CC_EMPLOYEE.TEMP_ASSIGN_ID, CCADMIN_CC_EMPLOYEE.EMPLOYER_CD, CCADMIN_CC_EMPLOYEE.EMPLOY_TYPE_CD, CCADMIN_CC_EMPLOYEE.JOB_FUNCTION_CD, CCADMIN_CC_EMPLOYEE.CC_GROUP_CD, Date() AS POSITION_EFFECTIVE_DATE, Date() AS LAST_UPD_DATE, CCADMIN_CC_EMPLOYEE.LAST_UPD_USERID, CCADMIN_CC_EMPLOYEE.JOB_FUNCTION_CREATE_USERID, CCADMIN_CC_EMPLOYEE.OPER_ID, qryNewFte.NewFte
FROM CCADMIN_CC_EMPLOYEE INNER JOIN qryNewFte ON CCADMIN_CC_EMPLOYEE.CSR_AGENT_NO = qryNewFte.CSR_AGENT_NO
WHERE (((qryNewFte.NewFte)<>[CCADMIN_CC_EMPLOYEE].[FTE_RATE]) AND ((CCADMIN_CC_EMPLOYEE.EMPLOY_END_DATE) Is Null) AND ((CCADMIN_CC_EMPLOYEE.EMPLOY_END_REASON_CD) Is Null));

Thanks again for your help.
Note: I created a query selecting the Max record from Work History table and added it to the field +1, but obviously it appended the query with the same ID for all the records!

 

[scriverb]

I need you to help me here because you have all of the tables to test run this code and see if we make the table as expected. Here is some SQL to paste into a new query called qryMTTempRecords:
SELECT CCADMIN_CC_EMPLOYEE.CSR_AGENT_NO, Date() AS JOB_FUNCTION_CREATE_DATE, CCADMIN_CC_EMPLOYEE.CC_TEAM_MANAGER_ID, CCADMIN_CC_EMPLOYEE.LOCATION_CD, CCADMIN_CC_EMPLOYEE.CSR_MERIDIAN_ID, CCADMIN_CC_EMPLOYEE.SALES_REP_NO, CCADMIN_CC_EMPLOYEE.CSR_STATUS_CD, CCADMIN_CC_EMPLOYEE.TEMP_ASSIGN_ID, CCADMIN_CC_EMPLOYEE.EMPLOYER_CD, CCADMIN_CC_EMPLOYEE.EMPLOY_TYPE_CD, CCADMIN_CC_EMPLOYEE.JOB_FUNCTION_CD, CCADMIN_CC_EMPLOYEE.CC_GROUP_CD, Date() AS POSITION_EFFECTIVE_DATE, Date() AS LAST_UPD_DATE, CCADMIN_CC_EMPLOYEE.LAST_UPD_USERID, CCADMIN_CC_EMPLOYEE.JOB_FUNCTION_CREATE_USERID, CCADMIN_CC_EMPLOYEE.OPER_ID, qryNewFte.NewFte INTO tblTempRecords
FROM CCADMIN_CC_EMPLOYEE INNER JOIN qryNewFte ON CCADMIN_CC_EMPLOYEE.CSR_AGENT_NO = qryNewFte.CSR_AGENT_NO
WHERE (((qryNewFte.NewFte)<>[CCADMIN_CC_EMPLOYEE].[FTE_RATE]) AND ((CCADMIN_CC_EMPLOYEE.EMPLOY_END_DATE) Is Null) AND ((CCADMIN_CC_EMPLOYEE.EMPLOY_END_REASON_CD) Is Null));

After this executes take a look at the table and see if it looks correct. These are the records that we are going to use as input to an Append Query to add the records to your other table. Bob Scriver

 

[scriverb]

Here is the next bit of code that needs to be executed. We will put together the full VBA code in the end but just put this behind a button and on a new form and execute it. This will create a new field called RecCounter as an AutoNumber field and make it the Primary Index. This is the Incrementer field that will be used in the APPEND process to make your new numbers in sequential order:
Dim db as DAO.Database
Set db = CurrentDB
db.Execute &quot;ALTER TABLE tblTempRecords ADD COLUMN RecCounter AUTOINCREMENT&quot;
db.Execute &quot;CREATE INDEX RecCounter ON tblTempRecords (RecCounter) WITH PRIMARY;&quot; Bob Scriver

 

[scriverb]

Now create a new function in a Database Module:

Function LastRecID() as Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(&quot;CCADMIN_WORK_HISTORY&quot;, dbOpenDynaset)
rs.MoveLast
LastRecID = rs(&quot;WORK_HISTORY_ID&quot
rs.CLOSE
db.CLOSE
End Function

This will provide us with the last WORK_HISTORY_ID number which is the starting point for adding new records in the APPEND query. Bob Scriver

 

[scriverb]

Here is the final query SQL. It using the tblTempRecords as input with the newly created AutoNumber RecCounter along with the function LastRecID() to create the NewRecID number for each record. I think I have everything covered here in this query. I used some letter table designation to shorten it and make it a little easier to read. I hope you don't mind. Create a new APPEND query by copying and pasting this SQL into the SQL window of a new query. Call it whatever you want to:
INSERT INTO CCADMIN_WORK_HISTORY (WORK_HISTORY_ID, CSR_AGENT_NO, JOB_FUNCTION_CREATE_DATE, CC_TEAM_MANAGER_ID, LOCATION_CD, CSR_MERIDIAN_ID, SALES_REP_NO, CSR_STATUS_CD, TEMP_ASSIGN_ID, EMPLOYER_CD, EMPLOY_TYPE_CD, JOB_FUNCTION_CD, CC_GROUP_CD, POSITION_EFFECTIVE_DATE, LAST_UPD_DATE, LAST_UPD_USERID, JOB_FUNCTION_CREATE_USERID, OPER_ID, FTE_RATE )
SELECT (LastRecID() + A.RecCounter) as NewRecID, A.CSR_AGENT_NO, Date() AS JOB_FUNCTION_CREATE_DATE, A.CC_TEAM_MANAGER_ID, A.LOCATION_CD, A.CSR_MERIDIAN_ID, A.SALES_REP_NO, A.CSR_STATUS_CD, A.TEMP_ASSIGN_ID, A.EMPLOYER_CD, A.EMPLOY_TYPE_CD, A.JOB_FUNCTION_CD, A.CC_GROUP_CD, Date() AS POSITION_EFFECTIVE_DATE, Date() AS LAST_UPD_DATE, A.LAST_UPD_USERID, A.JOB_FUNCTION_CREATE_USERID, A.OPER_ID, Q.NewFte
FROM tblTempRecords as A INNER JOIN qryNewFte as Q ON A.CSR_AGENT_NO = Q.CSR_AGENT_NO
WHERE (((Q.NewFte)<>A.FTE_RATE]) AND ((A.EMPLOY_END_DATE) Is Null) AND ((A.EMPLOY_END_REASON_CD) Is Null));

Let me know how this all comes out. I have not been able to test any of this so there may be a few typos or brain gaps here someplace but I need you to point them out for me as you have the tables. It would be best to make backups of your tables before you do this.

Bob Scriver

 

[Atalla]

My God, all this to append an AutoNumber field. Well, guess what, everything works perfectly. I don't know how to thank you, I would never have thought of this solution, besides I am not that good with Access.
I made some modifications, like for instance, in the last query (Append), I removed the criterias since the MakeUp table already contain the records that needs to be changed.

In one of your notes that has the code to create the new field &quot;RecCounter&quot;, you mentioned that &quot;...we will put the full VBA code in the end....&quot;, is there any more code to be added? Because it works fine right now!

Anyhow, thank you very much again.

Atala

 

[scriverb]

No I had intended to give your a full list of code to execute the whole thing at once. But, it sounds like you have already done that.

Glad that it works for you. Yes, this was a difficult one. I have seen different ways of doing similar things but they always start out with a counter of 1 like you just need a sequential record counter in the new recordset. Yours with starting one number paste the existing number made it quite a challenge. I am pleased that it worked for you with just a few slight fixes.

Good luck with your database. Bob Scriver