Home   Fees/Services   Access Templates   Tutorials   Tutorial Downloads   Articles   Search   Contact  Privacy  Links
Predicate Query: Top Percent All Distinct
Select Distinct, Select Distinctrow, Select Top, Select All
Home > Tutorials > Query Tutorials > Predicate Query Example
 






Query Tutorials Index:
Too Complex Query
Aggregate Query
Not In Operator
Choose Function
Crosstab Query
Date Time Query
SQL Delete Query
External Table Query
Report Filter Query
Group By Query
Having Query Clause
Histogram Query
Access Append Query
Master Detail Update
Order By Query
SQL Parameter Query
Predicate Query
Self Join Query
Scalar SubQuery
Master Detail Query
Select Query
Access SQL Select Top
SQL Union Query
SQL Update Query
Custom Query Function

 

MultiSelect Parameter Forms Access Tutorial


SQL Predicate Examples

SQL Predicates are simple but important to understand for full use of the query programming language. There are five predicate functions:

  • Select All

  • Select Distinct

  • Select Distinctrow

  • Select Top

  • Select Top Percent

The ALL command is the default when you use a select statement:



Microsoft Access is a fully capable database and it's primary limitations is the number of concurrent users it will support. Generally, we recommend a limit of 10 to 15 users.

Did you know you don't need to move to SQL/Server to run and manage your Access database on the net. There are many alternative solutions. Among them are Amazon's AWS cloud services and various hosting services in nearly every state.


Select * from Employees

is equivalent to entering

Select All * from Employees The Distinct command is often confused with the distinctrow keyword.  Here is an example of the difference:

Select Distinctrow Last_Name from Employees

This query won't necessarily retrieve a distinct list of employee last names.  If there are duplicates last names and ANY other field has different data between the two duplicate name records then you will get both records even though they have the same last name.  Whereas:

Select Distinct Last_Name from Employees

Use the distinct directive to query unique records.  Distinct will retrieve a unique list of employee last names because the Distinct command only looks at the fields you are returning in the query.  I have never had a reason to use the Distinctrow function because our tables never have duplicate rows.

Select TOP (aka Top Values/Top Values) is explained in an example page: Select Top 10 Records, but we will review it here combined with the PERCENT option:

Select Top 10 Last_Name from Employees
Order By Age desc

Using the Top 10 example tells the query engine to return 10 records, in this case it will be the 10 oldest employees.  Here is the percent option:

Select Top 10 Percent Last_Name from Employees Order By Age desc

In this case, if you had 1000 employees in the Employee table you would retrieve 100 records containing a list of the 100 oldest employees.

Now you know all about the use of predicates in the Microsoft Access programming language.







A Blue Claw Software Design Template:

HOA Software Template
 









A Blue Claw Database Design Article:

Microsoft Access On The Internet
 









Blue Claw Database Design Downloadable Tutorial:
Make Dependent Combo Box Code MS Access  Tutorial Download  








Microsoft Office:
MS Access 2000 Through Access 2016 & Office 365
 


Contact Information
Copyright 2000-2017 Blue Claw Database Design
Development in Microsoft Access, Microsoft SQL/Server and Azure

See our SQL/Server Development and Access Migration Tutorials