is this possible? Modifying a table's structure

[mgkSHA]

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

 

[quovuz]

hi! could you type the list of fields of all tables used with about 5 sample data each? my colleague here has an idea and would want to help out. you may send it to my email address at quovuz@yahoo.com and we'll get on it right away.

 

[mgkSHA]

hello: thanks for your correspondence. I no longer have the table in front of me, I am home, but here is an idea of what is going on. I have about 89 fields..the first 10 are fine, based employee info stuff. Then, the problem starts with numerous class and date fields. There are only two data types, text and date. I have typed in five fields as an example..this gives you an exact idea of what's going on, just on a smaller scale, same concept, but for 90 fields and 1400 records..see below

SS# FN class1 Date class2 Date2 class3 Date3
111-11-1111 Jonny P 01/02 P 02/02 P 03/02
222-22-2222 Brenda P 02/02 P 03/02 P 04/02
333-33-3333 Tony P 01/02 P 02/02 P 03/02
121-12-1112 Mark P 03/02 P 04/02 F 05/02
000-01-0101 Sara P 01/02 F 04/02 F 06/02

I need, without loss of data, a new, correctly structured table, as in

SS# FN Class Date Pass/Fail
111-11-1111 Jonny class1 01/02 P
111-11-1111 Jonny class2 02/02 P
111-11-1111 Jonny class3 03/02 P
222-22-2222 Brenda class1 02/02 P
etc...you get the idea.

You can just plug this into a simple Access table and you have a representation of what I am stuck with. PLEASE help if you can, I don't know what to do.

Martin K

 

[vb5prgrmr]

Access is a relational database and the creator of this database seems to have been familiar more with excel than access. The easiest thing to do is to break out the personal info into a "Persons" table. This table will contain the names, ssn, etc. etc....

[tt]
Personal_Table
PersonID AutoNumber
SSN Text 9
FirstName Text 17
LastName Text 24
.....
[/tt]

The Second table will be the child table of the relation. This will hold the class information.

[tt]
Class_Table
ClassID AutoNumber
PersonID Number 1 to many relation Foreign Key
Class Text 20
ClassDate Date/Time
PF Text 1
[/tt]

This will allow you to add classes forever (limited by DBMS) for each user.

Now that you have these designed here is your fun part. You will need to figure out a way to move the data from the one table to the two tables and still keep referential integrity of the data itself.

The easiest way to do this is create a temporary program that will read in the single table and insert as necessary into the other two tables based on each record. Meaning you read in the first record and update the person. You then run a query to return the PersonID and use that for the foreign key relationship as you insert into the class table with the rest of the data from the other class fields. Then move to the next record from the origional table, and so on, and so on.

I hope this helps, Good Luck

 

[mgkSHA]

Your suggestion of a personal table and say, a class table is valid. However, let me re-iterate that there are 90 fields in this table. There are over 70 class and date fields which need to be MERGED into one of each. If I break this into two tables personal and class, the personal table is fine, about 10 fields, then I am 70+ field will seperate class and date fields...that still doesn't solve my problem. Instead of this over and over:

class1 date1 class2 date2
P 01/02 F 02/02....continues on and one

I need

Class Date Pass/Fail
class1 01/02 P
class2 02/02 F

I appreciate your idea, but it is not an answer I am looking for. I really don't need a key here, with proper design, this table has 10 fields. I just need some way, if possible to combine all dates and class into the appropriate fields without losing data. I am starting to believe this is impossible.

 

[CmPaiva]

Hi,

