Access: Designing a Simple Query


One of the most powerful ways of analyzing
your data in Access is by creating a Query. Running a query is like asking your database
a question. A query can retrieve data from a single table, or from multiple tables, and
it all depends on how complex your question is. In this video, we’re just going to focus
on making a simple query that uses a single table, and this is sometimes just called an
advanced filter. Let’s suppose our bakery has an upcoming event,
and I want to get a list of all of our customers who live nearby so we can send them invitations.
A query is going to be able to go into the Customers table and find the names and addresses
of the nearby customers. To create a query, go to the Create tab, and
then click the Query Design command. And then you’ll need to select the table or tables
that you want to retrieve the data from. We’re just using the Customers table for now. Click
Add, and then you can close this window. When working with queries, there are a couple
of different views that we’re going to use. If you click on the View drop-down arrow,
you can see that we are in Design view. When we finish the query, we’re going to be viewing
the results in Datasheet View. And you can use this menu to switch views whenever you
want. The Customers table appears as a small window
in the Object Relationship Pane, which is this area here. And it has a list of all of
the fields that are in this table, such as First Name, Last Name, and Street Address.
I’m going to resize it so I can see all of the fields. What we’re going to do is double-click each
field that we want to include in the query. We need the customer’s name and address, so
we’ll double click on First Name, Last Name, Street Address, City, State, and Zip Code.
And we’re not emailing or calling them, so we don’t need those fields. Each field now appears in the area below,
which is called the Design Grid. Below the field names is the Table row, which
shows which table each field comes from, and this will become more important when we start
dealing with multiple tables. And we’re going to modify some of these blank cells to refine
our query. We want the results to be sorted by last name,
so in the sort row, we’ll click the cell under Last Name, and a drop-down arrow will appear.
You can click it and then choose how you want it to be sorted. Next, we’re going to use the Criteria row
to filter the records so that it’s only showing the customers who live nearby. First of all,
we want to send invitations to everyone who lives in Raleigh, so we’ll type Raleigh in
the City column. And since we’re looking for an exact match, it will need to be in quotation
marks. So far, this query will show all of the customers
who live in Raleigh, but none of the customers in other cities. But one of the zip codes
in Cary is close to Raleigh, and let’s say we’d like to send those customers an invitation
as well. To do this, we’re going to need to add another criteria under zip code. Instead
of using the Criteria row, we’re going to use the row below it, which is the ‘or’ row.
We’ll type 27513 in quotation marks. So now the query will show customers who are in Raleigh
*or* in zip code 27513. The reason why we didn’t put them on the same
line, is that it would then find just the customers who meet *both* criteria. In other
words, the customer would need to live in Raleigh *and* in zip code 27513. In some cases,
you may want that, but in this example, it will not give us the correct results. Now we’re finished designing this query, and
the final step is to Run it. Click the Run command in the Design tab, and the results
will instantly appear in Datasheet View, which looks exactly like a table. If you scroll
through the results, you can see that each customer lives either in Raleigh *or* in zip
code 27513. If you want to make any changes to the query,
you can click the View drop-down arrow and go back to Design View. And just like any
object, it’s a good idea to save it. I’ll call it Nearby Customers. This was just a basic example, and in the
next video we’re going to talk about how to make a query with multiple tables. You may
want to practice this one a few times before you go on, to make sure you’re comfortable
with the process.

38 comments

  • Zoe Carrillo

    You are helping me a lot with your videos! Thank you very much!

    Reply
  • George Hatoutsidis

    what is this programme??

    Reply
  • Darren Do

    Concise, easy to understand, good audio clarity and comes with subtitles as well. These videos are a life saver. Thank you!

    Reply
  • John Smith

    Can you just write an SQL Query, much simpler. If you can where would you do that ?

    Reply
  • Paige Queen B

    Thank you! Starting my first Finance job next week and I need to brush up on these things.

    Reply
  • C.J Cox

    I live in raleigh so respect

    Reply
  • Md. Rabiul Alam Hridoy

    VEY HELPFUL.. THANKS

    Reply
  • gillian fraser

    thank you a million times my daughter have been looking all over the internet for a simple video thanks again

    Reply
  • Gabriella LaPlace

    thanks

    Reply
  • Nerf Attack

    Better explaination than my maths teacher. And my school is third in education in india

    Reply
  • Ray Lewis

    Hi. If I wanted to show/list the last five (5) dates of which any particular customer purchased an item?

    Reply
  • Dal

    Thanks for the great tutorial!

    Reply
  • Zakarya Husaen

    Keep doing it

    Reply
  • jean andrew joseph

    Thank you. You have helped me understand how to create a simple query. This is beneficial to me because I am currently doing an assessment that is based on access, creating forms and query. I wasn't sure how to make a query. If I get a good mark, it's because of you. Please continue making such beneficial and simple videos. From NZ.

    Reply
  • Samuel Johnson

    your voice sounds like Tina from "Bob's Burgers"

    Reply
  • Wink Shine

    Thank you so much

    Reply
  • Gill

    I'm in 11th grade and I take Computer Application Technology as a subject. But I'm completing high-school online so I have no help from anywhere. and I have a task due in two days involving word, excel and lastly the dreaded access. So glad this enabling me to actually complete this assignment.THANK YOU!

    Reply
  • _Faithful Girl

    Thank you! I really needed help. Getting a test on this.

    Reply
  • Jayeeta Guha

    Hey must say thank u😚😚😚😊😊😊😚😚😚☺☺☺☺😊😊😊😉😉😉😚😚😚😚😙😙✌ helped me

    Reply
  • Clinton Snow

    Very good video, This video is very helpful and easy explanation.

    Reply
  • Salma Nagy

    I want to learn how to connect to tables with a special way like: I have a database for students and in the main table there is a field of the number of absence days, in the other table is like a calendar with the teachers if she marked on one day or added a link to the document it will automatically count one number more in the first main table under the field of absece and student's record . can you please help me in that ?

    Reply
  • Odjao Bless

    VERY SIMPLE

    Reply
  • ɞıɢѧռԀғıռє2

    Thank you explain everything I needed.

    Reply
  • Nick Contabile

    Thank you for the concise easy to understand break down of creating queries

    Reply
  • Abhik Pal

    nice.Vividly explained

    Reply
  • Bass Booster

    500th like 🔥

    Reply
  • Lam8261

    Excellent video!! 👏🏼

    Reply
  • ferrarienzo564

    0:58 query designs tab. Thank you. You my friend are a life saver.

    Reply
  • Nicholas Morgenstern

    I love you stranger with a voice that is like Tina's from Bob's Burgers. Great job.

    Reply
  • I AmRis

    Tq sir , nice information

    Reply
  • lowkey loki

    Two years later and you're still saving lives. Thank you so much!

    Reply
  • IttaiAK

    thanks you, it helped a lot

    Reply
  • Bibhav Dash

    Thank you very much.. From India

    Reply
  • Pankaj Negi

    Thanks very helpful

    Reply
  • James Hardwick

    ok

    Reply
  • Tanaya Amar

    I'm a Duke graduate working outside NC, and I have an interview next Friday for a role that requires Access. I wanted to watch a short video that explained the basics and this was the first video I selected. Was shocked to see the City column! I guess the interview is gonna go great! 😀

    Reply
  • Terrence Bob

    Ms ramandeep sent me here

    Reply
  • Wayne M

    Thank you. It's been years since I used Access. I use Word and Excell all the time, but have forgotten how to use Access

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *