WIXLIB

FilteringSELECT COUNT (ISBN) AS [How many books from Alpha Press?]FROM BOOKSWHERE (PubID 1);How many books from Alpha Press?6SELECT COUNT(ISBN) AS [Number of books],MIN(Price) AS Min Price,MAX(Price) AS Max Price,AVG(Price) AS Avg PriceFROM BOOKS;Number of booksMin PriceMax PriceAvg Price1512.00 zł49.00 zł29.27 zł45

GroupingSELECT PubID, COUNT(*) AS [Number of books]FROM BOOKSGROUP BY PubID;PubIDNumber of books1624344146

Group filteringSELECT PubID, COUNT(*) AS [Number of books]FROM BOOKSGROUP BY PubIDHAVING COUNT(*) 2 ;PubIDNumber of books16243447

Aggregation, filtering,grouping, group filteringSELECT PubID, COUNT(*) AS [Number of books]FROM BOOKSWHERE Price 25GROUP BY PubIDHAVING COUNT(*) 2 ;PubIDNumber of books14223348

Elements of the SELECT commandElementDescriptionCompulsory?SELECTReturns columns or expressionsYesFROMTaking data from tablesYes if data from tablesWHEREFiltering rowsNoGROUP BYCreating groupsYes for aggregationfunctionsHAVINGFiltering groupsNoORDER BYSorting of the outputNo49

DELETE and UPDATEDELETE BOOKS1.PriceFROM BOOKS1WHERE ((BOOKS1.Price) 40);UPDATE BOOKS1SET BOOKS1.Price [BOOKS1]![Price] 1;UPDATE BOOKS1SET BOOKS1.Price [BOOKS1]![Price] 1WHERE ((BOOKS.Price) 20);50

Queries based on multiple tablesSELECT Title, PubName, PriceFROM PUBLISHERS, BOOKSWHERE PUBLISHERS.PubID BOOKS.PubIDORDER BY BOOKS.Title;Design ViewDynaset – a computed table51

Inner join 1SELECT BOOKS.Title, PUBLISHERS.PubName, BOOKS.PriceFROM PUBLISHERS, BOOKSWHERE PUBLISHERS.PubID BOOKS.PubIDORDER BY BOOKS.Title;SELECT BOOKS.Title, PUBLISHERS.PubName, BOOKS.PriceFROM PUBLISHERS INNER JOIN BOOKSON PUBLISHERS.PubID BOOKS.PubIDORDER BY BOOKS.Title;Structure of the inner join one-to-many:TABLE1 INNER JOIN TABLE2ON TABLE1.primarykey TABLE2.foreignkey52

Inner join 2 one-to-manySELECT Count(BOOKS.Title) AS [Number of books],PUBLISHERS.PubName,AVG(BOOKS.Price) AS [Average price]FROM PUBLISHERS INNER JOIN BOOKSON PUBLISHERS.PubID BOOKS.PubIDGROUP BY PUBLISHERS.NameORDER BY PUBLISHERS.Name;53

What happens if WHERE is missing?SELECT BOOKS.Title, PUBLISHERS.PubName, BOOKS.PriceFROM PUBLISHERS, BOOKS;TitlePubNamePriceIliadBig House25.00 złIliadAlpha Press25.00 złIliadSmall House25.00 złIliadEdition 200025.00 złMoby DickBig House49.00 złMoby DickAlpha Press49.00 złMoby DickSmall House49.00 złMoby DickEdition 200049.00 złOn LibertyBig House25.00 złOn LibertyAlpha Press25.00 złOn LibertySmall House25.00 złOn LibertyEdition 200025.00 złUlyssesBig House34.00 złUlyssesAlpha Press34.00 złUlyssesSmall House34.00 złUlyssesEdition 200034.00 zł .Result:A direct productof two tables54

