{"id":1259,"date":"2023-04-14T09:44:27","date_gmt":"2023-04-14T08:44:27","guid":{"rendered":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/?page_id=1259"},"modified":"2023-04-14T09:56:26","modified_gmt":"2023-04-14T08:56:26","slug":"microsoft-access","status":"publish","type":"page","link":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/data-modelling\/microsoft-access\/","title":{"rendered":"Microsoft Access"},"content":{"rendered":"\n

Hopefully at the workplace you will use a full RDBMS such as Oracle, MySQL, MariaDB etc. But you can run SQL commands on Microsoft Access to at least get some experience using them.<\/p>\n\n\n\n

Creating a database<\/h2>\n\n\n\n

Launch Access. From the Home screen click the Blank database to start.<\/p>\n\n\n\n

\"Example
Example Access home screen<\/figcaption><\/figure><\/div>\n\n\n\n

Give the database a name when prompted.<\/p>\n\n\n\n

Access then creates a database with a new table to edit already made.<\/p>\n\n\n\n

\n\n\n\n

Add columns<\/h2>\n\n\n\n

Access has already added a column to work as a key. This is a number field that automatically increments each time you add a record.<\/p>\n\n\n\n

Click the dropdown beside \u201cClick to Add\u201d. Here you can see the data types available for the attributes.<\/p>\n\n\n\n

\"Example
Example to add a column<\/figcaption><\/figure><\/div>\n\n\n\n
\n\n\n\n

Access datatypes<\/h2>\n\n\n\n
Datatype<\/strong><\/td>Description<\/strong><\/td><\/tr>
Short text<\/td>Up to 255 characters with no formatting.<\/td><\/tr>
Long text<\/td>Up to approximately 65,000 characters. Allows html and other formats.<\/td><\/tr>
Rich text<\/td>A short cut to a long text field with formatting options present.<\/td><\/tr>
Number<\/td>Integers that range from -32,768 to 32,767.<\/td><\/tr>
Large number<\/td>Integers that range from -2,147,483,648 to 2,147,483,647.<\/td><\/tr>
Currency<\/td>15 digits to the left of the decimal point and 4 digits to the right. Not rounded off.<\/td><\/tr>
Attachment<\/td>Stores files in the record. Not all extensions allowed.<\/td><\/tr>
Data and time extended<\/td>Increased precision and formatting options<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Common Access datatypes<\/p>\n\n\n\n

Access defaults Number fields to use integers. If you want to use floating point numbers you will need to alter the Field Size in the table\u2019s Design View before adding data.<\/strong><\/p>\n\n\n\n

\n\n\n\n

Foreign keys<\/h2>\n\n\n\n

Use a number field for the foreign key because all the primary keys in this exercise are the automatically added number fields.<\/p>\n\n\n\n

Close any open tables that you want to link together.<\/p>\n\n\n\n

Click on the Database Tools menu then choose the Relationships icon.<\/p>\n\n\n\n

\"Relationships
Relationships button<\/figcaption><\/figure><\/div>\n\n\n\n

Click and hold on the ID under Driver, drag the mouse until you are over the DriverID in the link table and release.<\/p>\n\n\n\n

In the popup box tick the checkboxes so that deleting the Driver will also delete the matching items in the link table. Click Create and repeat for the VehicleID link. Save the Relationships tab.<\/p>\n\n\n\n

\"Example
Example relationships screen<\/figcaption><\/figure><\/div>\n\n\n\n
\n\n\n\n

Forms<\/h2>\n\n\n\n

To insert data in Access you use forms.<\/p>\n\n\n\n

Click on the Create menu and choose Form Wizard from the ribbon.<\/p>\n\n\n\n

\"Example
Example forms wizard<\/figcaption><\/figure><\/div>\n\n\n\n

Choose the columns that you want in the form. This will usually be all of them except the ID as that is automatically added by the system.<\/p>\n\n\n\n

Then choose whichever layout you prefer.<\/p>\n\n\n\n

Give the form a name the choose to either start adding data or change how the form looks. Choose the first option for now.<\/p>\n\n\n\n

\"Example
Example forms wizard final screen
<\/figcaption><\/figure><\/div>\n\n\n\n

Forms aren\u2019t just about adding data. You will see existing data too and can edit it.<\/p>\n\n\n\n

If you are adding a new record, then use the arrows at the bottom of the screen to move to the end of the records until you are at a blank one.<\/p>\n\n\n\n

\"Example
Example form screen<\/figcaption><\/figure><\/div>\n\n\n\n

Foreign keys have to exist before they can be used. A way to do this in forms is the combo box. Right click on the form and choose to open it in either Design or Layout view. Add a combo box from the ribbon.<\/p>\n\n\n\n

\"Combo
Combo box button<\/figcaption><\/figure><\/div>\n\n\n\n

In the popup wizard choose the first option to get the value
from another table. Choose the table that foreign key is from.
Choose the columns that you need, the ID plus some
description that makes more sense to the user. Choose how to
order the results in the next screen.<\/p>\n\n\n\n

Leave the hide ID checkbox ticked and store the value in the
foreign key field. Finally give it a Label.<\/p>\n\n\n\n

\"Example
Example combo box wizard<\/figcaption><\/figure><\/div>\n\n\n\n

Making it look nicer<\/h3>\n\n\n\n

Click on the combo box in the form and then view the Properties pane to the right. Under Data -> Row Source you should see something like:<\/p>\n\n\n\n

SELECT [Vehicle].[ID], [Vehicle].[Number Plate], [Vehicle].[Manufacturer], [Vehicle].[Model] FROM [Vehicle] ORDER BY [Manufacturer], [Model], [Number Plate];\n<\/code><\/pre>\n\n\n\n
Change this to something like:<\/p>\n\n\n\n
SELECT [Vehicle].[ID], [Vehicle].[Number Plate] & \" \" & [Vehicle].[Manufacturer] & \" \" & [Vehicle].[Model] FROM Vehicle ORDER BY [Manufacturer], [Model], [Number Plate]; \n<\/code><\/pre>\n\n\n\n
What you\u2019ve done is combine the descriptive results into one column. Now click on the Format tab in the property pane. You want to change the column count from 4 to 2 and alter the width of the new single column.<\/p>\n\n\n\n
\n
\n
\"Example
Example before change<\/figcaption><\/figure><\/div>\n<\/div>\n\n\n\n
\n
\"\"
Example after change<\/figcaption><\/figure><\/div>\n<\/div>\n<\/div>\n\n\n\n
Next: SQL<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Hopefully at the workplace you will use a full RDBMS such as Oracle, MySQL, MariaDB etc. But you can run SQL commands on Microsoft Access to at least get some experience using them. Creating a database Launch Access. From the Home screen click the Blank database to start. Give the database a name when prompted.… Continue reading Microsoft Access<\/span><\/a><\/p>\n","protected":false},"author":5710,"featured_media":0,"parent":1179,"menu_order":5,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1259","page","type-page","status-publish","hentry","entry"],"_links":{"self":[{"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/pages\/1259","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/users\/5710"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/comments?post=1259"}],"version-history":[{"count":4,"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/pages\/1259\/revisions"}],"predecessor-version":[{"id":1277,"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/pages\/1259\/revisions\/1277"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/pages\/1179"}],"wp:attachment":[{"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/media?parent=1259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}