Autonumber problem with a table

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
Who is Participating?
tabishConnect With a Mentor Commented:
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.
If you can provide the both tables it will help. I'm a bit unclear what do you want.
bobby6055Author Commented:
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.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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?
bobby6055Author Commented:
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.
bobby6055Author Commented:
Good job and your technique is easy to understand.

bobby6055Author Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.