How to use Microsoft Access - Importing Tables - part 7 of 13 in this educational video from dizzo95.
Tags:Microsoft Access - Importing Tables - part 7 of 13,dizzo 95,dizzo95,dr dan izzo,education,importing tables,microsoft access,part 7 of 13,software
Grab video code:
We've created the customers table, now we need the invoices table. We’ll get this data from another program with access you can use data you’ve already entered in other programs such as spreadsheets or other database programs. There are two basic ways to do this, you can either import data meaning bring data into access and make it an access table, or your can link to data in which case the data remains in its original program but you can use access to work with it. We’d use the spreadsheet program Microsoft excel 97 to create a work sheet with invoice information.
We want to move that information into our access data base so we’ll import the invoice worksheet. We click on the file menu and select get external data. From the submenu we’ll choose import and when we do the import dialog box opens. To display any excel files we open the files of type list, we’ll select Microsoft excel and when we do the excel files display. We’ll import the invoices file. We double click on it and the import spreadsheet wizard dialog box opens.
The wizard’s displays the spreadsheet data the import wizard asks if the first row contains column headings that can be used as field names for the table. This is what we want, so we click in the check box the labels in the first row become column headings. Then we click on next to move to the next import dialog box. It asks if we want our data in a new table or added to an existing table. We want a new table so we leave that selected, we click on next. This dialog box lets us set the field name and indexing for each field. The data type is grade out because its already set in the excel worksheet. We move on to the next dialog box. Here the wizard ask for a primary key. The default setting is for access to add one. We’d like the invoice field to be the primary key field. This way we don’t have to worry about duplicate invoice numbers.
We click on choose my own primary key and access suggest the first field invoice number. We could choose another field but that’s the one we want. The wizard highlights the selected field we click on next. The final wizard dialog box lets us give the table a name. So we type invoices in upper and lower case, which we prefer. We click on the finish button access displays a message box telling us the importing is completed. So we click on okay.
The worksheet is now an access table, to open the table we double click on it. Here is our imported invoices table. The table is an invoice number order with the costumer ID in voice total and due date. Now notice the text information is left justified, while numerical data and date information are right justified. We’ll best fit all of the columns now. And now we can format and edit this table just as if we created in access. The invoices table is setup the way we want it and in the next topic were going to use access to create a relationship between the invoices table and the customers table.