Tutorial 3: Microsoft Access queries and a look at their criteria!

So, I was working on an Access project for my class and I realized how much you can utilize a well developed database. As part of my assignment I had to create a query that required a special "criteria" equation that returns records less than 180 days (6 months) old. I got to thinking, what are the other criteria equations that I could use to enhance my database. My project setting was that of a gym, and I was manipulating raw data of current and former members. Whatever the situation that you're utilizing Access for, the criteria needed for the desired return might be similar. Here is a quick look at one criteria equation that I would like to use to enhance my database.

The first Query I wish to create would be: A list of current members that includes basic info (ID, name, address, birth date, type of membership).  This list could be used as a marketing tool. We can use this list to send promotions to current members on their birthdays and/or promotions based on their type of membership (Individual or family membership). My only issue is that the "Leftdate" field ( If left "blank", member is a current patron). What criteria could I use have my query return all records with blank "Leftdate" fields. Here is a closer look.

 

 

Screen Cap Taken By: Cesar Tobar

 

First we'll start with our query in "Design View". As you can tell I already added all the fields I want it to return. The red circle indicates the field that I want to return with only "blank" records.

Screen Cap Taken By: Cesar Tobar

As you can see the required criteria "equation" for the desired return is simple. "Is Null" does the job for this desired return. As you can see I also sorted our query by Last Name. You might want to sort it by ascending "Birth Date" so it's easier for the marketing department to send promotions as the time passes. You can also see that unchecked the "show" box for "Left Date" field, so  your return does not show a black column.

Screen Cap Taken By: Cesar Tobar

The finished product looks like this. As you can see a simple query could be turn into a report, which could be used as a great tool to generate more business. There is a TON on criteria equations that may be used to return a desired information. This page gives you a very extensive list of criteria and sort equations that may be used along with a simple explanation. Thanks for reading and stay tuned for the next post!

Database Project

I had attempted to use Microsoft Access last year for a project, it was difficult. I could not understand all the different views, structures of queries, forms and reports. Learning about Microsoft Access in this class is definitely something I was looking forward to because this is the one program that I could not figure out on my own. After finishing this project I learned many things: How the program is structured (different views & how to import data), how to create queries, forms reports and how to create reports from a certain source.

One way to improve this database could be: Create a table called "Current Members". We would know who our current clients are so we can send gifts and promotions on special dates (i.e. birthdays). We could have the same table as "6mo" except to change "Left Date" criteria and have the table return members with no "left date". Another way would be,  create a table of all current, individual memberships so the gym can send "family deals" so the whole family can sign up.