Results 1 to 13 of 13
  1. #1
    JJeffH is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30

    Query to parse unnormalized table into separate, normalized tables

    I’m trying to learn Access from the basics up. I just read Hernandez’ “Database Design for Mere Mortals” and I’m practicing on a simple db of media viewings that I kept in Excel.

    There were separate Excel sheets for things viewed on Amazon, Netflix, BritBox, and PBS. I standardized the fields in the four lists then imported them into one table in Access. Meanwhile, I designed normalized tables in Access and now I want to parse my one big list into the separate tables. And that’s what I need advice for.

    The single list has the fields: Date Viewed; Title of Program; Episode Name; Season; Episode Number; and Source (i.e. Netflix, etc.).

    The tables I’ve designed are: Titles; Episodes; Viewings; Sources; Genres; and Types. (I don’t currently have genre or type recorded, but I can going forward.)

    Step 1 was easy: I used a Make Table query on the big list to create the Titles table with unique values from the Title of Program field. But I don’t know how to proceed with step 2: parsing the Episodes and Viewings data into their respective tables.

    The Episodes table has the fields: Episode Title; Season; and Number. For single event programs, like movies, of course, those three fields are blank.

    The Viewings table is a linking table to establish a many to many relationship between the Titles and Episode tables. It has the fields: TitleID (foreign key); EpiTitleID (foregn key); Date Watched; SourceID (foreign key); and GenreID (foreign key).

    I have created the Sources, Genres, and Types tables and populated them. Each table has only one field and it's the primary key.



    HERE’S THE QUESTION:
    How can I can set up queries to create the Episodes and Viewings tables which maintain the linkages between Program Title, Episode, and Date Watched?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Need to create Episodes and Viewings tables with Title field. Then add another field for TitleID. Build query that joins Titles to Episodes linked on title fields. Switch to UPDATE and designate to update TitleID field with ID from Titles. Do same with Titles and Viewings.

    When update works, delete Title fields from Episodes and Viewings.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    JJeffH is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30
    Well, either I misinterpreted your instructions or I explained the issue incorrectly. I set it up as shown in my screenshot below but it updates 0 records.

    The Titles table is already populated with unique values from the Name field of the Raw Input table.
    I added TitleID and Title fields to the Episodes table. Then I linked Title to the Name fields in both the Titles and the Raw Input tables, and TitleID (PK) with TitleID (FK).

    What I think I'm doing here is updating the TitleID and Title fields of the Episodes table with the values in the same fields in the Titles table. And updating the Episode Title, Season, and Number fields of the Episodes table with the values from the Raw Input table in the fields Episode Name, Season, and Episode, respectively.

    (In case it's relevant, I've included a screen shot of the table relationships I already have set up, too.)



    Click image for larger version. 

Name:	Query1.jpg 
Views:	17 
Size:	142.4 KB 
ID:	41798

    Click image for larger version. 