Inner join 3 many-to-manySELECT BOOKS.Title, BOOKS.ISBN, BOOKS.Price, BOOKS.Year,PUBLISHERS.PubName, AUTHORS.AuNameFROM PUBLISHERSINNER JOIN (BOOKSINNER JOIN (AUTHORSINNER JOIN [BOOKS/AUTHORS]ON AUTHORS.AuID [BOOKS/AUTHORS].AuID)ON BOOKS.ISBN [BOOKS/AUTHORS].ISBN)ON PUBLISHERS.PubID 103-45678-925.00 zł1989Big HouseHomerMoby Dick0-11-345678-949.00 zł1998Small HouseMelvilleOn Liberty0-12-333433-325.00 zł1987Big HouseMillUlysses0-123-45678-034.00 zł1999Alpha PressJoyceJane Eyre0-12-345678-949.00 zł1990Small HouseAustenBalloon0-321-32132-134.00 zł1995Small HouseSnoopyBalloon0-321-32132-134.00 zł1995Small HouseGrumpyBalloon0-321-32132-134.00 zł1995Small HouseSleepyMain Street0-55-123456-923.00 zł1996Small HouseSmithMain Street0-55-123456-923.00 zł1996Small HouseJonesMacbeth0-555-55555-912.00 zł1991Alpha PressShakespeareHamlet0-91-045678-520.00 zł2000Alpha PressShakespeare55

Query Customers1SELECT [First name] & " " & [Surname] AS Person,BOOKS.Title, [CUST/BOOKS].DateOut, [CUST/BOOKS].DateBackFROM CUSTOMERINNER JOIN (BOOKSINNER JOIN [CUST/BOOKS]ON BOOKS.ISBN [CUST/BOOKS].ISBN)ON CUSTOMER.CustID [CUST/BOOKS].CustID;56

Query Customers2 – OUTER JOINSELECT CUSTOMER!First name & " " & CUSTOMER!Surname AS Person,WhoBorrowsID.Title, WhoBorrowsID.DateOut, WhoBorrowsID.DateBackFROM CUSTOMER LEFT {outer} JOIN WhoBorrowsIDON CUSTOMER.CustID WhoBorrowsID.CustID;WhoBorrows is a query57

Query WhoBorrowsSELECT [CUST/BOOKS].CustID, BOOKS.Title, [CUST/BOOKS].DateOut,[CUST/BOOKS].DateBackFROM BOOKS INNER JOIN [CUST/BOOKS]ON BOOKS.ISBN [CUST/BOOKS].ISBN;58

NULL in a querySELECT BOOKS.ISBN, BOOKS.Title, [CUST/BOOKS].DateOut,[CUST/BOOKS].DateBackFROM BOOKSLEFT JOIN [CUST/BOOKS] ON BOOKS.ISBN [CUST/BOOKS].ISBNWHERE((([CUST/BOOKS].DateOut) Is Null) AND (([CUST/BOOKS].DateBack) Is Null)) OR((([CUST/BOOKS].DateOut) Is Not Null) AND (([CUST/BOOKS].DateBack) Is Not Null))ORDER BY BOOKS.Title;Available books59

Functional queriesSELECT CUSTOMER!Surname & " " & CUSTOMER!First name AS Person,Count([CUST/BOOKS].ISBN) AS [Number of books],Year([CUST/BOOKS]!DateOut) AS YearOutINTO Table NotReturnedFROM CUSTOMER INNER JOIN [CUST/BOOKS]ON CUSTOMER.CustID [CUST/BOOKS].CustIDGROUP BY CUSTOMER!Surname & " " & CUSTOMER!First name,Year([CUST/BOOKS]!DateOut), [CUST/BOOKS].DateBackHAVING ((([CUST/BOOKS].DateBack) Is Null) AND((Year([CUST/BOOKS]!DateOut)) [Give the year]))ORDER BY CUSTOMER!Surname & " " & CUSTOMER!First name;Only SELECTCreating a new table60

Functional queriesCreating o copy of a tableSELECT *INTO NotReturned2008FROM Table NotReturned;Merging two tablesINSERT INTO Table NotReturnedSELECT *FROM NotReturned2008;61

Creating reportsCharacteristic features of a report: attractive form basic information in headers and footers information grouped and sorted graphical elements improving the formMethods of creating reports: design view Wizard AutoReport: Columnar or TabelarSource of data: tables or queries62

AutoReport63

Report – design view64

ToolboxCombo boxText boxList boxLabelSubreportImageButton65

Database formA form is an Access object. It generally serves three purposes:1) To allow users to perform data entry. Data can be inserted, updated,or deleted from a table using a Form object.2) To allow users to enter custom information, and based on thatinformation perform a task. For example, you may want to ask a userfor parameters before running a report.3) To allow users a method of navigating through the system. Forexample, you may create a form where a user can select a form toload, a report to run, etc.Forms: bound or unboundA bound form has a RecordSource, a table or query to which the form is"tied" or "based". An unbound form does not have a RecordSource,that doesn't mean it can't contain data, but the programmer will haveto bring that data in manually.66

