Creating Realistic Household Data Simulation Using VBA in MS Access

1. Module 1 • Read-in Data • Hardcoded distribution* • 2010 Census Summary File 1 • Using VBA macro in MS Access on raw SF1 file • P12 Pop. By Sex by Age • P16 Pop. Over/Under 18 by Sex • H13 HH Size • P29 HH Type by Relationship • P43 Group Quarter • Populate lists of male and female, adults and children • Form all households (HH) of different sizes and types. • Assign a Traveler Type to every resident • Populate all Group Quarters (GQ) in Census Block. • Iterate over # of HH of each size • Choose Fam or Non-Fam HH • Choose main Hhersex • Draw remaining members from HH relations distribution • Iterate over # of people of each age ranges of each sex • Uniformly distributed ages in ranges • Iterate over # of people in each GQ by age and sex • Preset Traveler Type for those whose GQ determines it. • Based primarily on age and HH type • Distribution based on statewide stats of employment and enrollment Legend: • Write these attributes to a CSV, the NJ Resident File • Assign an Income Bracket to each Working Resident Parsing text data • HH income sampled first with dollar amount • Then divided among workers in HH • Converted to Bracket indicators External data • Each resident is given a unique person ID • Each resident also given a household ID • Household members have the same HH ID and are in consecutive rows • Read-in Household Income Data Process Titles • Family Household Incomes • Non-Family Household Incomes • Family and Non-Family fields only • Only estimates, not margins of error Brief Process Description • 2010 ACS 5 YR