Using Access to Select Data

Using Access to Select the Data You Want

The data that we want to analyze is on the sheet CrnPtTeomMet of the Exercise 3 spreadsheet. As you can see, it will be problematic to use Excel to calculate average values over sub time periods, while excluding flagged records from the calculations. Instead, we will use MS Access to calculate averages over months, years, quarters, etc. and will then chart these values.

Open a new blank MS Access database. Save this database on your jump drive, named Exercise 3 with your initials.

Click File>Get External Data > Import:

Navigate to the Exercise 3 spreadsheet, and change the dropdown box to include Excel files so that the Exercise 3 spreadsheet is shown:

Click Import, to open the import dialog box:

Select the sheet “3B3-CrnPtTeomMet” and click Next.

Tell Access to put the data in a new table, click Next, and tell Access to index (duplicates ok) the date and time fields, as follows, with the other fields not indexed:

Click Next, and tell Access to add its own primary key:

Let Access name the table, and click Finish. Now the table will appear in the list of tables:

Open the table to make sure all the data is there, and verify that there are the same number of records in the excel table and the access table. (9220)

Open the New Query Wizard:

Click on the double chevron to select all the fields, then click the single back chevron < to de-select the ID field.

Click Next, then Summary:

Click on Summary Options, and select Average for all parameters except precipitation for which select Sum, and Count:

Click OK, Next, and Month for Group Dates In Query:

Click Next, Open the Query, and Finish. The results of the query will open:

Scroll to the right to see the values and you can see it is very ugly, with negative values, and too many significant figures:

The negative values are due to the inclusion of the values that were rejected, so to fix this, open the query in Design View:

Which opens up the query design pane:

Select the Time, and delete it, and add Is Null to all the qualifier fields, so that the averages are calculated only for those records that have not been flagged:

There are a total of 10 qualifier fields to add Is Null to, and also since their values will be null, uncheck the Show box:

Run the query by clicking the ! sign:

You will see the results of the query, looking something like:

Ignore the significant figures (that is easy to fix in the query design by changing each column’s Properties, Format, Fixed, and set it to 1 or 2 digits) but for now check that the values make sense. Save the query.

NOTE THAT, like excel, text values are left-aligned, and our dates have become text, which will not help us chart these values by date in an excel XY chart! To keep a field that is date and have it exportable as a date (rather than a text) field, open in design view (click on the drafting triangle-thing that looks like a t-square):

To open the design view:

add a column by clicking on Insert, Column:

Which opens up a new blank column, and then click on the Field, which gives you a drop-down list,

And select date, then in the Group By row change Group By to Min:

This will show us the Minimum date in each quarter, but we could have chosen First as well, it will just return the first of the month, or we could have chosen Max or Last and it would have returned the last day. We just want the date formatted as date (not text) for when we export into excel.

You can see that Access put the dates in a weird alphabetical order, but that is okay. All we are interested in is getting this into Excel where we can graph it. (You can sort on any column in datasheet view.)

Save the query again, adding _by_month and your initials to the name.

With the query open, click on File > Export >

And tell Access to export it as an excel file, onto your USB drive, and let Access name the file:

Now open that excel file and graph the data, using the chart wizard and setting each parameter as a different series in an XY chart, starting with following similar steps as we did previously.

It may look pretty bad at first:

But put the dates in order by clicking on Sort in the date column and it will improve, by selecting the entire table, then clicking on Sort Ascending:

Now the chart, with one series, should look something like this:

Add relative humidity as a 2nd series:

Select the RH data, click Copy, then select the chart, then click Paste Special, Add cells as new series, values (Y) in columns, and if you included the header in your Copy selection, labels in first row, and make sure categories in first column is Unselected (this is an XY chart and we don’t want the dates charted as categories, but as values.)

After adding humidity, you should see a fascinating graph like this. What does it tell you?

You can add each series as you just did, evaluating whether the new series helps you understand your data or not. Just click the series to remove it if it doesn’t give you insights about your data.

Pg. 2 of 16

Using Access to Select Data