Name:	Relationships.jpg 
Views:	17 
Size:	111.3 KB 
ID:	41799

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    It's unclear whether all the records you need are already in table Episodes. If not, it needs to be populated with an Append query from table Titles.
    Then you can use an update query for data from Raw Input to update the existing Episodes records as needed.
    Last edited by davegri; 05-09-2020 at 12:13 PM. Reason: clarif

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do not join on ID fields - there is nothing to join on until after update - just Title to Name.

    Why are you including RawInput table? Isn't RawInput data already in Episodes? Except not seeing Date and Source fields.

    Date is a reserved word. Should not use reserved words as names for objects.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    JJeffH is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30
    The table Episodes in not populated.

    The table Raw Input is the single list of all the data I’m bringing into Access. It is fully populated with 5,000 records.

    The Raw Input table includes 3 main elements: the Program, the Episode, and the Date Watched.

    I want Program data in one table, Episode data in another, and I want to link them with a Viewings table that contains the Date Watched.

    Where I’m getting confused is how to parse the Raw Data table while maintaining the linkages among those three main elements. In other words, I need to preserve the fact that on a certain Date I watched a certain Program and Episode, for all 5,000 records.

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The Raw Input table includes 3 main elements: the Program, the Episode, and the Date Watched.
    Much confusion.
    Nowhere in any diagram is 'Program' identified.
    You stated in post #1 that table Titles was populated, in post #6 maybe not.

    Best bet is to post the db and the spreadsheet.

  8. #8
    JJeffH is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30
    Here's what I'm trying now. It looks promising (to me, anyway).

    I added a foreign key TitleID to the Raw Input table and populated it with an Update query where the Names fields in Raw Input and Titles were linked. That worked, adding the correct TableID to each of the records in Raw Input. Then I added an auto number primary key to Episodes.

    So now I have these two tables:
    EPISODES
    EpisodeID (PK)
    TitleID (FK)
    Date Watched
    Episode Name
    Season
    Episode

    -and-

    TITLES
    TitleID (PK)
    Name
    Year Released
    Genre

    But now I'm not sure how to get the correct dates into the Viewings table (which is currently unpopulated) from the Raw Input table. I can't link the Date Watched fields because they are not unique. I have set up the Viewings table like this:

    VIEWINGS
    ViewingsID (PK) [although maybe this should be added after running an Update query]
    TitleID (FK)
    EpisodeID (FK)
    Date Watched
    SourceID (FK)
    GenreID (FK)


    - Jeff

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Create Titles and Viewings records from RawInput table. Include Name field data in both tables.
    For Titles table, run a SELECT DISTINCT INTO query. Add TitleID autonumber field to Titles table.
    For Viewings, simplest is to just copy RawInput table, give copy appropriate name, delete unnecessary fields except [Name]. Add ViewingID autonumber whenever.


    (Again, Name is a reserved word and should not use reserved words as names for objects.)

    Create a query that joins Titles to Viewings on Name fields. Set TitleID (FK) field to update from TitleID (PK) field. Query like:

    UPDATE Viewings INNER JOIN Titles ON Titles.[Name] = Viewings.[Name] SET Viewings.TitleID = Titles.TitleID;

    Then can delete Viewings.[Name] field.

    Access query designer can help build this.

    This does assume each title does not have multiple genre and year released associated.


    Also advise not to use spaces nor punctuation/special characters in naming convention.

    Also best to avoid using exact same field name in multiple tables.


    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    JJeffH is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30
    Right now the primary keys of Raw Input and Episodes are identical. That's because I populated Episodes from Raw Input and they have the same number of records (unlike Raw Input and Titles) all in the same order.

    So I think I could just copy Raw Input and change its primary field ("ID") to EpisodeID as . Then change the table name, add a new auto number field for the primary key, and delete all unnecessary fields (just keeping the Date Watched field plus the necessary FKs).

    I'll try that and report back.

    BTW, sorry for all the confusion! And I will also attack all my sloppy naming issues.

    - Jeff

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Hope you read my previous post after my final edit.

    I presume each title has multiple records in RawInput and therefore in Episodes. What you describe will not populate Episodes with TitleID value from Titles table.

    If ID is not already an autonumber field, how was it created?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    JJeffH is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30
    Yes! That seems to have done it. Thank you both for all the input!!

    - Jeff

  13. #13
    JJeffH is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30
    June7, I realize my naming, renaming, and descriptions were confusing. That's why I need forums to help me get my mind around a problem. When I see how I confuse others I have to try harder to clarify what I mean for myself.

    The input from both of you helped a lot. Applying what I said in my last post (which I got from you two - believe it or not) resulted in having all Program-related data isolated in one table, all Episode-related data isolated in another, and each Viewing event detailed in a third. That is exactly the outcome I wanted. I tested it with a query that put all the data together and it worked.

    So, again, sorry but thanks!

    - Jeff

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-01-2016, 10:25 AM
  2. making Normalized tables from Non-Normalized Tables
    By JrMontgom in forum Programming
    Replies: 1
    Last Post: 04-16-2015, 08:50 AM
  3. Macro to parse data and compare tables
    By twckfa16 in forum Programming
    Replies: 3
    Last Post: 01-05-2015, 03:24 PM
  4. Replies: 22
    Last Post: 04-25-2012, 10:03 AM
  5. New user - separate a table into two tables
    By Henry_Reimer in forum Database Design
    Replies: 19
    Last Post: 10-08-2011, 10:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums