Tables relationship Import & export data

Tables relationship Import & export data Comp1000 Lecturer: Ali Darejeh Resources: Alexander, M., & Kusleika, R., 2016, Access 2016 Bible, John Wiley & Sons. Darejeh, A. 2011. Reference guide to Access 2010. Saheer engineering group. Tutorialspoint: https://www.tutorialspoint.com Quac kit: https://www.quackit.com Gcflearnfree: https://www.gcflearnfree.org Microsoft Office support website: https://support.office.com

Link Tables using relationship feature A relationship helps to combine data from two different tables. Why do we need to link tables? One of the goals of a good database design is to remove duplicate data. To achieve that goal, we should divide our data into the different tables, then connect those tables to each other, so that each value is represented only once.

Why do we need to link tables? What is wrong with the this purchasetable?

Why do we need to link tables? To avoid entering duplicate values we can brake down the table into the 3 tables. Customers Purchased Items

Why do we need to link tables? Orders

What are primary key & Foreign Key? Relationship is created between a field in one table (usually a primary key) and a field in anothertable which is called Foreign Key. Customers Orders Purchased Items CustomerID Last Name First Name Address PartID Description UnitPrice OrderID PartID CustomerID Quantity • In Customers table Customer ID is the primary key as it uniquelyidentifieseach Customer. • In the Orders table Customer ID is a foreign key asitdoesnotuniquely identify each record and it is used to “link” to the Customerstable. • Although, we repeat the values in foreign key, it helps to avoid repeating the values of the other fields.

Foreign KeyExample A foreign key is a field in one table thatis also a primary key of anothertable SpeakerID isthe primary key of the Speakers table and foreign key in the SessionSpeaker table. It means that each speaker can talk in different sessions.

Types ofRelationships • One-to-manyrelationship • E.g. One customer can buy different items. • One-to-onerelationship • E.g. Each person has one driver’s license and one driver’s license belongs to only oneperson. • Many-to-manyrelationship • E.g. A student cantake differentsubjects and eachsubject canhave differentstudents.

How to create relationships? Create a Foreign Key column in the table that you want to connect it to the main table. Hint: the Foreign Keycolumn should have the same data type as the primary key of the main table. Click on database tools tab. Click on relationships button.

How to create relationships? Select the tables that we want to connect. Click on the primary key of the main table and drag & drop it on the Foreign Key of the second table.

How to create relationships? Edit relationships window appears to create the relationship. Select Enforce Referential Integrity checkbox to stop user entering a record with a foreign key in a related table unless that value already exists asa primary key in its home table.

How to create relationships? • Cascade Update RelatedFields • By ticking this checkbox, if primary key isaltered, related fields are automaticallyupdated. • Cascade Delete Related Records • By ticking this checkbox, when one item is deleted in the main table, it automatically deletes all records in the related tables that refer to the primarykey of that item. Click Enforce Referential Integrity Click Cascade Updatein case the primary keychanges Probably best not to clickCascade Delete RelatedRecords

Relationships BetweenTables One-to-manyrelationships Manyside: the“” (infinitysymbol) One to One: https://support.office.com/en-us/article/video-create-one-to-one-relationships-a5868c76-50ea-44b7-892d-43f2af2802e0 Many to Many: https://www.youtube.com/watch?v=A4FmEYrGzOY

Demonstration 9 We want to create a relationship between bank customers, their loans, and their loan repayments. Each customer can take different loans and repay their loans monthly. Customers Loans Payments

Creating table and import data from an external databaseor application Access enables users to import data from an external data-source such as web, excel, text file or another database. This data can be used to fill an existing table or create a new table.

How to import data? Go to external data. Click on New data source. Select the source that you want to import data from.

Import Data fromExcel Click Browse tofind aspreadsheet Decide what youwant to do with thedata

Import Data from Excel Choose theworksheet toimport Preview ofthe worksheetdata Click Next tocontinue

Import Data from Excel Columnheadings Click Next tocontinue

Import Data from Excel Change Indexed property to Yes(No Duplicates) Index provides quick retrievalofdata basedon the primarykey. ClickNext tocontinue

Import Data from Excel AID becomes the primarykey Click Next tocontinue

Import Data from Excel Accounts becomesthe tablename Click Finishto import thedata

Import data from a text file To use a text file, the contents of the file must be organized in such a way that the wizards can divide the contents into a set of records and fields. There are 2 ways: • Delimited files: Type each record on a separate line and separate the fields by a single character, called the delimiter. The delimiter can be a tab, semicolon, comma, space, and etc. • Fixed-width files: Type each record on a separate line and keep the width of each field consistent across records. For example, keep the first field of every record 7 characters long, and the second field 10 characters.

Import data from a text file • Click Text File from the External Data tab on the Ribbon.

Import data from a text file • Use the Browse... button to navigate to and select the import file. In this example we Selected Append a copy of the records to the table to import the content into an existing table.

Import data from a text file • Select the text format based on which you want to separate the fields.

Import data from a text file • If you select Delimited, Access will ask you to select the file's delimiter.

Import data from a text file • Click Finish to run the import.

Import data from a text file • The data from the text file will be imported to the selected table.

Export Access tables Access enables users to export data to an external file such as HTML, excel, or a text file. Go to external data. Select the desired export option.

Export Access tables to excel Select the table or a query that you want to export. Go to external data tab. Click on excel.

Export Access tables to excel Set a destination to save your file and type a name for it. If you want to keep the format of the tables data tick export data with formatting and layout.

Export Access tables to excel Access gives you the option of saving the steps you just took, so that you can re-run them again later.

Export Access tables to excel • You can now check your exported file in Excel.

Compact and repair a database Access databases increase in size over time and to save storage and solve database problems we can use compact and repair feature. • Compacting to: • Avoid loss of data • Recover unclaimed space • Defrag fragmented databases • Repair corrupted databases

Compacting & Repairing a Database

Backing Up a Database • Backups are critical to a database • Access backup feature provides a default file name, including the original file name and adds on the current date.

Access save style • When you change the content of a field (e.g., phone number), Access saves the changes as you shift focus to another record. • Changes to the design of a table, query, report or form must be saved • Multiple users can work on an Access database simultaneously as long as they are not working on the same record.

Question time

Tables relationship Import & export data