CURSO DE ACCESS 2019 - LECCIÓN 9 CONSULTAS — Transcript

Full transcript

1. 0:09All right, today we are going to start
2. 0:11with queries. In the last lesson, we
3. 0:14learned how to create relationships,
4. 0:16and we had a series of tables that
5. 0:18could work linked to one another. Well,
6. 0:21what is a query? A query is a way to
7. 0:24filter our tables so we can get results
8. 0:28that are more filtered and more
9. 0:30favorable for us, and that way, we
10. 0:33don't have to work with all the data.
11. 0:38We will be able to join several—
12. 0:40pardon me—several tables for that. We
13. 0:44will be able to delete data from tables
14. 0:46, create new tables, append data, and
15. 0:48so on. Well, I would go to Create and
16. 0:51then come over here to Query Design.
17. 0:54Let's start with an easy one. I have to
18. 0:56tell it which table or tables I want to
19. 0:58use. So, I'm going to start, for
20. 1:00example, with the Customers table. I
21. 1:02can select it there and click Add. And
22. 1:06here, all the fields appear: Customer
23. 1:08ID, Company Name, Contact, Title,
24. 1:10Country. And what I have to do here
25. 1:13where it says Field, for each one of
26. 1:15these, I have to add the fields I have
27. 1:17here, the ones I need. I surely don't
28. 1:20need all of them, so I don't have to
29. 1:21include everyone. What would I do now?
30. 1:24Well, for example, I would add Customer
31. 1:25ID. I have two ways: I can either
32. 1:28double-click it or drag it. I'm going
33. 1:30to move Company Name here. I'm going to
34. 1:34put Contact here and, for example,
35. 1:36Country. Let's assume that I don't need
36. 1:39any more than these. So, if I now click
37. 1:43here to view the datasheet, I now have
38. 1:45a table here that it has extracted from
39. 1:48the original, from the original
40. 1:50Customers table. So, as you can see,
41. 1:53there is my entire table. It's a little
42. 1:56different because I removed a field I
43. 1:58didn't need, or two fields, rather. So,
44. 2:00in that way, I can already work with
45. 2:03that table. If I go back to Design, I
46. 2:06will have options, such as if one of
47. 2:08the fields I added isn't needed, I can
48. 2:10hide it. I say, "Right now I don't want
49. 2:13to see the contact one," so I come here
50. 2:14and it removes it for me. And then I
51. 2:17can start adding criteria, like, for
52. 2:19instance, let's assume that here in
53. 2:21Company Name I'm only interested in—
54. 2:24I'll take a look to see what companies
55. 2:26there are. Well, it will only interest
56. 2:29me, for example, those that start with
57. 2:32the letter A. So I can come here and
58. 2:35tell it where it says criteria. Let's
59. 2:38see, here I put an A asterisk, and that
60. 2:44way it tells me that it will search for
61. 2:46what starts with A and has something
62. 2:48after it. You can see there that four
63. 2:50results appeared. And then I could
64. 2:53enter a second criterion. Let's assume
65. 2:55that, for example, I say I want those
66. 2:57from Mexico. There it would be. And now
67. 3:01none come up. Why? Because Mexico had
68. 3:04an accent, so I have to enter the exact
69. 3:06one. Since I didn't put the exact one,
70. 3:08I did it on purpose, it gives me an
71. 3:10error. So now I am going to put it with
72. 3:14there it would be with the accent. Now
73. 3:16yes, now two come up there. At any time
74. 3:18I can delete the criterion and then I
75. 3:21recover everything. And if I add more
76. 3:25data to the original table, that data
77. 3:27will be part of the query if it was
78. 3:30filtered for it. So, there we see again
79. 3:33that I have all the data. I could come
80. 3:37here and the same, be able to say, "Hey
81. 3:39, I want customer IDs between 30 and
82. 3:4235." So I put exactly between 30 and 35
83. 3:45. There we see 30, 31, 32, 33, 34, and
84. 3:5135. And that way, keep adding any
85. 3:53criteria that come to mind. We can
86. 3:56always provide the exact one, for
87. 3:58example, say company name, well I want
88. 3:59one called Bonetiz, so I put it, or I
89. 4:01want a country here, well I come here,
90. 4:02I say, you're going to get me all those
91. 4:04from Germany. Germany, like this, there
92. 4:08I would have it. I can enter a second
93. 4:11criterion or delete later, and so on.
94. 4:14If I want to save the query, in the end
95. 4:16a query is as if it were a table. This
96. 4:19is called, anyway as I told you, it's
97. 4:21called a select query. It says it here,
98. 4:24or selection, because what it does is
99. 4:26take data from the original table and
100. 4:28we simply filter them and display them.
101. 4:31Well, if I exit here now and tell it
102. 4:33that I do want to save the changes and
103. 4:36I give it a name, well, let's assume I
104. 4:38call it query. customers, query
105. 4:46customers, like this, I click accept
106. 4:47and it has created a new category for
107. 4:49us. Before we had tables and now we
108. 4:51have queries. I can query the query,
109. 4:54never better said, easily and it is
110. 4:57really the same as a table, but
111. 4:59filtered. And at any moment I can go
112. 5:03back to the design and remove any of
113. 5:05the criteria I have here, and that way
114. 5:08I could play around with it a bit and
115. 5:11find the data I want. To make a new
116. 5:14query, we said we were going to Create
117. 5:17and go to Query Design. Well, today
118. 5:20what we are going to do is use several
119. 5:22queries to use several, uh, sorry,
120. 5:24several tables. To use multiple tables,
121. 5:27they have to be related, and since we
122. 5:29had already related them, we won't have
123. 5:31any problems. Let's assume we want
124. 5:34Customers; it's already marked, so I
125. 5:36add it. And we want Orders. Then the
126. 5:39relationship shows up there. And the
127. 5:42good thing about this is that we will
128. 5:44be able to create a query, which as we
129. 5:46said, a query is ultimately a table
130. 5:48that will contain the data from both
131. 5:50tables. Let's assume that I now want to
132. 5:54put the company name here. We
133. 5:58double-click Contact, and then here I
134. 6:00want, for example, the Order ID. And,
135. 6:05for example, the ID of the employee who
136. 6:09managed the order, the Product ID,
137. 6:12order date, and delivery date. Well, so
138. 6:17if I click View now, it has already
139. 6:19created a table with the orders I have.
140. 6:22So that is very good because, in that
141. 6:25way, I don't have to query both tables,
142. 6:27and I can easily see one or several
143. 6:29orders there. So, that's it. Right.
144. 6:32Then I could come here and try to do,
145. 6:35for example, by order date, saying,
146. 6:37well, I want to see the ones from
147. 6:39such-and-such a day, so I would come
148. 6:42here and put, for example, this order.
149. 6:46I put it there. We always write it, and
150. 6:49then when we hit enter, it will fill in
151. 6:51some data for us there. So, it has
152. 6:53placed it for us there, and the order
153. 6:55for that day appears, and we have
154. 6:57another order there. That, at any
155. 7:00moment, we can add, uh, another table
156. 7:02if we wanted to. We click Show Table
157. 7:04and say, "Okay, Orders, and I also want
158. 7:06to include Products." Well, I add it,
159. 7:09and since it was related, I won't have
160. 7:11a problem because now I say that, for
161. 7:13example, I say, "Oh, I'm also going to
162. 7:16include the product name," and if I go
163. 7:18to view now, it has already added the
164. 7:20product name there. I'm going to remove
165. 7:24this date from here so that all the
166. 7:25orders appear, and there I have them. I
167. 7:29can see and remove or delete any field
168. 7:30from here, for example, saying, "Hey,
169. 7:32I'm not interested in the employee ID
170. 7:34right now.""So I remove it, and that's
171. 7:38how I build my query by setting the
172. 7:40criteria I want; in upcoming videos, we
173. 7:43will see more complicated criteria,
174. 7:46etc., where we can do this." Okay, so
175. 7:49let's assume I want to save this table,
176. 7:52this query, so I click here. I'm going
177. 7:54to call this query orders and I click
178. 8:02OK. Okay, another thing is that since
179. 8:04everything is related, this is my
180. 8:06orders query, I can see it there. If I
181. 8:09enter some dates here, they will also
182. 8:11automatically appear in the table.
183. 8:13Let's assume that here I enter 24/01/16
184. 8:14. and for the delivery date, let's put
185. 8:22that it was 28/01/16. and we exit. So
186. 8:30if I now go to the table, uh, we go to
187. 8:33orders. In orders, you can see that
188. 8:35they appear, and I have there what I
189. 8:37just filled in the query. In other
190. 8:39words, everything is interconnected; we
191. 8:41have no problem. when it comes to
192. 8:44filling it out from the table or from
193. 8:47the query. And if I make a new order,
194. 8:50for example, it will also automatically
195. 8:52appear in the orders query. Let's
196. 8:55assume I come here and say that
197. 8:57customer 22 has placed an order that
198. 9:01was managed by employee 2. The product
199. 9:06ID is 16, and the order date, let's put
200. 9:12it here, for example, let's say it was
201. 9:1701/22/2016, and we shipped or delivered
202. 9:23the product on 01/26/2016. And I'm also
203. 9:28going to fill in the data I have here.
204. 9:30I'm going to fill in this product as
205. 9:31well. Let's assume this is 01/25/2016.
206. 9:38I could also use this calendar here to
207. 9:39look for dates, but I find it more
208. 9:41comfortable to type it in, especially
209. 9:42if they are from previous years. Okay,
210. 9:45and so here let's assume this was
211. 9:48received on 01/30/2016. Okay, so there
212. 9:53we have it. Yes, well, I made a mistake
213. 9:55there. It's 30th of January, I put 30th
214. 9:57of February. There it is. Okay, and if
215. 10:00I exit now and open the orders query
216. 10:02again, exactly, now I have the four
217. 10:04orders there; I wouldn't have any
218. 10:06problem because when entering new data,
219. 10:09the query updates itself without issue.
220. 10:12Well, let's go back to the design for a
221. 10:13moment. At any moment, as I have told
222. 10:16you, I can modify the tables. What
223. 10:20happens if we try to put together two
224. 10:22tables that have no relationship? Well,
225. 10:25in principle we will be able to enter
226. 10:27the data, but it will surely give us an
227. 10:28error or the data will be inaccurate.
228. 10:30So, it doesn't make much sense to enter
229. 10:33two tables that are not related.