Instructions:
Download the Excel spreadsheet - VAPopProjections Localities 2020-2040 2017. It contains data about the Commonwealth of Virginia. (This file is an excerpt from Weldon Cooper Center for Public Service, March 2017.)
1.Open the spreadsheet and review/study the contents.
2.Using MS Access 2010, 2013 or 2016, Create a Table (Population) from the column headings (row 4) provided in the spreadsheet and format the data as you see it in each column. Exclude Geography Name for this table. Primary key will be the FIPS code.
3.Create a Table (FIPS Code). It will contain two fields: FIPS Code should be the primary key and Geography Name.
4.Import the spreadsheet data into your newly created tables.
5.Create the following queries:
a.List only records that are a County. Sort ascending by Geography Name. Do not show FIPS Code. Include a field that calculates the difference between 2020 Total Population and 2040 Total Population. Name the query Counties.
b.List only records that are a City. Sort ascending by Geography Name. Do not show FIPS Code. Include a field that calculates the difference between 2020 Total Population and 2040 Total Population. Name the query Cities.
c.List all records from the population table showing FIPS, Geography Name, and 2030 Total Population. Sort descending by 2030 Total Population. Name the query 2030 Populations.
6.Create one form to enter data for the Population table. Include all five (5) fields.
7.Create one report to provide the following:
a.Use the Population and FIPS Code tables.
b.Create a relationship by joining the two tables using FIPS field as the relationship.
c.Exclude the 2020 Total Population and FIPS Code fields from the report.
d.Provide the data in the following order from left to right:
i.Geography Name
ii.2030 Total Population
iii.2040 Total Population
iv.Sort ascending by Geography Name.
v.Set the print to landscape.
vi.Add your name and current date to the footer of the report.
vii.Place an appropriate title on the report.