2 Database Objects

2 Database Objects-PDF Download

  • Date:07 Jul 2020
  • Views:5
  • Downloads:0
  • Pages:32
  • Size:1.20 MB

Share Pdf : 2 Database Objects

Download and Preview : 2 Database Objects


Report CopyRight/DMCA Form For : 2 Database Objects


Transcription:

Access Basics for Programming Database Objects Crystal Jan 8 2008 2 2. Datasheet View of a Table, Each row of data is a record Each column is a field Within a record the field describes an attribute. of the record The field names or captions are displayed across the top as column headings In my. opinion you should not use captions in table definitions because they mask the real names In other. words it is best to see the real field names when you look at the datasheet view of a table. Figure 2 1 Datasheet View of a Table,Design View of a Table. When you design a table you create the field names specify what type of data the field will hold. like text number or date create a description that explains more about the field and specify other. properties such as size format default value and whether or not the field will be indexed. Each table should have one field or a combination of fields that will be unique to each record This. field or combination can be designated as a Primary Key It is common to use an AutoNumber for. the primary key An AutoNumber is a special form of the Long Integer data type whereby Access. automatically assigns the values Although to define a field to be a Long Integer you use Number. as the Data Type and Long Integer as the Field Size Access does actually treat a Long Integer as a. specific Data Type, Copyright 2008 Crystal Long strive4peace2008 yahoo com. Access Basics for Programming Database Objects Crystal Jan 8 2008 2 3. Figure 2 2 Design View of a Table, Without tables a database normally has little value as this is the where data is typically stored. exceptions are database properties INI and other external files and the registry Designing tables. is discussed in greater detail in the Normalization section of this document. The most common use of a Query is to SELECT information specify criteria filter and sort. Queries show information from one or more tables as records rows and fields columns Records. can be sorted they can also be filtered so that only certain records show For example let us assume. you are printing a Denver phonebook from a database that contains information for the whole. country you can specify criteria to limit records to those whose city is Denver Then you can sort. the list alphabetically by last name then first name. If you frequently find yourself looking at the same information perhaps for different criteria such as. a date range or a region it would be good to define a Report instead of using a Query to show the. data When a Report is based on a Query that query is the RecordSource. The best object to use for changing data is a Form A Query can be used for a form RecordSource. Word of caution even though a query can pull from many tables good practice dictates that you. should only change data in one table on each form If you wish to add or update data in a related. table at the same time it is best to use a form subform subsubform scenario. Another thing a query could be used for is to specify the RowSource of a combo or listbox As for. Properties in most instances where you can use a table you can also use a query and vice versa. This is why tablenames and querynames cannot be the same. Queries give you a way to quickly answer ad hoc questions list all sales for a particular quarter. identify duplicate information so you can fix it find unmatched records so you can fill in missing. data switch translate rows into columns using a Crosstab Pivot in Excel so you can see for. instance sales in columns under the months derived from daily amounts that are being stored. In addition to selecting data queries can perform actions to manipulate data such as Delete get rid. of Records Update modify records Append add records and Make Tables. Copyright 2008 Crystal Long strive4peace2008 yahoo com. Access Basics for Programming Database Objects Crystal Jan 8 2008 2 4. The statement defining a Query is stored using SQL Structured Query Language Many SQL. statements can be displayed graphically on the QBE Query By Example grid in Design View. As with tables opening a query that selects information uses the datasheet view to display the. information, In this example three tables have been used to show information.
1 t PEOPLE has fields describing a person such as first name last name gender or main name for. company Each record is uniquely identified using a field called PID People ID. 2 t Types is used to categorize records such as Friend Family Professional Auto Hospital and. Art Supplies Each record in this table is uniquely identified using a field called TypeID By storing. a corresponding TypeID in the People table a short number can be stored in the People table and the. longer text can be displayed at any time from the Types table The Types table is commonly. referred to as a lookup table, 3 t eAddresses stores email address It is linked to the People table using a field called PID which. is short for PeopleID Since PID is such a common field it is abbreviated. Datasheet View of a Select Query, The datasheet view of a query looks like the datasheet view of a table. Figure 2 3 Datasheet View of a Select Query, In Figure 2 3 the Fullname column shows a combination of. In the case of a human this is the last name For a company it is the company name. Copyright 2008 Crystal Long strive4peace2008 yahoo com. Access Basics for Programming Database Objects Crystal Jan 8 2008 2 5. First name and Middle name or initial, Some human records have middle name or initial specified and some don t Companies don t. have first names or middle names so that information is ignored as well as the commas and. spaces that act as separators,Some have suffix Jr III and some don t.
In the second column type of contact the word Typ is used since Type has a special meaning to. Access reserved words will be discussed later,The third column shows the email address. Design View of a Select Query, We are starting you out with a slightly complicated example the simple one comes next smile. Figure 2 4 shows the QBE Query By Example grid and fieldlists for the data displayed in the. select query of Figure 2 3,Figure 2 4 Design View of a Query. Copyright 2008 Crystal Long strive4peace2008 yahoo com. Access Basics for Programming Database Objects Crystal Jan 8 2008 2 6. SQL View of a Select Query, The SQL statement is what Access stores internally to render a query. Figure 2 5 SQL View of a Select Query, When you specify the calculated field to be sorted this is the SQL that Access will construct It is more.
efficient however to use this as your Order By clause. ORDER BY MainName FirstName MiddleName, The semi colon at the end of the SQL statement indicates the end of the statement. Calculated Field, This is a calculated field and will be called Fullname. MainName suffix FirstName MiddleName AS Fullname, This equation concatenates combines information from main name first name middle name and. suffix into one place Literal values such as spaces and commas are concatenated where needed. Note that literal values can be enclosed in single or double quotes the quote marks just have to be. Definition Concatenate is used in database terminology to mean to combine or connect or link. together In databases one reason it is important to separate data into the smallest possible unit is. because it is much easier to concatenate than it is to parse. Definition Parse is a term meaning to separate If you store someone s full name in one field and. decide you want just the first name you would need to parse that out from the whole field. Copyright 2008 Crystal Long strive4peace2008 yahoo com. Access Basics for Programming Database Objects Crystal Jan 8 2008 2 7. Difference between and,and are both Operators, The standard Concatenation Operator is ampersand If a term that is concatenated is Null has. no data unknown all terms will display if you use ampersand. The Addition Operator is the plus sign but even if one of the terms has a value the result will. be Null if any term is Null kind of like multiplying by 0 As in math what is enclosed in. parentheses will be evaluated first,Null anything Null.
Null anything anything,something anything something anything. something anything something anything, no difference because both of the terms have a value. Null Lastname Lastname,Null Lastname Lastname, in the second case the parentheses do not make a difference each term is concatenated and note. the space in the result before Lastname, Do you see the difference between using and using For instance if you want to add a space. between first and last name but you are not sure that first name will be filled out you can do this. Firstname Lastname, What is in the parentheses is evaluated first then it is concatenated to what comes next.
You might also want to do this,Firstname Middlename Lastname. Combining and in an expression gives you a way to make the result look right without having to. test if something is not filled out, What if firstname is filled but nothing else There will be a space at the end Usually this is not a. problem but if you want to chop it off you can wrap the whole expression in the Trim function. which truncates leading and trailing spaces,Trim Firstname Middlename Lastname. Copyright 2008 Crystal Long strive4peace2008 yahoo com. Access Basics for Programming Database Objects Crystal Jan 8 2008 2 8. Figure 2 6 Fieldlist A fieldlist is a list showing fields that are in a table or query. The titlebar shows the name of the table or query and its fields are listed. To add a fieldlist to a query,Click the Show Table icon. from the menu choose,Query Show Table, A join is a line between two fieldlists showing how they relate to each other.
In the query design example the line between the table with People information and the table with. eMail addresses is an equi join aka INNER JOIN This means that only people records that also. have an email address will be included, When you Right Click on a join line you can choose to edit the Join Properties You can also. Double Click a join line to bring up the properties window directly. Figure 2 7 Join Properties Equi Join, Copyright 2008 Crystal Long strive4peace2008 yahoo com. Access Basics for Programming Database Objects Crystal Jan 8 2008 2 9. An equi join is not always desired Another fieldlist in the example shows the type person or. company What if the type is not filled out TypeID field in the people table but we have an email. address In that case we still want the person and the email address to show How do we do that. We can use an outer join which is also called a Left join or a Right join depending on how the SQL. statement is written,Figure 2 8 Join Properties Right Join. Data Example, To better understand what is happening with the Join lines assume that we have the following data. in our tables,t People t Types t eAddresses,PID Fullname TypeID TypeID Typ PID eAddress.
768 Camfield Greyson D 48 Friend 35 Bruce Camou yahoo com. 769 Camfield Haden E 48 50 Professional 36 Lucille Camou bellsouth net. 35 Camou Bruce Matthew 48 54 Store 59 Joel Carson sealane com. 36 Camou Lucille 48 55 Home Suppl Serv 60 Vanna Carson earthlink net. 1214 Campbell Car Wash 58 56 Sports 65 Ronna Chase somewhere us. 970 Campbell Joslyn B 48 57 Books 66 Lemuel Chase mybighouse net. 971 Campbell Laura 58 Auto 731 Marco Cheveraux cox net. 709 Cannon Dalton M 48 69 Music Store 769 Haden Camfield hotmail com. 710 Cannon Quinn G 48 70 Bldg Supplies 899 Evan Carlson earthlink net. 898 Carlson Dylan H 48 71 Dance 970 Joslyn Campbell someschool org. 899 Carlson Evan G 48 76 Health Store 971 Laura Campbell Soupy net. 1253 Carlton Camp Lodge 81 77 Games 1006 Meredith Carroll yahoo com. 1340 Carpets To Go 70 78 Computer Electronics 1007 Kaitlyn Carroll Company com. 1007 Carroll Kaitlyn 50 79 Flowers 1253 Vacations CarltonCampLodge com. 1006 Carroll Meredith V 50 80 Coins Not all people shown have email. 59 Carson Jr Joel 48 81 Hotel Motel,In this example Campbell Laura. 60 Carson Vanna S 48 Bold means that this TypeID does not have a type specified but. is used in the list of people she does have an email address so. Red indicates there is no corresponding that are shown. PID in the email addresses table so this she will still show in the query. name will not show in the query, Copyright 2008 Crystal Long strive4peace2008 yahoo com. Access Basics for Programming Database Objects Crystal Jan 8 2008 2 10. The results of our earlier query example run on this data would net the following results. Fullname Typ eAddress, Camfield Haden E Friend Haden Camfield hotmail com. Camou Bruce Matthew Friend Bruce Camou yahoo com,Camou Lucille Friend Lucille Camou bellsouth net. Campbell Joslyn B Friend Joslyn Campbell someschool org. Campbell Laura Laura Campbell Soupy net,Carlson Evan G Friend Evan Carlson earthlink net.
Carlton Camp Lodge Hotel Motel Vacations CarltonCampLodge com. Carroll Kaitlyn Professional Kaitlyn Carroll Company com. Carroll Meredith V Professional Meredith Carroll yahoo com. Carson Jr Joel Friend Joel Carson sealane com,Carson Vanna S Friend Vanna Carson earthlink net. Carter Home Appliance Home Suppl Serv CustomerService CarterHomeAppliance com. Only people that have an email address are displayed regardless of whether or not their type has. been specified,Simple Query Example, Now that we started you out with a complicated query that pulled data from three tables and used. If you frequently find yourself looking at the same information perhaps for different criteria such as a date range or a region it would be good to define a Report instead of using a Query to show the data When a Report is based on a Query that query is the RecordSource The best object to use for changing data is a Form

Related Books