Dave Sharpe
DaveSharpe2 at cox.net
Tue Apr 20 18:28:27 CDT 2004
Mark I've sent You an MDB that I hope helps ( I could do what I thought much easier that I could verbalize My thoughts ). Dave ----- Original Message ----- From: "Mitsules, Mark S. (Newport News)" <Mark.Mitsules at ngc.com> To: "'Access Developers discussion and problem solving'" <accessd at databaseadvisors.com> Sent: Tuesday, April 20, 2004 6:45 PM Subject: RE: [AccessD] Create Junction Table From Existing Data Sure...the data started out as an Excel flat file (no normalization). They then put it into an Access Db (same format). I now have the task of cleaning it up. The data consists of checklist items, and the checklists to which they pertain. In my example, (ID--CODE--SA--SAR--SRD) "code" refers to the checklist item description, and the last 3 fields are examples of checklist types (which should have their own table). I have already created the normalized tables tblChecklistItems and tblChecklistTypes. What I need assistance with, is how to create the junction table of this many to many relationship. Since this is the first time I've had to normalize an existing database, a little "hand-holding" would be appreciated;) Mark -----Original Message----- From: William Hindman [mailto:wdhindman at bellsouth.net] Sent: Tuesday, April 20, 2004 6:03 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Create Junction Table From Existing Data ...you've lost me Mark ...I thought you were trying to join data from two tables ...your latest post looks as if you're trying to pivot the data instead ...can you clarify it a bit? William Hindman "Always code as if the person who is maintaining or testing your code is a violent psychopath who knows where you live." William Silverstein ----- Original Message ----- From: "Mitsules, Mark S. (Newport News)" <Mark.Mitsules at ngc.com> To: "'Access Developers discussion and problem solving'" <accessd at databaseadvisors.com> Sent: Tuesday, April 20, 2004 5:08 PM Subject: RE: [AccessD] Create Junction Table From Existing Data > I did just that to create the primary tables. But, since I've never had to > do this on such a grand scale, I need assistance in generating the junction > table. Here is pseudo data for clarification. I've placed dashes to > maintain alignment. > > ID--CODE--SA--SAR--SRD > 1---EA1---SA--SAR--SRD > 2---EEY1--SA > 3---EEY2--SA--SAR--SRD > > ...from the data above, I need: > PK--CODE--TYPE > 1---EA1---SA > 2---EA1---SAR > 3---EA1---SRD > 4---EEY1--SA > 5---EEY2--SA > 6---EEY2--SAR > 7---EEY2--SRD > > Any suggestions? > > Mark > > > > -----Original Message----- > From: William Hindman [mailto:wdhindman at bellsouth.net] > Sent: Tuesday, April 20, 2004 4:42 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Create Junction Table From Existing Data > > > ...I know of no simple way to do this ...my approach has always been to add > an ANPK to the original table and then to use that as the control to > populate the derivative tables through a series of queries ...but the > circumstances change so much from mdb to mdb that I've never been able to > write code that ever worked for more than one situation. > > William Hindman > "Always code as if the person who is maintaining or testing your code is > a violent psychopath who knows where you live." William Silverstein > > > ----- Original Message ----- > From: "Mitsules, Mark S. (Newport News)" <Mark.Mitsules at ngc.com> > To: "'[AccessD]'" <AccessD at databaseadvisors.com> > Sent: Tuesday, April 20, 2004 4:23 PM > Subject: [AccessD] Create Junction Table From Existing Data > > > > Group, > > > > Is there a method to create a junction table from a non-normalized table? > I > > inherited a table that I now need to normalize into two primary tables and > a > > joining junction table. The primary tables were easy enough to create, > but > > how can I create the junction table? > > > > I want to take a records such as: > > Fld1 Fld2 Fld3 Fld4 Fld5 <=Fields > > Dat1 Fld2 Fld4 Fld5 <=Record > > Dat2 Fld3 Fld4 <=Record > > > > And turn it into: > > Fld1 FldID <=Fields > > Dat1 Fld2 <=Record > > Dat1 Fld4 <=Record > > Dat1 Fld5 <=Record > > Dat2 Fld3 <=Record > > Dat2 Fld4 <=Record > > > > > > Mark > > -- > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com