Solved

Autonumber problem with a table

Posted on 2011-03-24
7
231 Views
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.

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
0
Comment
Question by:bobby6055
  • 4
  • 3
7 Comments
 
LVL 4

Expert Comment

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

Author Comment

by:bobby6055
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.
Sample-DB.mdb
0
 
LVL 4

Expert Comment

by:tabish
ID: 35213034
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?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 3

Author Comment

by:bobby6055
ID: 35214100
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
0
 
LVL 4

Accepted Solution

by:
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.

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

tblGoodStudentList_Renumbered:
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.
 Sample-DB--3Fixed.mdb
Sample-DB--3NotFixed.mdb
0
 
LVL 3

Author Comment

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

Thanks
0
 
LVL 3

Author Comment

by:bobby6055
ID: 35222943
tabish:
I have one more challenging question opened at:

http://www.experts-exchange.com/Microsoft/Applications/Q_26910343.html

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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now