Access Inventory Exercise for Access 2003
1. First, get your Excel inventory spreadsheet and take away all the calculations
- In Moodle, go to the Excel Inventory 4 Sheet Project and right click on your spreadsheet to save it to the desktop.
- Open the inventory spreadsheet in Excel and remove all the total rows and all the columns after Unit Value.
OR create a new spreadsheet with 20 items with these columns: Item#, Item, Category, Quantity, Unit Value
OR use this inventory spreadsheet: http://home.adelphi.edu/~pe16132/csc170/access/inventoryforaccess.xls
2. Import your inventory spreadsheet into a new Access Database.
- Start the Access program: Start / program / Microsoft Access. (You need to use the library computer for this if you do not have Microsoft Access.)
- Create an Access Database: Choose File and then New and then double click the blank database. Then change the database name to Inventory and change the path to desktop and save.
- Import the inventory spreadsheet into Access: file / Get external data / import and then change the file type to excel and browse to the file. Double click the file to import it and just accept all defaults with next.
- See the table values: Double click on the table that was created to see all the values that were in the spreadsheet.
- See the table design: Right click on the table and choose design view. See all the fields and the data type chosen.
3. Work with queries: - Notice how the query is a view on the table and reflects all changes in the table.
List the item #, item, category and quantity of every item that has at least a quantity of 2. Sort by category. Save the query. To do this:
- Click
queries on the left:
- Click
create query in design mode:
- Click Tables tab and "Simple List" or whatever your inventory list is called:
- click close
- drag down the item#, item, category and quantity to the columns below. In the criteria row of Quantity, write >2. In the Sort row of category, hit the down arrow and choose Ascending. Your query design will look like this:
- Choose file / save as and name this "many item query".
- Choose view and then datasheet view to see the items that had more than 2.
- File / close
- Click on tables and then double click the inventory table to change the quantity of some items. Also, add one more item.
- File / close
- Click on queries and double click the "many item query" again.
- See that it changed to reflect what you typed.
4. Work with a grouping query to get group totals: To get the totals for each category: (Notice how Access can easily get group totals.)
- click query
- click create query in design view
- Click Tables tab and "Simple List" or whatever your inventory list is called
- Click close
- Drag down category, quantity
- Calculate the total value as =quantity * [unit value], and then change expr1 to total. (If you find this step difficult, just leave the total off your report.)
- Query / crosstab – and now a new total row will show.
- Change total row for quantity and total value to sum
- Query / select query to remove the crosstab row
- View / datasheet view to see the totals per category
- File / save as and name it "total per category"
It will look like this:
Steps 5 & 6 are optional They will help you understand what we will cover in the next classes, but you do not have to do them to get 100. If you find I go too fast in class when presenting a new program, or you find Access very confusing, it will help to do these at home first.
5. optional - Add a category table in Excel. The categories should match the
categories in your inventory list.
. You can enter a list of categories in Excel and then import them.
- In Excel, type Category, description and sort across the top
- Fill in every category name that appears in the inventory list
- Put some description next to the category name.
- Put any number in the sort column.
- Name the sheet tab Category
- File / save
- In Access: file / Get external data / import and then change the file type to excel and browse to the file. Double click the file to import it and just accept all defaults with next.
The Category table you will create will have the following fields: (Notice how you have have 2 tables in Access)
|
Field |
Type |
Size |
|
Category |
Text |
15 |
|
Description |
Text |
100 |
|
Sort Order |
Number |
int |
6. optional - Create a query that shows each of your items with the category description.
|
|
Description |
Sort
order |
|
Item
# |
Item |
Category |
Quantity |
Unit
Value |
Total
Value |
|
Item
# |
Item |
Category |
Quantity |
Unit
Value |
Total
Value |
|
Item
# |
Item |
Category |
Quantity |
Unit
Value |
Total
Value |
Category
ID |
Descr- iption |
|
Item
# |
Item |
Category |
Quantity |
Unit
Value |
Total
Value |
Category
ID |
Descr- iption |
- click query
- click create query in design view
- Click Tables tab and "Simple List" or whatever your inventory list is called
- Also click categories. Now you have 2 tables on the top.
- Click close
- Click on categories in one table and draw a line to categories in the other table.
- Drag down item, category, quantity, category name, sort
- On the sort row, choose ascending under the "sort" column
- View / datasheet view to see every item with its category. Note that you only had to type the category name a few times to get it to repeat in your query.
- File / save as item with category query.
You are done. Upload this to Moodle's Access Inventory Project Upload.