Access Inventory Exercise for Access 2007 and above

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.

• In moodle, go to the Excel Inventory 4 Sheet Project and right click on your spreadsheet to save it to the desktop.
• 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 change the database name to Inventory and change the path to desktop. Then double click the blank database.
• Import the inventory spreadsheet into Access:
• Choose the External data tab and click on Excel.
• Let it keep the selection to “import the source data into excel”
• Click the browse button and then choose your inventory spreadsheet
• Click ok until it shows your inventory spreadsheet.
• Click finish
• Click Close
• 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 the create tab
• Click the query design icon
• 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 query tools design and then view and then datasheet view to see the items that had more than 2.
• Click the x to close the query, and it will ask you what you want to name the query. Name it  "many item query".
• Click on tables and then double click the inventory table to change the quantity of some items. Also, add one more item.
• Click the x to close the table
• 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 the create tab and the design query icon
• 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.)
• Click the crosstab button – and now a new total row will show.
• Change total row for quantity and total value to sum
• Click the select button to remove the crosstab row
• Click the View button and select  datasheet view to see the totals per category
• Click the x to close and  name this query  "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 Sheet
• Put some description next to the category name.
• Put any number in the sort column.
• File / save
• Import the category spreadsheet into Access:
• Choose the External data tab and click on Excel.
• Let it keep the selection to “import the source data into excel”
• Click the browse button and then choose your category spreadsheet
• Click ok until it shows your inventory spreadsheet.
• Click finish
• Click Close

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.

Category

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 create and then query design
• 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
• Click View and choose 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.
• Click close and name this query  item with category query.

You are done. Upload this to Moodle's Access Inventory Project Upload.