Running an INSERT statement using MS ACCESS

• Select 'Queries' on the left hand side under 'Objects' in the main window of the HealthyGym database.
• Select the set square 'design' icon on the top toolbar.
• Close the pop up window without adding any tables. click on 'SQL' on the left hand side of the top toolbar.
• Cut and paste the following command in one line in the SQL window. Remember that you will have to add '#' on both ends of the date 12-01-06 (note that memberID is taken care of by MS Access since it is set to autoincrement).

INSERT INTO GymMembers (MemberName,MemberAddress,MemberExpire,MemberGender) Values ('Alex valoha','512, Megabyte Blvd',12-01-06,'F')

• Click on the '!' icon on the top toolbar.
• Answer 'Yes' to the confirmation pop up window.
• Now, close this query without saving it by clicking on the cross on the top right hand side of the window and clicking 'No' on the pop up window that appears and asks you if you want to save the Query.
• Click on 'Tables' under 'Objects' and double click on the GymMembers Table
• The new row will appear in the table in the following way

Screen Shot 6b: Adding a new line using SQL

Since you entered the values of every each column in the new row in the order in which the columns appear in the table, there is no need to specify each column by name. If you want to try the following command on MS Access, substitute '$$$MemberID$$$' with the last (and highest) MemberID PLUS 1, and alter other values if you want - the MemberID column is an autoincremented field. MSAccess finds +1 for you and inserts the row when you use a command that includes column names but you have to calculate the next MemberID to be entered manually if you choose to insert without column names. Also, don’t forget to enclose the date within '#' and paste the entire command in one line.

INSERT INTO GymMembers Values ($$$MemberID$$$,'Alex valoha','512, Megabyte Blvd',12-01-06,'F')

Entering data into the table directly using the GUI is very simple; you just double click on the table name to open it and start entering data. Since the ID field has been set to autoincrement in MS Access, you just have to type in data in the other fields. Usually, people use Forms that validate the data entered when the person filling the form clicks 'Submit' to insert data into databases. A program processes the submitted form and talks to the database; the Insert Statement is usually sent to the database through this program.

The following command sets the cost of all services to $50. Do not try it out.

UPDATE GymService SET ServiceCost=50

The value to be updated appears after the SET keyword. If you want to update more than one column, you separate them by commas as follows

UPDATE GymService SET ServiceCost=50, ServiceName='AllInOne'

The following command deletes all data from the GymService table. Do not try this one either.

DELETE FROM GymService

There are almost no situations where you would need to update all the values in a column or delete all information from a table. Update and delete statements are more complicated than insert statements because we first have to find the value or set of values we need to change or remove. We do this by using the WHERE clause with its operators just the way we did in the last chapter to select data. Suppose the Gym Attendant just found out that Alex Valoha is a Man, not a woman, you could alter his record in the following way (remember to substitute * for % if you run this in MS Access):

UPDATE GymService SET MemberGender='M' WHERE MemberName LIKE 'Alex val%'

Apparently, Alex's last name got typed with a lower case 'v'. This is easy to fix.

UPDATE GymService SET MemberName ='Alex Valoha' WHERE MemberName Like 'Alex val%'

Delete the new row and any others that you added to practice adding new data using commands like the following (this is necessary because these members do not have records in the MemberPayment and MemberService tables - a discrepancy we will address in chapter 8):

DELETE FROM GymService WHERE MemberName Like 'Alex%'

Suppose the alphabet 'A' was picked up in a membership upgrade lottery for women; ladies whose names begin with an A get free membership for a year. Then the following command will update these records. Remember to use the MS Access format (* instead of %, dates enclosed in #):

UPDATE GymService SET MemberExpire=01-01-08 WHERE MemberName LIKE '% A%' and MemberGender='F'

You can practice update and delete commands by inserting sample data and then playing with it. In the next chapter, we will look at more complex ways of analyzing data.