Computer Science Homework Help

For this project, you add data to the tables in the database for a friend’s music collection. To ensure consistency, the starting file is provided for you. Append records from an Excel file to the Albums table and append records from a fixed-width text file to the Songs table. Edit data records and create a query.

[Student Learning Outcomes 8.1, 8.2, 8.4]

File Needed: MusicDatabase-08.accdb (Available from the Start File link.), NewAlbums-08.xlsx, and NewSongs-08.txt (Available from the Resources link.)

Completed Project File Name: [your name]-MusicDatabase-08 and[your name] CorrectedAlbums.xlsx

Skills Covered in This Project

• Prepare an Excel file for importing.
• Append data from an Excel file to an existing table.
• Prepare a text file for importing.
• Review a table after importing data.
• Append data from a fixed-width text file to an existing table.

1. Open the MusicDatabase-08 start file.
2. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor
3. Enable content in the database.
4. Prepare to import an Excel file.
   1. Click the Relationships button [Database Tools tab, Relationships group] to open the Relationships window. Note the one-to-many relationship between Albums and Songs. Because you are importing records to both tables, you must import the records into the “one-side” table, Albums, first.
   2. Close the Relationships window.
   3. Open the Albums table in Design view.
   4. Click through each field to note the Field Name and the Required property setting of the field.
   5. Change the Required property of the Length and RecordingLabel fields to Yes.
   6. Change the Format property of the ReleaseDate field to Medium Date.
   7. Open the NewAlbums-08 Excel spreadsheet from your student data files.
   8. Delete the blank space between the words in the column headers in cells A1, B1, F1, and G1 so the names match the field names in the table. Note that every cell on the data rows contains a value. This matches the Required property settings of the fields in the Albums table.
   9. Save the file as [your initials] CorrectedAlbums.xlsx and close Excel.
   10. Close the Albums table.
5. Append data from Excel to an existing table.
   1. Click the New Data Source button [External Data tab, Import & Link group], select From File, and then select Excel. The Get External Data – Excel Spreadsheet dialog box opens.
   2. Click the Browse button to launch the File Open dialog box.
   3. Locate and select the [your initials] CorrectedAlbums Excel file and click the Open button. The File Open dialog box closes.
   4. Select the Append a copy of the records to the table radio button. Select the Albums table in the drop-down box if it is not already selected.
   5. Click OK to launch the Import Spreadsheet Wizard.
   6. Confirm that the data records display in the wizard window (Figure 8-113) and click the Next button to advance to the next page of the wizard. Access confirms the name of the table in which to append the records.Figure 8-113 Import Spreadsheet Wizard
   7. Click Finish. The wizard displays the Save Import Steps screen.
   8. Click the Close button to complete the wizard without saving the import steps. Access appends the records to the table.
6. Verify that the records appended successfully.
   1. Open the Albums table in Datasheet view to verify that the records for AlbumIDs 51, 52, 53, and 54 were appended to the table (Figure 8-114).Figure 8-114 Datasheet view of the Albums table
   2. Click the plus sign to the left of AlbumID 51 to expand the related records. Note that no songs have been attached to Meghan Trainor’s album.
   3. Close the Albums table.
7. Prepare to import the fixed-width text file.
   1. Open the Songs table in Design view.
   2. Click through each field to note the Field Name and the Required property setting of the field. Also notice that the Data Type of the SongID field is set to AutoNumber.
   3. Right-click the NewSongs-08.txt file in File Explorer, select Open with, and select Notepad to open the file (Figure 8-115).Figure 8-115 Song text file in Notepad
   4. Note that the first row does not contain field names. Also note the blank space at the far left to represent the SongID field. Even though the SongID value is added automatically due to the field’s data type in the table, the field must be represented in the fixed-width file.
   5. Close Notepad.
   6. Close the Songs table.
8. Append data from a fixed-width text file to an existing table.
   1. Click the New Data Source button [External Data tab, Import & Link group], select From File, and then select Text File [External Data tab, Import & Link group]. The Get External Data – Text File dialog box launches.
   2. Click the Browse button to launch the File Open dialog box.
   3. Locate and select NewSongs-08.txt and click the Open button. The File Open dialog box closes.
   4. Select the Append a copy of the records to the table radio button, click the table name box drop-down arrow and select Songs. Click OK to launch the Import Text Wizard (Figure 8-116). The wizard displays the data records.Figure 8-116 Select the Fixed Width radio button to specify the file format
   5. Select the Fixed Width radio button if it is not already selected, and click the Next button to advance to the next page of the wizard. This page indicates the columns represented by each field. Access usually preselects field breaks based on spacing between fields, but they may not be correct or not all the breaks may be indicated.
   6. Click either on the Ruler at the tick mark for position 45 or in the data between the “51” and the “0” that starts the Length field value. The new field break is added into the window (Figure 8-117).MORE INFOIf you add a break in the wrong spot, click and drag it to the proper location.Figure 8-117 Select the breaks to indicate each field
   7. Click to add another field break to the left of the “W” in “Watch” at the tick mark for position 1. The completed window should match Figure 8-118.Figure 8-118 Completed field breaks
   8. Click the Advanced button to open the Import Specification dialog box.
   9. Check the Skip box for the SongID field (Figure 8-119).ANOTHER WAYEnter field breaks directly into the Import Specification dialog box. Additionally, if any fields are missing in the list, add them to the Field Information area.Figure 8-119 Import Specification dialog box
   10. Click OK to close the dialog box.
   11. Click the Next button to advance to the final page of the wizard.
   12. Verify that the Songs table name displays in the Import to Table box and click Finish.
   13. Click the Close button to complete the wizard without saving the import steps.
9. Verify that the records appended successfully.
   1. Open the Songs table in Datasheet view. Scroll down in the table to verify that the records for SongIDs 635 through 688 were appended to the table.
   2. Change the misspelling of “Stopp” to “Stop” in the SongTitle field of SongID 650.
   3. Save and close the table.
   4. Open the Albums table in Datasheet view.
   5. Click the plus sign to the left of AlbumID 51 to expand the related records. The table should match the records shown in Figure 8-120.Figure 8-120 Songs on Thank You album
   6. Close the Albums table.
10. Create a new query in Design view. The query finds albums belonging to the Sony recording labels.
    1. Add the Albums table into the Query Design window.
    2. Add all the fields into the query.
    3. Type Like “*Sony*” as the criteria for the RecordingLabel field.
    4. Save the query as SonyAlbums.
    5. Run the query. The datasheet should display three records.
    6. Change the misspelling of “Entertianment” to “Entertainment” in the RecordingLabel field of AlbumID 54. Remember that the change is automatically made in the table.
    7. Close the query.
11. Save and close the database.