handouts

You will use a USDA Food database with 455,907 nutrient records. You can study this database using their application SR-17 SEARCH or by viewing the tables directly in the access database it uses. The sr.mdb file measures 40 MB at first. It can easily grow to 150 MB as you use it. The query below will get you started.

Save time
Copy the prepared file sr.mdb from this location

P:\2000_EXERCISES\Access Example Databases\sr.mdb

to a "My Documents/ACCESS" folder on your computer and skip to "Part B - Using ACCESS" below.

Part A
Download and install
the following application on your PC. (approximately 4 minutes required at SWL)
http://www.nal.usda.gov/fnic/foodcomp/srch/pcs17.exe
Tip: just copy this address and paste it to your address bar, or double click it if it's a link.

To remove it when you are done:
Use your control panel and remove the "USDA HealtheTech SR-17 application".

Lookup a few items using the SR17 SEARCH application.
Example: how many calories in an 2 3/4 inch apple.

Then locate the Database behind this application (sr.mdb). It is in the following folder:
C:/program files folder/USDA-HealtheTech/SR17 Search/

Add this query to the database:
Open the sr.mdb database and create a new QUERY and paste this SQL text into it.
(use the View menu then SQL view then switch back to design view to make modifications).
(Note: do not introduce ENTER anywhere in this string)

SELECT FOOD_DES.Desc, NUTR_DEF.Tagname, NUTR_DEF.NutrDesc, NUTR_DEF.Units, NUT_DATA.Nutr_Val, IIf([Tagname]="ENERC_KCAL",[Nutr_val]/100*30*8) AS [calories per cup] FROM NUTR_DEF INNER JOIN ((FD_GROUP INNER JOIN FOOD_DES ON FD_GROUP.FdGrp_CD = FOOD_DES.FdGrp_Cd) INNER JOIN NUT_DATA ON FOOD_DES.NDB_No = NUT_DATA.NDB_No) ON NUTR_DEF.Nutr_No = NUT_DATA.Nutr_No WHERE (((NUTR_DEF.Tagname)="ENERC_KCAL"));

Notes:

Name this query QUERY1. This query automatically filters to show the DESCRIPTION, TAGNAME, NUTRIENT DESCRIPTION, UNITS of measure, NUTRIENT VALUES PER 100 Grams and Calories Per CUP.

End of installation

Part B
Using ACCESS

Suggestion: Copy this query 18 times and rename them Activity 1 through 18. Then you can replace the criteria and fields as required for each activity.

Tip: to copy the query quickly: Drag the query to a blank area while holding the CTRL key down. Then copy the copy. Then rename them Q01, Q02, etc.

Tip: to rename quickly press F2 and navigate using your arrow keys.

Fieldnames and descriptions:

• DESC The food name.
• Tagname The short name for the nutrient
• NutrDesc The full name of the nutrient.
• Units The units of measure of the nutrient (mg. Kcal etc.)
• Nutr_Val The number of units of the nutrient per 100 grams of food.
• calories per cup The number of calories per cup
  (calculates only if the TAGNAME is ENERC_KCAL otherwise it's blank).

This datase contains over 455,000 records. Not all fields are indexed. "Find" can take several minutes to execute (particularly if you do not use whole field). Merging this many records may take hours on a normal office PC.

Activities:
-Save your queries and documents using the Activity number
-Please only print the first page of any report, since most are very long.

1. Locate the highest source of Calcium.
   (Hint: Replace criteria ENERC_KCAL with CA and sort)
   (Warning: These are not medically sound recommendations. Please don't eat a cup of baking powder just because it's an excellent source of calcium.)
2. Print a list in alphabetical order of all foods that contain between 20 and 80 calories per cup.
   (Hint: for criteria use >20 and <80,>
3. Print a list of foods that contain ASH.
4. Print a list of the top ten foods that contain maximum dietary fibre.
5. List foods that contain over 10 units of IRON per 100 g.
6. Protein is important. What 10 foods contain the most?
7. What foods contain no water?
8. How many nutrient descriptions are there (NutrDesc field)? Answer 127.
9. How many units of measurement are used (Units field)? Answer 8.
10. How many manufacturers are represented in this database? Answer 38.
    Note: the ManufacName field is in the FoodDesc Table.
11. How many of the nutrients involve "Kraft Foods, Inc.". Answer 1864.
    Try sorting using the query, then try sorting using the AZ button, are duplicates hidden?
12. How many common names are used in this database. Answer 82.
    Note: the ComName field is the FoodDesc Table.
    Hint: just group this field.
13. Print a list showing the Description, common name and scientific name of the foods, but only if the scientific name exists. Then send it to Excel and Word for future use.
    Note: the scientific name field is SciName in the FoodDesc table.
    Try Export the query result. Does it work?
    Try Office links to Excel or Word. Does it work?
    What is the difference between Exporting and Office link?
    Try making a table from the Query and export. Does this work?
14. How many scientific names use the word "Citrus". Answer 6.
    What are their Descriptions?
    Send them to me at aded21@swlauriersb.qc.ca in the body of an email.
    Tip: Just copy and paste them to your email.
15. How many of the data in the survey field are Yes. Answer 209446.
16. Export the 455000 item list to Excel. What do you expect will happen? What actually happened?
    Answer: it won't fit.
17. Make some labels for your kitchen Spices And Herbs showing the DESCRIPTION. You may merge with WORD or make labels in Access. Choose a nice font. Answer 58 labels.
18. Publish a 200 page report listing the caloric value of each food item. Sorted in Alphabetical order within each Food Group (field: FdGr;_Desc). Use WORD and the usual report format*.
    Prepare the document for double sided printing.
    Note: there should be less than 7000 items in your report. Test merge the first 100 records to see how long it takes, then proceed with the full merge.
    Save, but do not print your report.
    *Report format: Create an appropriate title page, centered vertically. All other pages must have appropriate headers and footers. Paginate at the bottom right.