Autonumber problem with a table

Posted on 2011-03-24
Last Modified: 2012-05-11
Table A is a table of address for student activities. tblStudents as an autonumber that is recorded in TableA identifying
students activity. TableA has data about the Student activities covering from 01/01/05 through July 30, 2010.

In August 1st 2010 I goofed by recreating tblStudents (Autonumber). Since August TableA StudentID is no longer correct in TableA. However, I still have a backup of TableA covering up to July 30th 2010.

How can I use the old student list in tblStudents in my old backup tableA of July 30, 2010 to correct the newly created StudentID records in Table A covering a period of August 1st 2010 up till March 24th 2011?

TableA.StudentID is the only misnumbered data in that table. I am confused as to proceed.

I can provide a sample table of TableA and both the old and the misnumbered new table of tblStudents if needed
Question by:bobby6055
  • 4
  • 3

Expert Comment

ID: 35212299
If you can provide the both tables it will help. I'm a bit unclear what do you want.

Author Comment

ID: 35212593
As requested, I have provided you with the following data with the following explanation. Please download the attached sample db.

(a). tblTableToFix ----> Is the table I want to fix. Here I want tblGoodStudentList to be used
                                         to replace tblGoodData.StudentID.
(b). tblGoodData --------> Without any doubt, this table represent my July 30 2010 good table where tblGooStudentList was utilized to populate tblGoodData.StudentID (per my EE posting)
(Note that tblGooStudentList - autonumber is not smooth...some numbers do SKIP in the autonumbering...This is what I ACCEPT for now as my best table of Student List.

(c). tblGoodstudentList - is the BEST studentList that I need here to fix "tblTableToFix.StudentID"
(WHICH was populated by the tblRenumberedBadStudentList in error.)

Take note that this tblRenumberedBadStudentList has it's autonumbering redone. THIS IS WRONG.
Solution Sought:
Update tblTableToFix.StudentID with matching tblGoodStudentList.StudentID WHERE CourseNo and SubjectNo are MATCHES.
All I want is a list of good student to populate tblTableToFix.StudentID.

Expert Comment

ID: 35213034
Lets work with the following example:
MainID      CourseNo      SubjectNo      HouseNo      StudentID      Appdate      dateCreated      SerialNo
34      5141      810      2100      7      28/07/2009      8/12/2009      

MainID      CourseNo      SubjectNo      HouseNo      StudentID      Appdate      dateCreated      SerialNo
34      5141      810      2100      6      28/07/2009      8/12/2009      

You want tblTableToFix.StudentID (6) be replaced with tblGoodData.StudentID (7)?

moreover, you mentioned to match CourseNo and SubjectNo but these to columns wont return the unique row. We will need to use the MainID in both tables. How reliable would that be?
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Author Comment

ID: 35214100
In other to make things clearer, I am sending you this new sample db (attached).
In the sample you will see only one studenlist table now called tblGoodStudentList but it is actuall renamed from  tblRenumberedBadStudentList.

It's is the one I that I have the autonumber redone.. and it's causing the trouble.

Immediately below is my "new" tblGoodStudenList begining from all records created after July 30th 2010 for example. take note that StudentID is not the same (see the next table below it)
StudentID    LastName        FirstName
1                     Ian               Flemmings
2                     Richards       Teresa
3                     Jackson        Rosemary
4                     Luke             Stanley
5                     Palmer          Irene
6                    Jake               Leonard
7                    Williams         Harry
8                    Jacob             Martin
9                    Thomas         Larry
10                  Freestar        David
11                  Stones          Willis


Below is my "old" tblGoodStudentList (created for all records on and before July 2010 in this sample.

StudentID       LastName     FirstName
1                     Richards       Teresa
3                     Luke             Stanley
4                     Jackson        RoseMary
6                     Palmer         Irene
7                     Jake             Leonard
9                     Williams       Harry
10                   Jacobs         Martin
11                   Ian              Flemings

Now since the StudentID has changed in the two Student tables above, I will like to still use one good table list of students to fix my problem.
I want to combine the two data tables and then fix the studentID fields correctly so that there is no mismatch of StudentID accross the combined tables
(see below for further explanation)

The problem after recreating the autonumber in the first table above, I added new ones but in my database, here is what I want to do:
(1). "Delete" the StudentID for both tblGooddata and tblTabletoFix and combine both tblGooddata with tblTableToFix (lets call it tlbCombinedData).
(2). Compare the two tables of "tblGoodStudenList with tblRenumberedBadStudentList" (using just the LastName and FirstName - since StudentID for both tables are no longer the same

(3). Use the "new or the old" StudentList to fix the combined table "tlbCombinedData" using just the "LastName and FirstName" fields instead of the bad "StudentID"

(4). You can create a new "NewStudentID" in tlbCombinedData (if you like) and update tlbCombinedData.NewStudentID such that it now represent the correct StudentID FIX so that
       all data across the  tlbCombinedData represent correct data for each student.

I hope this explanation is now clearer.

Accepted Solution

tabish earned 500 total points
ID: 35222497
I'm assuming the tblDataToFix represent the data after the july and the tblGoodData represent the data before.

In this situation I believe you should fix the data before the first of july not the after. The reason being is the tblGoodStudentList_Renumbered has more (additional) students and those students also exist in tblDataTofix. So in “tblDataTofix” if we are to revert to old studentids using the oldstudentlist we'll not find the ids for new students who exist in tblGoodStudentList_Renumbered.

Please see the attached db Sample-DB--3Fixed.mdb. In it, I have updated the studentIDs of tblGoodData.

Here are the steps:
1 – Add another field “StudentIDFixed” in the tblGoodData.
2 – Run Update Query “Qry_OldToNewStudentIDMapping”. It will map the Old studentId to new and update the newly created StudentIDFixed field. I could have updated the studentid directly but updating the new field will give you the ability to compare the data.
3 – Run “Qry_OldToNewStudentIDMapping_Test” to test the data.

Let’s now take a look at an example.

StudentID      LastName      FirstName
7      Jake      Leonard
Id 7 was used to create data in tblGoodData. And there are 13 rows.

StudentID      LastName      FirstName
6      Jake      Leonard
Id 6 has been used in tblDataToFix and there are 10 rows.

Fixing the tblGoodData will change the StudentID 7 to 6. Since Studentid in the tblDataToFix is already 6, together we should have 23 rows when we combine the both the tables (see union query “Qry_CombinedData”). You should now use “tblGoodStudentList_Renumbered” as a lookup table.

Following the same logic I have also fixed the “tblDataToFix” (see “Sample-DB--3NotFixed.mdb”). You should see the data do not match when you run the test query.

Hope that is what you wanted. If not it will give you at least an idea how you can do it.

Author Comment

ID: 35222794
Good job and your technique is easy to understand.


Author Comment

ID: 35222943
I have one more challenging question opened at:

If you need sample db to resolve the sql; please feel free to request it at the posting and I'll upload one.

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question