I have been assigned to create databases for survey questionnaire.
I use R to store survey information in a semi-complex data structure with formats, questions, response levels, etc. This makes it easy to process and clean the data as well as generating reports. However, I now need to go one step further and create a blank database so that data entry forms can be generated.
To do this, I need to be able to generate blank databases from R, based on specifications, that have "constraints" on the types of data that can be entered. In R, this is similar to the factor datatype, in Access it is the lookup table.
If I create a blank dataframe in R and use the sqlSave command, however, the factor information does not carry over to the access database.
For instance:
library(RODBC)
con <- odbcConnectAccess2007('blankdb.accdb')
survey <- data.frame('id'=integer(0), 'age'=integer(0), sex=factor(levels=c('Male', 'Female')))
sqlSave(con, survey)
survey.copy <- sqlFetch(con, survey)
levels(survey$sex)
levels(survey.copy$sex)
Surveys have coded levels for responses. I would like to be able to create tables for data entry that have coded levels for responses, e.g. "What level of education have you achieved?" 0) No certificate 1) High school graduate / GED 2) College graduate 3) Postgraduate degree.