I think I have a solution for you. If the field name (the repeated ones, follow a rule, create a loop, accessing the fields this way:
do
for n=1 to xxx
fldname=format(n,"Class0&quot
var=rst.Fields(Fldname)

...

dest rst= var
next
loop

You must create a format string that ressemblys the field name.

I hope this helps you
Carlos Paiva

 

[CajunCenturion]

The first thing I would do is create a set of normalized tables which can contain all of the information in the existing table. Develop (on paper) a mappming from the old table columns to the new table(s) columns.

Then write a VB program which reads the old records, one at a time. For each record, build all of the necessary INSERT statements to place the data in the new table formats, based on the mapping that you have determind.

Now go forward.

That being said, you may want to get the system up and running with the new database and frontend before doing the conversion. Two reasons -- one, you will be able to validate your design which may lead to changes in your mapping, and two - your user community will not have to wait on the conversion to being using the system. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MichaelRed]

mgkSHA,

The process described to you are valid and usual approaches to the problem you have described. While I sense a degree of ugrgency in your postings, I also see a need for you to review and consider what advice you have been given. There are many programatic soloutions to the generic problem you have posted, however they will mostly involve the techniques already presented.

Perhaps, you feel the issue is more than you can deal with in your present circumstances. If this is so, you should approach your supervisor and discuss your on-going responsabilities and the reuquirements of this additional project ... implicitly suggesting that a (?temporary ?) contractor be hired to do the conversion routine and test it on the existing initial data set.

This could accomplish several useful tasks:

A[Tab]Actually accomplishes the normalization.

B[Tab]Relieves you of the immediate 'overload'.

C[Tab]Provides a 'tutorial' guide for the conversion which you can use.

D[Tab]Permits the continuing use f and addition to the current db/table structure while the conversion process is in development.

D[Tab]Provides your supervisor with a soloution which does not impact the day-today function of the organization.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[mgkSHA]

thanks everyone: all the ideas posted here have given me an idea of how to tackle this. I just needed some ideas to gets the wheeels grinding, I can do this, thanks.

MichaelRed my urgency came from the fact that this project was given to me last night with a tight deadline, very unexpected. I work hard and I try to do my best, my urgency stems from this hunger to succeed.

I appreciated everyone's advice, but your advice had nothing to do with fixing my problem, and I do not need professional advice or a list of reasons why we need consultants. But thanks anyway, I'm glad you could use my post as an attempt to sell some concept.

Again, thanks for the help everyone

 

[CmPaiva]

mgkSHA

Sorry, but i hope this will help, if what you neet is to copy contents of source table, to a known destination , newly formated table:

Set mSrcRs = DB.OpenRecordset(&quot;SELECT * FROM <yoursourcetable>&quot
Set mDestRs = DB.OpenRecordset(&quot;SELECT * FROM <yourdesttable>&quot
Do Until mSrcRs.EOF
For n = 1 To 50 '<LastClassNr>
mDestRs.AddNew
mDestRs!FN = mSrcRs!FN
'... all desired fields assignments
mDestRs!SS = mSrcRs!SS

mDestRs![Pass/Fail] = mSrcRs.Fields(Format(n, &quot;\C\l\a\s\s0&quot)
mDestRs!Date = mSrcRs.Fields(Format(n, &quot;\D\a\t\e0&quot)
mDestRs!Class = Format(n, &quot;\C\l\a\s\s0&quot
mDestRs.Update
Next
mSrcRs.MoveNext
Loop

Give a chance to this code, (with desired changes), and tell me if it solved your problem.
I realy hope so.

Carlos Paiva

 

[Foada]

I set this up from the table data that you gave and it does what your tables suggest.

Private Sub Command1_Click()
'Requires a reference to ADO Ext 2.xx for DLL and Security and MS ActiveX Data Objects 2.xx
Dim cat As New ADOX.Catalog
Dim con As New ADODB.Connection
Dim tblNew As New Table
Dim tblOld As Table
Dim rs As Recordset
Dim col As Column
Dim iC As Long
Dim iX As Long
Dim sConnection As String
Dim sSSN As String
Dim sFN As String
Dim sClass As String
Dim sDate As String
Dim sSQL As String


sConnection = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & _
&quot;G:\Win2K\Projects\Access\TransferTables.mdb&quot; 'The Path to the database
cat.ActiveConnection = sConnection

'Create the New Table
With tblNew
.Name = &quot;newTable&quot;
.Columns.Append &quot;SSN&quot;, adVarWChar, 12
.Columns.Append &quot;FN&quot;, adVarWChar, 15
.Columns.Append &quot;Class&quot;, adVarWChar, 15
.Columns.Append &quot;ClassDate&quot;, adVarWChar, 15
.Columns.Append &quot;PassFail&quot;, adVarWChar, 15
End With
cat.Tables.Append tblNew

DoEvents
'Count the number of 'Class & Date' fields assuming that each class field has a matching date field
Set tblOld = cat.Tables(&quot;OldTable&quot
For Each col In tblOld.Columns
If Left$(col.Name, 5) = &quot;Class&quot; Then
iC = iC + 1 'Counts the number of &quot;Class&quot; Fields in the old table
End If
Next
'Transfer the data to a new table
con.ConnectionString = sConnection
con.Open
Set rs = con.Execute(&quot;Select * From OldTable Order By SSN Asc&quot
Do Until rs.EOF
sSSN = rs(&quot;SSN&quot
sFN = rs(&quot;FN&quot
For iX = 1 To iC
sClass = &quot;Class&quot; & CStr(iX)
sDate = &quot;Date&quot; & CStr(iX)
sSQL = &quot;Insert Into NewTable(SSN, FN, Class, ClassDate, PassFail) Values ('&quot; & _
sSSN & &quot;', '&quot; & sFN & &quot;', '&quot; & sClass & &quot;', '&quot; & rs(sDate) & &quot;', '&quot; & rs(sClass) & &quot;')&quot;
con.Execute sSQL
Next
rs.MoveNext
Loop
'Close the connections
rs.Close
con.Close
'Clean up the objects
Set col = Nothing
Set tblNew = Nothing
Set cat = Nothing
Set rs = Nothing
Set con = Nothing
End Sub

Let me know if this helps. If you choose to battle wits with the witless be prepared to lose.

 

[vb5prgrmr]

Well I can see you missed the point of &quot;Relational Database&quot; that CajunCenturion and I tried to impart upon you. So lets try to go over it again. You said that the personal table would be about 10 fields and then you would have...then I am 70+ field will seperate class and date fields...that still doesn't solve my problem. Instead of this over and over:. We are not talking about having the class,date,PF repeated like...
[tt]
class,date,PF,class,date,PF,class,date,PF,....
[/tt]
We/I am talking about 1 Class Field, 1 Date Field, and 1 P/F Field in the second child table along with an auto number field and a foreign key field that links back to the personal table's unique ID field (which in my example is an auto number). So if you looked at the table in datasheet view it would look something like...
[tt]
ClassID PersonalID Class_Name DateOfTest PF
1 1 C1 12/12/02 P
2 1 C2 12/12/02 F
3 1 C3 12/12/02 P
4 2 C1 12/12/02 F
5 2 C2 12/12/02 P
6 3 C4 12/12/02 F
....
[/TT]
After the data has been moved.
I hope this makes better sense...

 

[MichaelRed]

CmPaiva, foada, vb5prgrmr, You, as they say &quot; ... lead a horse to water ... &quot;

... if it is still alive, there are (suggested) techniques to encourage drinking, but beating the dead ones doesn't work.

Strange reminicences re the need for an eight day camel ... use bricks ...


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MichaelRed]

CmPaiva, foada, vb5prgrmr, You, as they say &quot; ... lead a horse to water ... &quot;

... if it is still alive, there are (suggested) techniques to encourage drinking, but beating the dead ones doesn't work.

Strange reminicences re the need for an eight day camel ... use bricks ...


MichaelRed
m.red@att.net

Searching for employment in all the wrong places