asked on

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.

QUESTION:
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

tabish

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

bobby6055

ASKER

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.
Sample-DB.mdb

tabish

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

tblTableToFix
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?

bobby6055

ASKER

No.....
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)
(A).
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.

(2).
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.
Sample-DB--3.mdb

ASKER CERTIFIED SOLUTION

tabish

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bobby6055

ASKER

tabish:
Good job and your technique is easy to understand.

Thanks

bobby6055

ASKER

tabish:
I have one more challenging question opened at:

https://www.experts-exchange.com/questions/26910343/Question-on-Grouping-in-Access-table.html

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