Results 1 to 15 of 15
  1. #1
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Breaking up a flat database (2000)

    I know this is kind of a dumb question, but I figure I can either spend 5 hours digging through the help system and trying things, or I can cast myself on the collective knowledge of the Lounge.

    I have an old database with a very flat design (just one table). I want to break it up so that fields like "Author" are replaced by an AuthorID, with the actual author's name appearing in a separate table. In other words, I want to convert it to a relational database.

    I've already figured out how to create a uniquely-populated Authors table using an Append query, but I can't figure out how to fill in the new AuthorID field in the original table so that it references records in the new Authors table.

    The closest I could come was joining the Author and AuthorID fields of the two tables in a single join, then using an Update query to change AuthorID in the original table to Authors.AuthorID. But it doesn't work.

    I'm sure there's a simple solution, as I've done this before, a long time ago. I'm also certain that Access won't do nested dereferencing (which would solve my problem).

    Please help!

    Paulius

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Breaking up a flat database (2000)

    The usual process for doing this kind of thing is to create a MakeTable query which gets all of the unique Authors (in this case). Then you put an AutoNumber field on the new table. Next you create a new field in the original table that is a Long Integer, and will hold the AuthorID value. Then you do a join in a query between the Author name in the Authors table and the Author name in the original table, which should give you the same number of records as you had in the original table. Then change the query to an update query, and put the AuthorID autonumber field from the Authors table into the AuthorID in the original table. Once that is done, you can delete the Author field from the original table.

    Of course once you've done that, you have to figure out how to put in a new Author in the process of entering new records in the original table. One way is to use a combo box and trigger an add process on the "Not In List" event.
    Wendell

  3. #3
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Re: Breaking up a flat database (2000)

    Thanks, Wendell,

    Yes, indeed, that worked beautifully.

    One thing, though. If you use a Make Table query to build your Author list from the existing table, it requires passing the data through a temporary table. This is because the way to remove duplicate entries is by using a Primary Key, and you can only create a Primary Key in an existing table.

    Therefore, a better way of building the Author table is to first create an empty table with the structure you want and define a Primary Key, then use an Append query to populate the table. If the data you're copying consists of more than one field (eg: Firstname, Lastname), put those fields in the new table but also put another field that will be a unique combination of the two and make this the Primary Key. When you build the Append query, use an expression to uniquely combine the other fields (& operator) and Access will automatically remove duplicate records. You can then remove the combined field. Don't forget to sort the Append query if its important to do so.

    Hope that helps someone else trying to do this sort of thing.

    Paulius

  4. #4
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Re: Breaking up a flat database (2000)

    Forgot to mention.

    As for adding new Authors to the Author table from a Form, I got some code from WAW ages ago. Attached is a version of that code, modified to deal with two-field data. It needs to be added to the Not In List event of a combo box.

    Paulius
    Attached Files Attached Files

  5. #5
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Many-to-many relationships

    Another question on the same project.

    I'll start by outlining what I'm trying to do, then go through all the different things I've tried (that haven't worked).

    Each record in tblBooks contains up to 3 different Authors. I want to have just one tblAuthors table containing the names of all possible authors. So if a book has 3 authors, each of the three AuthorNID fields in tblBooks will refer to a different Author in tblAuthors.

    The trouble is, this immediately introduces ambiguities. If I create three one-to-many relationships between tblBooks (Author1ID, Author2ID, Author3ID to...) and tblAuthors (...AuthorID), then when I try to build a query to show the actual names of the three authors, simply dragging the AuthorName field from tblAuthors is ambiguous.

    If I add two more copies of tblAuthors to the query, delete two of the relationship joins and create joins between Author2ID and tblAuthors_1.AuthorID (similarly for Author3ID), then it *almost* works. However, only records which actually have three authors are shown. What I want is for all records from tblBooks to be shown, even if any of the AuthorNID fields are blank.

    Reading through the help system, I figure I might have a many-to-many relationship here, but not of the sort shown in the example. Still, the example shows that an intermediate table is necessary. I'm not sure what this intermediate table should be.

    I thought to create tblAuthors1 through tblAuthors3, with tblAuthors1 containing two fields: AuthorID (joined to tblAuthors) and Author1ID (joined to Books). There would be one record for each record in tblAuthors, containing the same ID number in both fields. I could then create three queries to extract the AuthorName for each AuthorID for each Author and use these queries in my final query.

    There's two problems with this. First, if I add a new Author, I also need to add a new row to tblAuthors1, 2 and 3. Second, I think the query may include repeated records from tblBooks, each record appearing once for each match of each author.

    So, what's the correct, simple way to do this?

    Many thanks, in advance,

    Paulius

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Many-to-many relationships

    Hi Paulius,

    The best way to handle this is an intermediate table tblBookAuthors that contains unique combinations of BookID's and AuthorID's. You don't need any AuthorID field in tblBooks in this approach,and you use just one tblAuthors table.

    tblBookAuthors will contain up to three records for each book - one record for each author. You can include other information that is specific to the combination of book and author in this table, such as the order in which the authors are to be listed.

    tblBookAuthors has a primary key on the combination of BookID and AuthorID, and it is joined to tblBooks and tblAuthors on BookID and AuthorID, respectively. Set referential integrity for the relationships to ensure that you can't create combinations with non-existing books or authors.

    You'll probably have more questions later on, but first see if you can translate this to your situation; then feel free to come back.
    Attached Images Attached Images
    • File Type: png x.png (4.2 KB, 0 views)

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Breaking up a flat database (2000)

    Sorry for the delay in responding - I've been away from Internet capability for much of the last 2 weeks.

    I should have provided more detail in my response about building the table. There is a property for queries that says only give the results for unique values - if you set that to Yes, you will only get one record for eadh unique author. That way you can use the make table query and not be concerned about getting duplicates (assuming you have spelled each author correctly).

    Also a comment about name uniqueness - it is highly likely in many applications that you will encounter people who have the same name. In your application that may not be a concern, but in most it is something you have to deal with - often by including some sort of address information or other unique data such as birth date.
    Wendell

  8. #8
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Re: Breaking up a flat database (2000)

    The "Unique Values" property of a query judges uniqueness based on all visible fields. This could indeed work in the situation discussed. But you have to make sure no other fields are visible.

    Thanks for your help, Wendell!

    Paulius

  9. #9
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Re: Many-to-many relationships

    Thanks for the tip, Hans.

    I think I'm starting to understand how Access works. But it raises some interesting questions. Some of these come from the observation that my "problem" is completely irrelevant when constructing a query on which to base a form, simply because the IDs themselves need to be passed to the form.

    A disclaimer right at the start: I'm going to be generalising based on a fairly basic structure and a very limited set of requirements. If I misstate or misunderstand anything, or don't get some advantage of one approach over another, please correct me.

    Let's see if I've got this straight.

    Relationships between tables are basically irrelevant when entering data. This is because, when you put a list box in a form, the list source is entered manually. When you select an item in the list, the name translates to an ID (using the list source table or query), which is stored in the main table. You could easily use the wrong query for the list source and end up with IDs that have no meaning, but you'd never know it, even if the tables are linked!

    Relationships become useful only when retrieving data. Then, they provide a translation from the ID stored in the main table to a corresponding text value in the subsidiary table. This works great in many cases, automating a lot of the translation work.

    BUT they don't help when the relationship is effectively many-to-many.

    I tried three main approaches:
    1. As Hans suggested, create a many-to-many link table, tblBookAuthors, that contains an entry for each author of each book, associating each book with its authors, one by one.
    2. Create THREE link tables, one for each author. The advantage over Hans suggestion is that there can be at most one author in each position, 1, 2 and 3.
    3. Use three many-to-one links to three ID fields in Books and sort it all out in a query.

    As far as I can tell, no matter which way you do it, you'll end up having to sort it all out by fudging the links in a query!

    What I'm trying to do is come up with a query that returns the following fields: Title, Author1, Author2, Author3, etc.

    In case 1, I can't simply put Books and Authors in the query and expect to extract the three Author fields, as it's ambiguous. So I create intermediate queries for each and rename the Author field to Author1, etc. Each query also uses as a criterion that ListingOrder equals 1, 2, 3, respectively. I then create another query based on Books and these three queries. For some reason, no relationships exist between the ID field in Books and the BookID field in the three queries, so I have to make one manually, then set it to "Include ALL records from Books".

    In case 2, I can't even build a query that doesn't either return duplicate entries or just one record.

    In case 3, within the query, I can delete two of the joins to Authors.AuthorID, add two extra copies of Authors to the grid and recreate those joins with the new copies of Authors. However, I also have to set every join to "Include ALL records from Books." In other words, exactly the same thing I did in case 1, but without the intervening queries or tables.

    So, unless I did something wrong with case 1 (eg: the queries should have automatically had joins to Books), case 1 doesn't seem to give you anything over case 3. If that's true, then I'd really like to get case 2 working, reducing the number of blank Author3 fields without the need for extra queries or the possibility of duplicate Author1 entries (although perhaps setting the Primary Key to BookID+ListingOrder would fix that).

    I'd love to hear which bits of this I've gotten wrong!

    Sorry to make this so complicated. I guess I'm trying to not only fix my problem, but also understand how Access works.

    Sincerely,

    Paulius

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Many-to-many relationships

    Paulius,

    <hr>Relationships between tables are basically irrelevant when entering data. This is because, when you put a list box in a form, the list source is entered manually.<hr>
    Wrong on both counts. You can set the Row Source Type of a list box or combo box to Table/Query and the Row Source property to the name of a table or query. This is much more efficient, dependable and flexible than a manually entered List of Items. If you set Referential Integrity for the relationship between tables, you ensure that users can't enter non-existing values for fields linked to another table.
    <hr>In case 1, I can't simply put Books and Authors in the query and expect to extract the three Author fields, as it's ambiguous. So I create intermediate queries for each and rename the Author field to Author1, etc.<hr>
    You are right that you need to create intermediary queries in case 1. In my case, tblBooks was automatically linked to each of the queries, but I had to change the joins to outer joins manually.

    I have attached a small demo database (Access 97, zipped) that demonstrates this. Take a look at the design of the queries and of the data entry form with subform.
    Attached Files Attached Files

  11. #11
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Re: Many-to-many relationships

    Hi Hans,

    You're right: relationships can matter for data entry, but not for the reason you stated. It's true that the Row Source for a listing control can be set to Table/Query, but that is still done manually. And if you pick the wrong table or query, that could cause problems (for example, if you used a full list of clients rather than an appropriately restricted list). I agree that using a Query as the Row Source is the safest way to do things.

    But the real way in which relationships help data entry is, for example, if the query on which you base your data entry form contains IDs from different tables (such as for a many-to-many join). Then, the relationship ensures that the correct ID is used as the data for a control, because of its link with the BookID (in this case).

    I think I'm getting the hang of this!

    Thank you, indeed, for the sample database. It was very helpful and I really appreciate your efforts.

    A couple of things about it: first, qryBooks needs the link to tblPublishers to be an outer join, in case some records have a blank PublisherID (alternatively, PublisherID could be made a required field).

    Second, although using a subform to allow as many authors as necessary is a great idea, there doesn't seem to be any easy way to fill in the ListingOrder field, so the Authors won't actually appear in qryAuthorN. It looks like some VBA is required to fill in this field.

    On the other hand, uniqueness of the ListingOrder seems to be guaranteed by the joint Primary Key. Am I correct in thinking that a joint Primary Key means each record must be unique with respect to both Primary Keys individually? For example, setting the ListingOrder to 1 for two records for the same book makes the record non-unique with respect to BookID, which is not allowed.

    Finally, using what I learned from your example, I was able to create a slightly different structure (case 2 from an earlier post). I use three intermediate tables tblAuthorN, one for each possible author. I then create a query (qryAuthorN) for each of these. This not only links Authors to tblAuthorN, it also delivers the actual author name to qryBooks as AuthorN. This effectively removes any ambiguity. I can then create a Form source query with all the fields from tblBooks along with the three IDs AuthorNID from qryAuthorN. And I can build a display query (eg: for a report) getting the Author names directly from the queries, rather than needing three copies of tblAuthors in the design grid and having to add and delete joins manually. The only thing I had to do manually was make all the joins outer joins in qryBooks and qryBookEntry. So far, everything's working beautifully!

    Many thanks to both Hans and Wendell. I think I get it now. [img]/forums/images/smilies/smile.gif[/img]

    Paulius

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Many-to-many relationships

    Hello Paulius,

    Having a primary key on two fields means that the combinations must be unique, there can be repetitions in the individual fields (one author can occur several times because he/she has written more than one book; one book can occur several times if it has more than one author, but a specific book/author combination can occur only once)

    It would be easy to make the listing order editable by the user in my example - just add the ListingOrder field to the subform, and if you like to see the authors in the correct order here, set the Record Source of the subform to qryAuthors.

  13. #13
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Re: Many-to-many relationships

    Just when I thought everything was working, I've found a problem. D'oh!

    In the sample database, if you try to delete an author in frmBooks (and I mean if a second author has been added accidentally and needs to be removed from the record for a particular book, not removing the author from tblAuthors), you get the following error: "Index or primary key cannot contain a Null value." In this case, the record in tblBookAuthors needs to be deleted somehow.

    In my own version (with a separate intermediate table for each author), if I try to add or remove an author once the book record has been created, I get the same error. In this case, the record in tblAuthor1 either needs to be deleted, or the BookID needs to be filled in (adding an author). I don't understand why the BookID is not filled in automatically, but changing the table to have a joint Primary Key doesn't seem to matter.

    I think I have a partial solution. If I add another ID field to tblAuthor2, set it to AutoNumber and make it the lone Primary Key, it seems to fix the problem. However, deleting an author leaves an empty record in tblAuthor2.

    So, a couple of questions.

    1) How to delete records in tblBookAuthors or tblAuthor2 when an author is removed from a book? (both versions)
    2) Why isn't BookID filled in automatically in tblAuthor2 when a book's record is edited in frmBooks (my version), but it is when the book's record is first created?

    Again, thanks for any help,

    Paulius

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Many-to-many relationships

    >> 1) How to delete records in tblBookAuthors or tblAuthor2 when an author is removed from a book? (both versions)

    You can let Access take care of that by specifying that the relationship between the main table and the intermediate table(s) performs cascading deletes. Open the Relationships window, double click one of the relationships, and check all three check boxes (Enforce Referential Integrity, Cascade Update Related Fields and Cascade Delete Related Fields).

    If you can't check Enforce Referential Integrity, make sure that the linking field is the primary key on the "one" side, and that there are no conflicting data in the tables.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Many-to-many relationships

    Addition to 1)

    In the demo I attached higher up in this thread, you can't remove an author by clearing the name, you must delete the record in the subform:
    Click in the author's name.
    Then select Edit | Delete Record or click the corresponding button on the Form View toolbar.

    About 2)

    I wouldn't create three author tables in that case; I would create three fields AuthorID1, AuthorID2 and AuthorID3 in tblBooks, and link each to the single tblAuthors (you will see three authors tables in the Relationships window, but they are just copies). In the form, display AuthorID1 etc. in a combo box with tblAuthors as Row Source.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •