Microsoft Access 2007 is a very extensive and versatile program; one of its most useful features being its ability to integrate with the Internet. In this blog I will talk about a few important uses of Access integration, and also a few warnings.
From my research, I found the best use of Access on the Web is for sites that contain data which changes often. The most formidable that comes to mind is an online store with an ever changing product catalog, such as Amazon.com. The inventory on such a site is constantly changing, constantly being added to, subtracted from, or modified. Imagine the tedious work that would be involved in editing such a site through basic HTML (or XHTML). It would be ridiculous. If such a site is driven by a database, making these edits would be much simpler. This integration is made possible through a data access page, which is a webpage that is connected directly to the data in your database. Any changes made in that database will be integrated into your online store via this data access page.
Another big part of online stores, besides the online inventory, is the shopping cart. Access comes in handy when you need to perform calculations or apply some sort of algorithm to the data that affects the way it is presented on your site, which a shopping cart is a prime example of. A database is very useful for the ever-changing environment that an online store calls for. It is easy to see why such sites as Amazon.com or Ebay.com would be reliant on database/Internet integration.
During my research, I also found several warnings concerning the use of database driven websites. While this method is excellent for online inventory and sales, it should not be used for the entire format of your site. Formatting and styling of page layouts is much more difficult on text elements derived from a database. Also, articles should not be stored in a database because all of the formatting necessary for such file types is much more difficult through a database. Large Access databases consume much more server resources than most other file formats. Database connections are also more expensive in terms of time and processing power, and many search engines may not be able to "see" your pages if they are completely database driven.
In conclusion, Access integration with the Internet can be very useful and profitable, but must be used wisely. Use it for what is necessary to ensure the quality of design that you require, but use standard web design for everything else. This will help your website to continue to run proficiently, even if it contains large database files.
Monday, April 6, 2009
Wednesday, March 11, 2009
Thinking About Queries
Chapter Six outlines several different types of queries and how to use them. Here is a listing of the queries I learned about and an example of how each can be used.
Parameter Query: This query prompts the user for the value of a particular field. An example would be if I had a table listing restaurants in four different cities I could use this query to list only the restaurants in one of the cities, and each time I opened the query I would be prompted for which city I want to display information on.
Find Duplicate Query: This query is used for finding duplicate values in particular fields. When creating a find duplicate query you can specify which field you want to find duplicate entries in, such as a last name field where some of the last names are listed more than once.
Find Unmatched Records Query: This query is the opposite of the previous one, only displaying entries in a chosen field that are not repeated.
New Table Query: Queries are not tables, although Access allows you to work with the data in queries as if they were tables. This query saves the result of your query as a table. This would be great if you wanted to create a similar table to one that already exists but don’t want to retype all of the values in.
Add Records Query: This is also known as an append query. It can be used to add records to a table when the query you are using shares a common name with the table you want to add to.
Delete Query: This query is used to remove records from a table based on your chosen criteria. For example, if you want to delete all records in an employee table that contains an employees name, this would be the query to use.
Update Query: Let’s say for example that you have a huge orders list table and you need to increase the unit price of one of the products. Rather than scanning the entire table to add a dollar amount to every entry, you can let this query do the work for you
Crosstab Query: This query allows you to summarize the contents of fields that contain numeric values, such as date or numbers fields. You can choose between average, sum, maximum, minimum, and count.
Parameter Query: This query prompts the user for the value of a particular field. An example would be if I had a table listing restaurants in four different cities I could use this query to list only the restaurants in one of the cities, and each time I opened the query I would be prompted for which city I want to display information on.
Find Duplicate Query: This query is used for finding duplicate values in particular fields. When creating a find duplicate query you can specify which field you want to find duplicate entries in, such as a last name field where some of the last names are listed more than once.
Find Unmatched Records Query: This query is the opposite of the previous one, only displaying entries in a chosen field that are not repeated.
New Table Query: Queries are not tables, although Access allows you to work with the data in queries as if they were tables. This query saves the result of your query as a table. This would be great if you wanted to create a similar table to one that already exists but don’t want to retype all of the values in.
Add Records Query: This is also known as an append query. It can be used to add records to a table when the query you are using shares a common name with the table you want to add to.
Delete Query: This query is used to remove records from a table based on your chosen criteria. For example, if you want to delete all records in an employee table that contains an employees name, this would be the query to use.
Update Query: Let’s say for example that you have a huge orders list table and you need to increase the unit price of one of the products. Rather than scanning the entire table to add a dollar amount to every entry, you can let this query do the work for you
Crosstab Query: This query allows you to summarize the contents of fields that contain numeric values, such as date or numbers fields. You can choose between average, sum, maximum, minimum, and count.
Information on SQL
Based on my research on the subject of SQL, here is the information of I have come up with to describe it. As an analogy, SQL is to databases what HTML, or XHTML, is to web design programs. It is the programming language behind the graphical interface that makes up a software program. Although it is not necessary to know
SQL in order to create a decent database, much the same as a knowledge of XHTML is not necessary to create a webpage, it is helpful to known at least a little bit about it in case you need to ever manually edit a project. However, to average user would probably have no need for it.
Here are the basic commands in SQL:
-The SELECT operation is the most common operation in SQL language. It is used to retrieve data from a specific table, or multiple related tables, in a database. An asterisk symbol can be used to indicate that all available columns of a table are to be retrieved.
-The FROM clause. This indicates the source table or tables from which the date for your query is to be retrieved from.
-The WHERE clause. This is used to restrict the number of rows used by a query.
-The GROUP BY clause. This is used to group together rows with related values.
-The HAVING clause. This is used to elmininate rows after the GROUP BY clause has been implemented.
-The ORDER BY. This is used to identify which columns are used to sort the resulting data and in which order they should be sorted.
Here is a simple example of an SQL query that I found on Expedia.com:
SELECT *
FROM Book
WHERE price > 100.00
ORDER BY title;
I don't really see the need for using SQL when such a user-friendly interface is available. But like I said, it is good to know at least a little bit about it in the case that manual editing of queries is necessary.
SQL in order to create a decent database, much the same as a knowledge of XHTML is not necessary to create a webpage, it is helpful to known at least a little bit about it in case you need to ever manually edit a project. However, to average user would probably have no need for it.
Here are the basic commands in SQL:
-The SELECT operation is the most common operation in SQL language. It is used to retrieve data from a specific table, or multiple related tables, in a database. An asterisk symbol can be used to indicate that all available columns of a table are to be retrieved.
-The FROM clause. This indicates the source table or tables from which the date for your query is to be retrieved from.
-The WHERE clause. This is used to restrict the number of rows used by a query.
-The GROUP BY clause. This is used to group together rows with related values.
-The HAVING clause. This is used to elmininate rows after the GROUP BY clause has been implemented.
-The ORDER BY. This is used to identify which columns are used to sort the resulting data and in which order they should be sorted.
Here is a simple example of an SQL query that I found on Expedia.com:
SELECT *
FROM Book
WHERE price > 100.00
ORDER BY title;
I don't really see the need for using SQL when such a user-friendly interface is available. But like I said, it is good to know at least a little bit about it in the case that manual editing of queries is necessary.
Monday, February 23, 2009
Reflection on Fields
This week helped open up my eyes to the extensive possibilities available with the Access software. I didn't find any field type to be difficult to create or even understand. Several might have had me blinking at the screen for a few minutes until their purpose clicked in my brain, but this momentary confusion lasted mere milliseconds. The book is very straight forward and explains things really well. Also, the pictures in it really help to help me make sure I'm on the right track because they correspond with the steps listed. It's actually the most easily understood text book I've ever used. Gone are the days of "boring", 2D spreadsheets. Access offers a multidimensional way to for the user to record information.
Friday, February 13, 2009
More on my Final Project Idea
I have decided to make the purpose of my final project to organize my bills and hash out a budget. The two tables I created for this week's assignment (bills and due dates & amounts) I'm going to use for the project, but I'm also going to create several others. I want a tables for total debt amounts, which will have some bills that are not on the previous two tables since these two are only for bills I am paying at the moment and not ones that are in deferment, such as student loans.
I want this table to show the total amounts due on debts and the companies I owe. The first two tables I created a one-to-one relationship for with the "company name" being the relating field. I'd also relate the third table to these two with the company names that they share. I haven't decided yet what I could use a fourth table for, but I would like it to relate somehow to the debts table. Maybe a table that shows how long it would take to pay off each debt.
I want this table to show the total amounts due on debts and the companies I owe. The first two tables I created a one-to-one relationship for with the "company name" being the relating field. I'd also relate the third table to these two with the company names that they share. I haven't decided yet what I could use a fourth table for, but I would like it to relate somehow to the debts table. Maybe a table that shows how long it would take to pay off each debt.
Saturday, February 7, 2009
My Initial Database Concept
My initial instinct is to create a database with a professional goal in mind. I work in the parts department of a car dealership in town and I'm seeing how Access would be very beneficial towards my job. It would be useful for storing inventory, tracking sales, storing shipping and receiving information. So I might integrate this into my final project as it would benefit not only my class experience but also my work (and might impress my bosses too). While creating a database on my CD/DVD collection or address book would be fun, I don't believe it would really help me realize the real potential that Access has in my professional life.
I could also create a database based on me and my wife's finances. I could document our income every month with everything we have going out in bills, expenses, and savings and create a budget. That's another idea that would be highly beneficial. I could set up a table for income, one for bills, one for savings, one for food, one for gasoline...there are many possibilities. Then I could create reports based on this information to compare our money in with our money out. I could also create a report that shows how much we save each month, and in what avenues we save in, and project what our savings could be like in years to come. Ultimately, my goal with the final project would be to create a database that connects Access to either my professional life or my personal finances. Either way, it will prove to be really helpful.
I could also create a database based on me and my wife's finances. I could document our income every month with everything we have going out in bills, expenses, and savings and create a budget. That's another idea that would be highly beneficial. I could set up a table for income, one for bills, one for savings, one for food, one for gasoline...there are many possibilities. Then I could create reports based on this information to compare our money in with our money out. I could also create a report that shows how much we save each month, and in what avenues we save in, and project what our savings could be like in years to come. Ultimately, my goal with the final project would be to create a database that connects Access to either my professional life or my personal finances. Either way, it will prove to be really helpful.
Wednesday, January 28, 2009
Why Use a Database
The key difference between Access and Excel is that Excel is used for a single table or a single data entry list. Where Access becomes useful is when you need more than one list or more than one table. Excel information is flat or nonrelational. Think of Excel as being a flat 2-d cartoon while Access being a 3D animation.
Access is also more useful with larger amounts of data and categories, and data that is relational. Access is designed to handle much more complex information than Excel.
Access is also more useful with larger amounts of data and categories, and data that is relational. Access is designed to handle much more complex information than Excel.
Subscribe to:
Posts (Atom)
