I am burning the midnight oil tonight at work because many unexpected things have come up. I was handed a mdb today at 4 pm and told to transfer the tables to Access XP and create a VB interface. The user of the application said the database was out of memory and we were asked to "bring this back to life". Now, my dilemna.
Sadly, the creator of this mdb was very inexperienced (a MS Office user, and not even a mildly experienced Access user) and the main table in this MDB has 90 fields (yes, I know, my heads hurts too). Basically, she can not add more fields and the mdb is crashing. There are 1400 records in this, and I need to find a way to modify this table design without losing any data or the data integrity.
The basic format of the table as it is now:
Fields: First Name, Last, SS#, {other user fields}, class1, date1, class2, date2, class3, date3 (the classes and date fields go on forever from this point)
the field data within each of these class fields is either "P or F" signifying pass or fail and the date fields have a date in them.
I need to find a way to restructure this table into proper format, something like this:
FN LN SS# Class Date P/F
blah blah1 000-00-0000 class1 01/02 P
blah blah1 000-00-0000 class2 02/02 P
blah blah1 000-00-0000 class3 02/02 P
Mblah blah2 111-11-1111 class2 02/02 F
etc.
I have NO CLUE if this is possible..basically I need to take 20+ class fields and merge them into a general class field, all dates and merge them in a date field, etc. And of course, the data still have to be valid (the correct classes with the correlating user info.)
I have thought of all kinds of crazy potential INSERT or UPDATE table SQLs, but nothing sensible or logical is coming to me. I have done many different things in VB, but nothing like this. Is there any way to salvage this? If not, I have real dilemna because this data is needed and more classes and info needs to be added. I can not take a "from this point on approach" because new entries would be OK, but the old 1400 entries are still active and need to continuously modified... ACK!
I need some serious expert advice here. What can I do, is there anything? If not, it is going to be very tedious to create a brand new table by hand a re-create 1400 ss#'s with numerous classes and dates...we are probably talkiing about organizing 25,000 records by hand.
If any VB gurus think I have absolutely no chance of saving this table, please let me know so I stop banging my head on the desk. If there is no way to restucture this table, I am not sure what we are going to do . Please advise, even if your advice is, "there is nothing you can do". PLEASE HELP! thanks
Martin
Sadly, the creator of this mdb was very inexperienced (a MS Office user, and not even a mildly experienced Access user) and the main table in this MDB has 90 fields (yes, I know, my heads hurts too). Basically, she can not add more fields and the mdb is crashing. There are 1400 records in this, and I need to find a way to modify this table design without losing any data or the data integrity.
The basic format of the table as it is now:
Fields: First Name, Last, SS#, {other user fields}, class1, date1, class2, date2, class3, date3 (the classes and date fields go on forever from this point)
the field data within each of these class fields is either "P or F" signifying pass or fail and the date fields have a date in them.
I need to find a way to restructure this table into proper format, something like this:
FN LN SS# Class Date P/F
blah blah1 000-00-0000 class1 01/02 P
blah blah1 000-00-0000 class2 02/02 P
blah blah1 000-00-0000 class3 02/02 P
Mblah blah2 111-11-1111 class2 02/02 F
etc.
I have NO CLUE if this is possible..basically I need to take 20+ class fields and merge them into a general class field, all dates and merge them in a date field, etc. And of course, the data still have to be valid (the correct classes with the correlating user info.)
I have thought of all kinds of crazy potential INSERT or UPDATE table SQLs, but nothing sensible or logical is coming to me. I have done many different things in VB, but nothing like this. Is there any way to salvage this? If not, I have real dilemna because this data is needed and more classes and info needs to be added. I can not take a "from this point on approach" because new entries would be OK, but the old 1400 entries are still active and need to continuously modified... ACK!
I need some serious expert advice here. What can I do, is there anything? If not, it is going to be very tedious to create a brand new table by hand a re-create 1400 ss#'s with numerous classes and dates...we are probably talkiing about organizing 25,000 records by hand.
If any VB gurus think I have absolutely no chance of saving this table, please let me know so I stop banging my head on the desk. If there is no way to restucture this table, I am not sure what we are going to do . Please advise, even if your advice is, "there is nothing you can do". PLEASE HELP! thanks
Martin