I'll try to be very conscise.
We have a school system db- each school has their own db4 system. (20+ schools)
Currently each student is assigned an ID (studentid) that will stay constant from grades k-12/even if they change schools.
They want a 2nd id(idnew) for each student (it will coincide with a new state id). The db4 program gives an initial id, but can not do a second autonumber type field so I've been asked to make this id in Access and update the db4 database.
So this is my initial game plan:
1. Link to each school table
2. Make an access table that has the idnew (autonumber field)and the studentid
3. Dump all studentid into new table- making sure that there are no duplicates.
4. Update each schools table- if idnew is null on school table =idnew from Access table.
This would mean I’d have to make a 50 some queries then open them through a macro. (For number 3 I’d have to pull from each school’s table 20+ schools and number 4 I’d have to go update these same school tables).
Is there a way for me to consolidate this or will I need to make lots of queries but just change the school table? Should it be a bunch of queries? Should I have put this in the vb part of the forums and try from there?
Thanks,
J
We have a school system db- each school has their own db4 system. (20+ schools)
Currently each student is assigned an ID (studentid) that will stay constant from grades k-12/even if they change schools.
They want a 2nd id(idnew) for each student (it will coincide with a new state id). The db4 program gives an initial id, but can not do a second autonumber type field so I've been asked to make this id in Access and update the db4 database.
So this is my initial game plan:
1. Link to each school table
2. Make an access table that has the idnew (autonumber field)and the studentid
3. Dump all studentid into new table- making sure that there are no duplicates.
4. Update each schools table- if idnew is null on school table =idnew from Access table.
This would mean I’d have to make a 50 some queries then open them through a macro. (For number 3 I’d have to pull from each school’s table 20+ schools and number 4 I’d have to go update these same school tables).
Is there a way for me to consolidate this or will I need to make lots of queries but just change the school table? Should it be a bunch of queries? Should I have put this in the vb part of the forums and try from there?
Thanks,
J