From the course: Learning Data Analytics Part 2: Extending and Applying Core Knowledge

Building distinct queries

From the course: Learning Data Analytics Part 2: Extending and Applying Core Knowledge

Building distinct queries

“

- [Instructor] We don't always have access to every single table or data system in an organization. And you may find that you need to create data from the data you do have. Knowing how to create unique or distinct queries can help you achieve these datasets. Let me show you what I mean. Let's take a look at the invoices detail data. I'll go ahead and double-click that. And I want you to take a look at the StockItemID and the Description. We don't have access to the products system. However, from our invoices detail, we can see what's been invoiced by looking at the stock item and the description. Okay, let's go ahead and write a query. I'll go to create, I'll choose query design. I'll go ahead and double-click InvoicesDetails. And then I'll double-click StockItemID, and I'll double-click Description. I know I want to see these sorted, so I'll go ahead and clicking in the sort line and sort the StockItemID ascending. This'll put all of them together. Go ahead and run your query. And the very first thing you'll note is that there are 23,000 plus records here. And we see USB missile launcher (Green) at the very top, and it's repeated every time it's been ordered. I just need the data to show me one line per product every time it's been ordered. I can make this a unique or distinct query so that I can have a query that just gives me the product listed one time versus every time it's been ordered. Okay, let's go ahead and go to the design view. Now, it's important to note that in Access, we go and set a property that makes this unique. If you're writing a SQL statement, you would just include the word distinct in the select statement. Let's go to the property sheet, and then I'm going to click in this gray area of my query. This gives me all of my query properties, and then I have a line for unique values. I'll go ahead and change that no to a yes. It's going to look at the two items that I put into the query grid and give me a unique listing. I'll go ahead and choose run, and then I see 227 unique list of products. Okay, let's go ahead and take a look at that SQL view. And just like in SQL you see it's got the distinct keyword. So by setting that property, it actually puts the distinct keyword in the statement. I'll go ahead and go back to my design view. Okay, let's go ahead and save this as ProductsList, and I'll go ahead and click OK. And run it one more time to take a look at it. I'll make note of that 227 records, and then I can close my products list and my invoice details. Being able to define a unique list in a query is an absolute must know-how skill. Remember, the way we do it in other programs might be slightly different, but we'll achieve the same results.

Contents