Separate Speaker Notes to accompany the asgn1 presentation for CIS17:
I am going to prepare a file/table in the access database to contain the information for the first problem in the first weeks assignment.
This presentation uses Access 2000 - you could also use Access 2002/XP. Note that the database has a .mdb extension which is the standard extension for Access databases.
Again, if you are using a different version, things may be slightly different.
In access I use text field. Some other languages might call it character or string.
I believe ISBN is a 10 digit number with three hyphens. Adding the hyphens makes this a character field instead of a numeric field. In addition, some ISBN numbers have letters especially in the last field. Another reason to make it a character field.
In the bottom image of the screen, I am entering title which I am also making character - this time I decided 35 worked. I soon discovered that it should have been bigger, but rather than change the structure, I shortened the titles. I will now enter the other information in the same way.
Note that I made author 25 and topic 15 characters.
Access requires you specify a key when you create a table. If you do not, it establishes one for you numbering the first record 1 and the second record 2 etc. The key is supposed to be a unique string of data that is not duplicated. That makes the ISBN number a perfect candidate. More about keys later - for now just accept that things will work better if you establish a key on a file that will contain unique data.
I am now saving the table structure. This is the design of the table that tells what data is going to be stored here. Now I can create records following this structure. Note that every record I create will conform to the table structure that I have just designed.
Once the table has been designed and created, I now need to enter data into the table.
I entered 12 records (the 6 shown on the assignment and 6 more to give more data). Note that the data is entered into the structure of the table.
This is known as populating the table.
A query is looking at information and asking to see all records that meet a specific criteria.
After I click on Add to add the booklist table, I then click on the Close in the Show Table popup screen.
In the query, I selected that I wanted to see all of the fields so it showed me all of the records in the table with each field on the record showing. This means by clicking on the view icon I go into view mode to see the records and by clicking on the design icon I go back into design mode if I want to make changes to or just see the actual query I did.
I saved the query as all and all now appears under my query list.
In this example, I selected three fields to see: isbn, title, topic. I then put under the intersection of Criteria and topic the entry ="Oracle". This means that I only want to see those records where the topic is Oracle. As you can see, 4 records meet the criteria and are displayed. This means I have the data to access my file/table and get records of a specific topic.
I did not show it here, but I saved this query under the name oracle.
Note that I am testing for equal here. I could also test fields for greater than or less then.
This shows all the records that meet the criteria title > "G" which actually means they they have to start with something greater than G. Not very useful, but I wanted to show greater than with a text field. The next slide will show it with a numeric field.
This shows all books where the year of publication was less than 2000. Note that when you are dealing with numeric data, you do not put quotes around the value.
Contrast to ="Oracle" and >"G".