Creating Forms in Microsoft AccessRemember: there is a largenumber of pre-defined forms.The Form options quicklycreate a form based upon atable or query.67

Form Design68

Creating forms – Form WizzardForm facilitating introduction of a new customer to the database:1) Select the datasource2) Select the formfields69

Creating forms – the layout and style70

Creating forms – the form titleThe form can bemodified in DesignView mode.71

Creating forms - adjustments72

Using a formAccess to the existing records inthe table CUSTOMERInput of a new record73

Editing properties of a formProperties icon:Our original goal is to create a form for data purposes. Wedon’t want to grant employees full access to view or editcustomer records. Setting the „Data Entry” property to„Yes” will only allow users to insert new records and modifyrecords created during that session.74

The form after the edition of propertiesOpening the form: no earlierrecords can be seenThe CustID is automaticallyassigned, First name andSurname are typed in.A new record is introducedinto the table CUSTOMER:75

Security differences between Access2007 and earlier versions of AccessEarlier versions:-security levels (Low, Medium, or High),-to run potentially unsafe code or not.Access 2007 disables all potentially unsafe code or othercomponents, regardless of the version of Access that you usedto create the database.Message Bar:You can choose to trust or not trust the disabledcontent in the database.76

You trust the disabled content: Trust the database only for the current session (while thedatabase is open) Click Options on the Message Bar. Trust the database permanently Place the database in a trustedlocation — a folder on a drive or network that you mark as trusted.You don't trust the database:Ignore the Message Bar. When you ignore the Message Bar, youcan still view the data in the database and use any componentsin the database that Access has not disabled.77

Trust a database for the current session1. Start Office Access 2007, and on the Getting Started withMicrosoft Office Access page, under Featured OnlineTemplates, click Assets to open the Assets template.78

2. In the File Name box, type a name for the new database, andthen click Download. Access downloads the database templateand creates a new database, and the Message Bar appears.3. On the Message Bar, click Options. The Microsoft OfficeSecurity Options dialog box appears.4. Click Enable this content, and then click OK.79

Create a trusted locationStart Office Access 2007 (you do not need to open a database tocomplete these steps).Click the Microsoft Office Button, and then click AccessOptions.Click Trust Center, and then click Trust Center Settings.80

Click Trusted Locations.Click Add new location.The Microsoft Office Trusted Location dialog box appears.81

The Microsoft Office Trusted Location dialog box appears.In the Path box, type the file path and folder name of the location thatyou want to set as a trusted source, or click Browse to locate a folder.By default, the folder must reside on a local drive.Note If you want to allow trusted network locations, in the TrustCenter dialog box, click Allow Trusted Locations on my network(not recommended).82

Move a database to a trusted locationIf you have a database openClick the Microsoft Office Button .Point to Save As, and under Save the database in anotherformat, click one of the available options.In the Save As dialog box, navigate to the trusted location,and then click Save.If you do not have a database openLocate and copy your database83

Use a database password to encrypt anOffice Access 2007 databaseThe encryption tool in Office Access 2007 combines and improveson two older tools — database passwords and encoding.Open in exclusivemode84

Encrypt by using a database passwordOpen the database (in Exclusive mode) that you want to encrypt.On the Database Tools tab, in the Database Tools group, clickEncrypt with Password. The Set Database Password dialogbox appears.85

Decrypt and open a database1. Open the encrypted database as you open any otherdatabase.2. The Password Required dialog box appears.3. Type your password in the Enter database password box,and then click OK.86

Remove a passwordOpen the database in Exclusive mode.On the Database Tools tab, in the Database Tools group, clickDecrypt Database.87

Package, sign, and distributean Access 2007 databaseAccess 2007 makes it easier and faster to sign and distribute adatabase. When you create an .accdb file or .accde file, you canpackage the file, apply a digital signature to the package, and thendistribute the signed package to other users. ways to convey trust add only one database to a package signs all of the objects in your databases the process also compresses the package file a security certificate88

Create a self-signed certificateIn Microsoft Windows, click the Start button, point to AllPrograms, point to Microsoft Office, point to Microsoft OfficeTools, and then click Digital Certificate for VBA Projects89

