"Kelvin" <ebug@hotmail.com> wrote in message
news:191e0546.0402220116.50bfd29f@posting.google.com...
> Hi Allan,
>
> Thanks for your help and tips.
> Here is the real table scheme and the data of what it stands for.
>
> Customers Table
> ID,Name,studentID,staffID,providerID,schoolID
> 1,ABC High School,(NULL),(NULL),(NULL),(NULL)
> 2,Kelvin,(NULL),(NULL),(NULL),1
> 3,Linda,2,(NULL),(NULL),(NULL),1
>
> above relationship shows that:
> ID=1 is a school,
> ID=2 is a student that is in ABC High School
> ID=3 is a Staff that has Kelvin as student under her and himself works
> under ABC High school (working under this school)
> as you can see this is how it goes in database, I need to transfer all
> this into SQL (it was in access db) but the new design i as following:
>
> Staff table that contains staff info with ID,StaffName
> Providers table that contains provider info with ID,providerName
> Students table that contains students info with ID,studentName
>
> so to link this up, I created a reference table like this:
> FromID, ToID
> So I can linke student with school with its own id and recorded in a
> reference table.. if I want to delete them.. I just delete the record
> in reference table instead of deleting students or staff table.
>
> I hope I answered your question right. and hope there is a solution
> for this kind of table scheme.
>
> Kelvin
>
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:<#Uy394#9DHA.1392@tk2msftngp13.phx.gbl>...
> > DTS can certainly do this yes.
> >
> > If refID is your SourceSystem ID then you have duplicated values no ?
RefID
> > = 1
> >
> > You can work the logic into an Active SCript transform. You can save
the
> > logic in a package.
> >
> > If you show us Real source and real destination rows we may be able to
offer
> > more concrete samples.
> >
> > --
> >
> > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > www.allisonmitchell.com - Expert SQL Server Consultancy.
> > www.SQLDTS.com - The site for all your DTS needs.
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> > "Kelvin" <ebug@hotmail.com> wrote in message
> > news:191e0546.0402201140.432d8fd9@posting.google.com...
> > > I have an Access DB and recently designed a SQL 2000 tables scheme
> > > that is some how similar to Access but added more fields and more
> > > tables and normalized tables to more tables.
> > >
> > > I need to use DTS (if this is the right tool to use, if not, please
> > > suggest) to transform all the data ONLY from Access to SQL.
> > >
> > > Is there any way that I can customize the way it transform and I need
> > > to save these procedures in future use (just in case if I want to do
> > > all these transforming again in the future).
> > >
> > > The weird thing about the table design in sql is that in Customers
> > > table, the primary ID will be referenced to new record. for example.
> > >
> > > Customers table:
> > > ID, refID, firstName, lastName, dayPhone
> > > 1,0,aaa,aaa,123-456-7890
> > > 2,1,bbb,bbb,123-123-1234
> > > 3,1,ccc,ccc,123-123-4321
> > > 4,2,ddd,ddd,123-123-4320
> > >
> > > you see how refID is actually from its previous ID and this is the way
> > > that my previous guy designed (not good I know of, but has no
> > > permission to change the scheme ).. so I need a very huge favor and
> > > help on this thing to get my old app completed transfered to new sql
> > > and without breaking the relationship of ID, refID.... Thanks.. you
> > > can reach me at msn IM ebug@hotmail.com or email to this account as
> > > well... Again.. I am very appreciated for any of your help. Thank
> > > you.