REFERENCE CHEMISTRY DATABASES

1. REFERENCE CHEMISTRY DATABASES Maureen Suryaatmadja Graduate Research Assistant Agricultural Engineering Iowa State University October 31st, 2005

2. Background • Old reference chemistry databases were not well organized. • The reference chemistry databases need to be well documented to support quality control program. • “Sloppy records and poor records maintenance likely reflect poor quality control in other areas of operations and will give that impression to others". (Garfield, 1991).

3. Objective • To develop well organized reference chemistry databases as part of quality control program.

4. Old Reference Chemistry DB (EXCEL)

5. Old Reference Chemistry DB (EXCEL)

6. New Reference Chemistry DB • Structure of Reference Chemistry DB consist of 5 tables for both Soybeans and Corn databases. • The databases were build in Microsoft Access.

7. Soybean Chemistry Structure and Content

8. Corn Databases Structure and Content

9. Step to build new databases • The old reference chemistry databases are organized and separated according to the table that will be build. (in Excel) • Additional information are added to the new table, such as date of the test and Lab doing the test. • The individual files such as calibration, oven moisture, density might be useful to find information about the samples. • The new databases in the Excel are imported to the Access.

10. Step to build new databases

11. Step to build new databases

12. How to import file in Access

13. How to import file in Access

14. How to import file in Access

15. How to import file in Access

16. How to import file in Access

17. How to import file in Access

18. How to import file in Access

19. How to import file in Access

20. Field Design

21. Complete Databases

22. Example (Soybeans Master Table)

23. Example (Soybean Proximate Table)

24. How to link tables • Save Sheet in Excel in “webpage format” (This will be table in Access). • Open Access, Click Get External Data- Link Table. • When the dialog box appears, choose “html document” for the file type. • Select table that you want to connect. • Follow step in the dialog box, before you finish, click “advanced” to make sure the field data type are right.

25. Link Tables

26. Link Tables

27. Link Tables

28. Link Table-specify field data type

29. Link Table- name table

30. How to add/update data • Find the respective files for each table, for example: • To update Master table, use the calibration file for the respective year. • To update Proximate table, use the Proximate Results from Eurofins (In the Completed form Folder) for the respective year. • Copy the files to the Excel Databases according to field available, you may need to arrange the file before making a copy to Excel Databases. • Save the file as the table name in “webpage” format. • For example: If you update Proximate table, save “Proximate” sheet in Excel as webpage format, choose “selection sheet”, save as “Proximate”, it will replace the existed file. • Notes: every table in database is linked to the webpage file, but to update, you need to use Excel database.

31. Update File

32. How to add the data • Another way to add the data is to type the new data directly to the Access. (This is so time consuming).

33. Reference Chemistry Files Location

34. How to make query

35. How to make query

36. How to make query

37. How to make query >20030000

38. Query Result

39. How to make query >=20030001 AND <20040001

40. How to make query

41. Analyze the data in Excel

42. Analyze data in Excel

43. Where are the Reference Chemistry DB located? The Reference Chemistry Databases are available at: \\grainbin\References\Soybean DB \\grainbin\References\Corn DB

44. QUESTION ? SUGGESTION ?