Create a self-signed certificate90

Create a self-signed certificate91

Create a signed packageOpen the databaseMicrosoft Office Button ,point to Publish, and thenclick Package and SignSelect a digital certificate andthen click OKThe Create Microsoft OfficeAccess Signed Packagedialog box appears92

Create a signed packageSelect a location for your signeddatabase package.Enter a name for the signedpackage in the File name box,and then click Create.Access creates the .accdc fileand places it in the location thatyou choose93

Extract and use a signed package1.Click the Microsoft Office Button , and then click Open.2.Select Microsoft Office Access Signed Packages (*.accdc) asthe file type.3.Locate the folder that contains your .accdc file, select the file, andthen click Open.4.Do one of the following: If you have earlier chosen to trust the digital certificate thatwas applied to the deployment package, the ExtractDatabase To dialog box appears. Go to the next step. If you have not yet chosen to trust the digital certificate, anadvisory message appears.94

Extract and use a signed packageIf you trust the database, clickOpen. If you trust any certificatefrom that provider, click Trust allfrom publisher and then clickOK.Optionally, select a location for the extracted database, andthen in the File name box, enter a different name for theextracted database95

Splitting a database96

Hide VBA code from usersIf your database contains Visual Basic for Applications (VBA) (Visual Basicfor Applications (VBA): A macro-language version of Microsoft Visual Basicthat is used to program Windows applications and is included with severalMicrosoft applications.) code, you can hide that code by saving yourMicrosoft Office Access database (database: A collection of data related toa particular subject or purpose. Within a database, information about aparticular entity, such as an employee or order, is categorized into tables,records, and fields.) in the .accde file format. Saving a database as an.accde file compiles all VBA code modules, removes all editable sourcecode, and compacts the destination database. Your VBA code retains itsfunctionality, but the code cannot be viewed or edited. In general, thedatabase will continue to function as usual — you can still update data andrun reports.97

Microsoft Access SecurityThe Security Wizard in MS Access is a very useful tool.But there is more to security than just running the wizard.The 12 steps will describe how to secure a databasefrom start to finish.98

How to secure a database?1. You may secure any database that has been created while joined to theSystem.Mdw.2. Create a new workgroup file(*. MDW)3. Open the unsecured database and create a password for the Admin user.4. Create a new user account that will be the new workgroup administrator, like"PowerAdmin"5. Modify the Admins group by adding the new administrator and removing theoriginal Admin account.6. Re-log into Access as the new workgroup administrator that you createdpreviously.7. Set up a password for the new workgroup administrator.8. Run the Security wizard under Tools Security User - Level Security.9. Create any additional group accounts.10. Create any additional user accounts.11. Set up the database object's permissions.12. Any new databases that you create will already be secure.99

System.MDW fileA Workgroup Information File (*.MDW) storesinformation to authenticate a user. It stores the usernames, group names, and passwords. It does notstore any permission or rights to any database. Itsmain purpose is to verify that a user is really whothey say they are. The permissions of the databaseobjects, tables, queries, forms, etc., are stored ineach MDB file. The System.mdw is the defaultworkgroup filename created when you install MSAccess.100

The Admin userEvery time a user opens the MS Access program, MSAccess attempts to login the Admin user with a blankpassword. If the log in is successful, MS Access continuesloading and the user never realizes that they were loggedin as Admin. However, if the login is unsuccessful, say forexample the Admin user does not have a blank password,then a login dialog box pops up asking the user to specifya username and passwordWhen you create a new User, you will be prompted for aUser Name, Password, and a PID or PersonalIdentification number. A PID can be any text or numbersup to 20 characters long. All three values uniquely identifyeach user.101

The database.MDB fileMS Access verifies that the user name and password existin the Workgroup Information File. After the user has beenverified, the workgroup information file’s job is done. TheMDB itself stores security rights and privileges for eachuser and for each database object.The MDB will have a list of user id’s and the privileges thateach user may have. One user may have the rights to openthe table, but not delete any records, or change the designof the table. Another user may not have any restrictions atall. The MDB file knows each user’s privileges. Thedistinction between the workgroup information file

Databases for beginners 02-DBBA Marek Kręglewski. About the course Steven Roman, Access Database Design & . Home library – table Books . SQL – Structured Query Language Types of queries: –Select q