If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help - Convert Access flat file to normalized tables
Folks,
Hi. I have an Access file that consists of a single flat file table. I'd like to create a series of tables that are normalized. I would like to do this manually, rather than using the ToolsAnalyzeTables approach, which will create look-up fields. My reading indicates that look-up fields (vs. look-up tables) are the Devil's work. I have read and studied a good tutorial on the subject (the URL is noted below). http://pubs.logicalexpressions.com/P...cle.asp?ID=182 However, I still can't quite get things right, using the approach described in the tutorial. Is there anyone who would be willing to correspond with me via email to help? If so, I could be more explicit about the problems I've having. I'm afraid that trying to do this in the context of the mailing list will try the patience of most subscribers. TIA, Steve |
#2
|
|||
|
|||
Help - Convert Access flat file to normalized tables
Steve,
Most people here will be happy to answer any specific question you have, and most of the broad ones. Getting private one-on-one help may turn into a paid arrangement. Generally speaking, take a look at the information you have, and break it into logical entities. That is your starting point for your new table design. -- Kevin Hill President 3NF Consulting www.3nf-inc.com/NewsGroups.htm www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs. "Steve Newton" wrote in message ... Folks, Hi. I have an Access file that consists of a single flat file table. I'd like to create a series of tables that are normalized. I would like to do this manually, rather than using the ToolsAnalyzeTables approach, which will create look-up fields. My reading indicates that look-up fields (vs. look-up tables) are the Devil's work. I have read and studied a good tutorial on the subject (the URL is noted below). http://pubs.logicalexpressions.com/P...cle.asp?ID=182 However, I still can't quite get things right, using the approach described in the tutorial. Is there anyone who would be willing to correspond with me via email to help? If so, I could be more explicit about the problems I've having. I'm afraid that trying to do this in the context of the mailing list will try the patience of most subscribers. TIA, Steve |
#3
|
|||
|
|||
Help - Convert Access flat file to normalized tables
On Mon, 28 Jun 2004 13:16:49 -0700, Steve Newton
wrote: Folks, Hi. I have an Access file that consists of a single flat file table. I'd like to create a series of tables that are normalized. I would like to do this manually, rather than using the ToolsAnalyzeTables approach, which will create look-up fields. My reading indicates that look-up fields (vs. look-up tables) are the Devil's work. That they are... g I have read and studied a good tutorial on the subject (the URL is noted below). http://pubs.logicalexpressions.com/P...cle.asp?ID=182 However, I still can't quite get things right, using the approach described in the tutorial. Is there anyone who would be willing to correspond with me via email to help? If so, I could be more explicit about the problems I've having. I'm afraid that trying to do this in the context of the mailing list will try the patience of most subscribers. Well, without going to the tutorial, I'll just toss out the approach I use when this operation is needed. - Use logic (aided by a #2 pencil, a block eraser, and a pad of paper; or a whiteboard with a good eraser) to design a properly normalized set of tables, based on the real-world logical structure of the data. Reference to the existing table at this point is *only* for the purpose of identifying what information is needed. - Create empty tables with the appropriate linking fields and relationships. - Run as many Append queries as necessary to migrate the data from the wide-flat table into the normalized tables. These queries will make free use of the UNIQUE VALUES query property to extract only one instance of sets of duplicated fields, and may involve joining the first-created table to the wide-flat table in order to pick up the value of a new primary key field for use in a foreign key. By all means post back with more details. As Kevin says, most of the volunteers here would prefer to keep volunteer work to the newsgroup; private EMail support is usually available to paying customers. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#4
|
|||
|
|||
Help - Convert Access flat file to normalized tables
On Mon, 28 Jun 2004 16:21:15 -0600, John Vinson
wrote: - Use logic (aided by a #2 pencil, a block eraser, and a pad of paper; or a whiteboard with a good eraser) to design a properly normalized set of tables, based on the real-world logical structure of the data. Reference to the existing table at this point is *only* for the purpose of identifying what information is needed. - Create empty tables with the appropriate linking fields and relationships. John and Kevin, Thanks. I have created the design for the normalized tables on paper. So far, so good. I have also created the empty tables with the appropriate linking fields and relationships. - Run as many Append queries as necessary to migrate the data from the wide-flat table into the normalized tables. These queries will make free use of the UNIQUE VALUES query property to extract only one instance of sets of duplicated fields, and may involve joining the first-created table to the wide-flat table in order to pick up the value of a new primary key field for use in a foreign key. In the tutorial, I was advised to begin creating my tables with make-table queries (rather than beginning with empty tables). I suspect that some of my problems began here. In any event, my two questions at this point a 1. When I created the empty tables with appropriate linking fields and relationships, should I have enforced referential integrity, or just established the relationships without enforcing referential integrity? 2. I know that I need to get from my wide flat-file table to the tables with the relationships. I understand the unique-values property, which was explained well in the tutorial I read, Can you, however, explain a little more about the phrase "...and may involve joining the first-created table to the wide-flat table in order to pick up the value of a new primary key field for use in a foreign key"? I think this is where I am messing up somehow. When I created the empty tables, I established the PKs and FKs. In doing this, should I have somehow accounted for specific fields in the wide flat-file table? Many thanks, Steve |
#5
|
|||
|
|||
Help - Convert Access flat file to normalized tables
On Mon, 28 Jun 2004 16:54:21 -0700, Steve Newton
wrote: In the tutorial, I was advised to begin creating my tables with make-table queries (rather than beginning with empty tables). I suspect that some of my problems began here. In any event, my two questions at this point a That can sometimes work, but IME you have more control over field types and field sizes if you create empty tables using the user interface. Either way will work. 1. When I created the empty tables with appropriate linking fields and relationships, should I have enforced referential integrity, or just established the relationships without enforcing referential integrity? A relationship without referential integrity is just a suggestion; it provides NO protection against entering invalid data. By all means establish RI. 2. I know that I need to get from my wide flat-file table to the tables with the relationships. I understand the unique-values property, which was explained well in the tutorial I read, Can you, however, explain a little more about the phrase "...and may involve joining the first-created table to the wide-flat table in order to pick up the value of a new primary key field for use in a foreign key"? I think this is where I am messing up somehow. When I created the empty tables, I established the PKs and FKs. In doing this, should I have somehow accounted for specific fields in the wide flat-file table? Not knowing anything about your data I can only speak in general terms; but let's consider an example. Suppose you had a wide-flat sales database with fields CustomerName, SaleDate, Item, Quantity, Cost; and you want to create three tables, Customers, Items, and Sales. You could create a Customers table with an autonumber ID, and run a Unique Values query selecting CustomerName and appending into this table, getting one record for each customer (or, more precisely, one record for each spelling variant of each customer). Do the same for Items. The Sales table would have foreign key fields CustomerID (long integer, linking to the Customers table autonumber ID) and ItemID (linked to Items autonumber ItemID) - but your wide-flat table doesn't have these fields! So you would create a Query joining WideFlat to Customers *by customer name*, to pick up the ID, and likewise joining it to Items *by Item* to pick up *that* ID. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#6
|
|||
|
|||
Help - Convert Access flat file to normalized tables
You probably don't need this advice, but make sure you have backups! Also,
you might consider taking the time to make some notes as you work your way through the process, or even save a new backup after every major step in the process. I did this kind of thing once, and found myself, after discovering I'd made some mistake or other along the way, having to redo the *whole* process from scratch. And I had forgotten some of the details along the way. Eventually, I learned to make notes about just what I was doing at each step, and I saved a copy of the database after each step, so that if I discovered a mistake, I just had to go back one or two backups, rather than start over. Naturally, if your database is huge this might not be feasible... HTH Fred Boer P.S. I'll leave the experts to help you with the actual work! "Steve Newton" wrote in message ... On Mon, 28 Jun 2004 16:21:15 -0600, John Vinson wrote: - Use logic (aided by a #2 pencil, a block eraser, and a pad of paper; or a whiteboard with a good eraser) to design a properly normalized set of tables, based on the real-world logical structure of the data. Reference to the existing table at this point is *only* for the purpose of identifying what information is needed. - Create empty tables with the appropriate linking fields and relationships. John and Kevin, Thanks. I have created the design for the normalized tables on paper. So far, so good. I have also created the empty tables with the appropriate linking fields and relationships. - Run as many Append queries as necessary to migrate the data from the wide-flat table into the normalized tables. These queries will make free use of the UNIQUE VALUES query property to extract only one instance of sets of duplicated fields, and may involve joining the first-created table to the wide-flat table in order to pick up the value of a new primary key field for use in a foreign key. In the tutorial, I was advised to begin creating my tables with make-table queries (rather than beginning with empty tables). I suspect that some of my problems began here. In any event, my two questions at this point a 1. When I created the empty tables with appropriate linking fields and relationships, should I have enforced referential integrity, or just established the relationships without enforcing referential integrity? 2. I know that I need to get from my wide flat-file table to the tables with the relationships. I understand the unique-values property, which was explained well in the tutorial I read, Can you, however, explain a little more about the phrase "...and may involve joining the first-created table to the wide-flat table in order to pick up the value of a new primary key field for use in a foreign key"? I think this is where I am messing up somehow. When I created the empty tables, I established the PKs and FKs. In doing this, should I have somehow accounted for specific fields in the wide flat-file table? Many thanks, Steve |
#7
|
|||
|
|||
Help - Convert Access flat file to normalized tables
....Although you will undoubtedly make fewer mistakes than me and may not
make any at all! g Fred |
#8
|
|||
|
|||
Help - Convert Access flat file to normalized tables
On Mon, 28 Jun 2004 18:48:22 -0600, John Vinson
wrote: The Sales table would have foreign key fields CustomerID (long integer, linking to the Customers table autonumber ID) and ItemID (linked to Items autonumber ItemID) - but your wide-flat table doesn't have these fields! So you would create a Query joining WideFlat to Customers *by customer name*, to pick up the ID, and likewise joining it to Items *by Item* to pick up *that* ID. John, Many thanks! After reading the above paragraph a couple of times, and drawing its implications on a note pad, I managed to turn my single wide-flat table into 4 normalized tables that do exactly what I want. This will result in my being able to develop a form and subforms that make my successor's job of entering data much, much easier. And, of course, it will aid my subsequent analysis of the data. This newsgroup has been invaluable to my interest in learning the concepts that underlie the practical application of Access. Thanks to Fred and Kevin too, not to mention the hundreds of others who freely give their advice and encouragement each day. Steve |
#9
|
|||
|
|||
Help - Convert Access flat file to normalized tables
On Tue, 29 Jun 2004 08:02:10 -0700, Steve Newton
wrote: Many thanks! After reading the above paragraph a couple of times, and drawing its implications on a note pad, I managed to turn my single wide-flat table into 4 normalized tables that do exactly what I want. This will result in my being able to develop a form and subforms that make my successor's job of entering data much, much easier. And, of course, it will aid my subsequent analysis of the data. Glad to have been of assistance! And thanks for the feedback: it's good to know that we're having a beneficial effect. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
access 2000 convert to 2002 | Bryan | New Users | 1 | June 11th, 2004 02:10 PM |
Unsafe Attachments | Ron | Installation & Setup | 2 | June 9th, 2004 01:55 AM |
Need help with Tables Design and Relationships | Tom | Database Design | 24 | May 19th, 2004 06:51 PM |
Default File Location | Derek Ruesch | Setting up and Configuration | 6 | January 30th, 2004 03:03 AM |