| CATEGORII DOCUMENTE |
Limbajul SQL
Limbajul SQL (Structured Query Language = Limbaj structurat de interogare) este un mijloc universal de interogare si procesare a bazelor de date, prezent in toate sistemele puternice de gestionare a bazelor de date.
El se concretizeaza in SQL Statements (propozitii SQL) sub forma unui sir de caractere cu o anumita sintaxa. Acest SQL Statement se trimite bazei de date ca o 'directiva', care va fi executata asupra bazei de date, avand ca rezultat o colectie de date virtuala sau o procesare in bloc a inregistrarilor bazei de date.
SQL Statements, propozitiile SQL se mai numesc 'Query' - 'Interogare'.
Interogarile pot fi de doua feluri:
-SELECT Query
-Action Query (delete query, update query, append query).
Prin Select Query se face interogarea propriu-zisa a bazelor de date, adica selectarea de informatie dintr-una sau mai multe tabele, cu descriere de campuri calculate, respectand anumite criterii , iar informatia selectata ordonandu-se dupa anumite campuri.
Prin Action Query se executa prelucrari asupra unei coloctii de date, respectand anumite criterii de selectie. Se pot executa stergeri de inregistrari (delete query), adaugare de inregistrari (append query) sau editari de inregistrari(update query).
Este o 'intrebare' trimisa unei baze de date sub forma unei propozitii care descrie o structura de tabel. 'Raspunsul' bazei de date este un DataSheet - tabelul cerut completat cu informatia corespunzatoare extrasa din sursa de date specificata in propozitie.
Exista doua tipuri principale de 'intrebari'-propozitii: SELECT simplu si SELECT total. Selectul simplu returneaza un bloc de date care respecta o anumita conditie si eventual campuri calculate (de exemplu, toti salariatii dintr-o anumita grupa) . Selectul total returneaza un sumar pe sursa de date (de exemplu, total impozit aferent fiecarei grupe de salariati).
Datasheet view este "updatable" daca se pot modifica valorile campurilor in tabel
SELECT simplu:
Sintaxa:
SELECT FROM [WHERE ] [ORDER BY [DESC] ]
Parantezele drepte semnifica faptul ca clauza din paranteze este optionala; daca clauza se utilizeaza, parantezele nu se vor scrie.
FROM- Sursa de date este o colectie de date sub forma de tabel care se gaseste in interiorul bazei de date.
SELECT- Lista de campuri este enumerarea campurilor (separate prin virgula) care ne intereseaza din sursa de date.
WHERE- Conditie - optionala- este criteriul de selectie pentru inregistrarile returnate (de ex. WHERE grupa='Sectie1').
ORDER BY -camp - optional- specifica campul pe care se face ordonarea datelor rezultate. Daca se specifica DESC (descending) ordonarea se face descrescator.
Exemplu :
Interogare care returneaza o lista cu salariat, Impozit, Avans, suma Impozit+Avans cu conditia sa fie din grupa 'sectie' si ordonarea sa se faca descrescator in functie de suma Impozitului cu avansul:
SELECT nume, GetImpozit,GetAvans, GetImpozit+GetAvans as [Camp Calculat] FROM Salariati WHERE grupa='Sectie' ORDER BY GetImpozit+GetAvans DESC;
SELECT total:
Sintaxa:
SELECT , FROM [WHERE ] GROUP BY [HAVING conditie] [ORDER BY [DESC] ]
Se observa ca fata de tipul anterior prezentat , acest SELECT are doua tipuri de campuri de selectie- campuri care se grupeaza si campuri care se totalizeaza, si mai are o clauza in plus- clauza GROUP BY.
Functiunea clauzelor SELECT,FROM,WHERE,ORDER BY este indentica cu tipul anterior.
- este lista de campuri la care dorim sa atasam un total facut pe .
- reprezinta campurile care se vor totaliza cu ajutorul functiilor de totalizare - SUM(camp) - se face suma campului pentru fiecare valoare a campurilor pentru care se face sumarizarea, AVG(camp) - media, MIN(camp) - valoarea minima ,MAX(camp) -valoarea maxima etc.
GROUP BY - este clauza in care se specifica campurile pentru care se face sumarizare, care vor aparea o singura data in setul de date returnat (dupa care se va face gruparea datelor)
De exemplu:
Interogare pentru aflarea totalului impozitului pe fiecare grupa.
SELECT grupa, sum(GetImpozit) as [Suma Impozitului] FROM Salariati GROUP BY grupa ORDER BY sum(GetImpozit) DESC;
Use the SELECT statement to query existing tables or queries
Syntax
[AS alias1 [, alias2 [, ]]]
SELECT [predicate] ListaCampuri
[WHERE ]
[GROUP BY ]
[HAVING ]
[ORDER BY ]
unde
ListaCampuri =
Remarks
The SELECT statement uses these arguments.
Argument Description
predicate One of the following predicates: ALL, DISTINCT, DISTINCTROW, or TOP. You use the predicate to restrict the number of records returned.
table The name of the table from which records are selected.
field1, field2 Campurile din care vrei informatia. Daca vrei mai multe campuri, ele vor apare in ordinea din lista.
ListaCampuriCalculate = lista de campuri si campuri calculate; pt. construirea ei se pot folosi diferite functii. A se consulta capitolul FUNCTII SQL
alias1, alias2 The column names to use in displaying the retrieved data in Datasheet view.
tableexpression The name of the table or tables containing the data you want to retrieve.
externaldatabase The name of the database containing the tables in tableexpression, if not in the current database.
Notes
SELECT is usually the first word in an SQL statement. Most SQL statements are either SELECT or SELECTINTO statements.
The minimum syntax for a SELECT statement is:
SELECT fields FROM table
You can use an asterisk (*) to select all fields in a table. The following example selects all of the fields in the Employees table:
SELECT Employees.* FROM Employees;
If a field name is included in more than one table in the FROM clause, precede it with the table name and the . (punct) operator. In the following example, the Department field is in both the Employees table and the Supervisors table. The SQL statement selects Department from the Employees table and SupvName from the Supervisors table:
SELECT Employees.Department, SupvName
FROM Supervisors, Employees
WHERE Employees.Department = Supervisors.Department;
In Datasheet view, Microsoft Access uses the field name as a column heading. If you want a different heading, use the AS reserved word. The following example uses the title Birth to head the column in the resulting datasheet:
SELECT [Birth Date] AS Birth FROM Employees;
Syntax
FROM sourcetable
If you don't include one of the predicates, ALL is assumed, and Microsoft Access selects all of the records that meet the conditions in the SQL statement. The following two examples are equivalent and return all records from the Employees table:
SELECT ALL *
FROM Employees
ORDER BY [Employee ID];
SELECT *
FROM Employees
ORDER BY [Employee ID];
Use DISTINCT when you want to omit records that contain duplicate data in the selected fields. To be included in the results of the query, the values for each field listed in the SELECT statement must be unique. For example, several employees listed in an Employees table may have the same last name. If two records contain Smith in the Last Name field, the following SQL statement returns only one of the records:
SELECT DISTINCT [Last Name]
FROM Employees
If you omit DISTINCT, this query returns both Smith records.
If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results.
The output of a query that uses DISTINCT isn't updatable and doesn't reflect subsequent changes made by other users.
Use DISTINCTROW when you want to omit data based on entire duplicate records, not just duplicate fields. For example, you might create a query that joins the Customers and Orders tables on the Customer ID field. The Customers table contains no duplicate Customer ID fields, but the Orders table does because each customer can have many orders. The following SQL statement shows how you can use DISTINCTROW to produce a list of companies that have at least one order but without any details about those orders:
SELECT DISTINCTROW [Company Name]
FROM Customers INNER JOIN Orders
ON Customers.[Customer ID] = Orders.[Customer ID]
ORDER BY [Company Name];
If you omit DISTINCTROW, this query produces multiple rows for each company that has more than one order.
DISTINCTROW has an effect only when you select fields from some, but not all, of the tables used in the query. DISTINCTROW is ignored if your query includes only one table.
Using DISTINCTROW is equivalent to setting the UniqueRecords property to Yes (the default value) in the query property sheet in a query's Design view.
Use the TOP reserved word to return a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose you want the names of the top 25 students from the class of 1994:
SELECT TOP 25 [First Name], [Last Name]
FROM Students
WHERE [Graduation Year] = 1994
ORDER BY [Grade Point Average] DESC
If you don't include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause.
The TOP predicate doesn't choose between equal values. In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records.
You can also use the PERCENT reserved word to return a certain percentage of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose that, instead of the top 25 students, you want the top 10 percent of the class:
SELECT TOP 10 PERCENT [First Name], [Last Name]
FROM Students
WHERE [Graduation Year] = 1994
ORDER BY [Grade Point Average] DESC
The value that follows TOP must be an unsigned Integer.
TOP doesn't affect the query's updatability.
Specifica care sunt tabelele care contin campurile din lista de campuri; de asemenea, in clauza from se pot stabili legaturi intre (JOIN) tabele .
FROM specifies the tables or queries that contain the fields listed in the SELECT statement.
Syntax
SELECT fieldlist
FROM tableexpression [IN externaldatabase]
Remarks
A SELECT statement containing a FROM clause uses these arguments.
Argument Description
fieldlist The name of the field or fields to be retrieved along with any field-name aliases, SQL aggregate functions, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
tableexpression An expression that identifies one or more tables from which data will be retrieved. The expression can be a single table name or a compound resulting from a JOIN or subquery.
externaldatabase The name of an external database containing all the tables in tableexpression.
Notes
FROM is required and follows any SELECT.
The order of the table names in tableexpression isn't important.
You can use an INNER JOIN operation in any FROM clause. It creates an equi-join, which is also known as an inner join. Equi-joins are the most common type of join. They combine records from two tables whenever there are matching values in a field common to both tables.
Syntax
Explicatii
Care tabele sunt legate : table1 INNER JOIN table2
Remarks
The INNER JOIN operation uses these arguments.
Argument Description
table1, table2 The names of the tables from which records will be combined.
field1, field2 The names of the fields that will be joined. If they aren't numeric, the fields must be of the same data type and contain the same kind of data, but they don't have to have the same name.
You can use INNER JOIN with the Departments and Employees tables to select all the employees in each department. In contrast, to select all departments (even if some have no employees assigned to them) or all employees (even if some aren't assigned to a department), you can use a LEFT JOIN or RIGHT JOIN operation to create an outer join.
If you try to join fields containing Memo or OLE Object data, an error will occur.
You can join any two numeric fields, even if they are of different data types. For example, you can join a Number field whose FieldSize property is set to Integer and a Counter field.
The following example shows how you could join the Categories and Products tables on the Category ID field:
SELECT [Category Name], [Product Name]
FROM Categories INNER JOIN Products
ON Categories.[Category ID] = Products.[Category ID];
In the preceding example, Category ID is the joined field, but it isn't included in the query output or datasheet because it isn't included in the SELECT statement. To include the joined field, include the field name in the SELECT statementin this case, Categories.[Category ID].
You can also nest JOIN statements using the following syntax:
SELECT fields
FROM table1 INNER JOIN
(table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN )] ON table3.field3 = tablex.fieldx)]
ON table2.field2 = table3.field3)
ON table1.field1 = table2.field2;
Exemplu:
SELECT DISTINCTROW Intrari.*, Gestiuni.NumeGest
FROM Intrari INNER JOIN Gestiuni ON Intrari.Gest = Gestiuni.IDGest;
Are ca rezultat tabela Intrari la care se ataseaza numele gestiunii pt. fiecare pozitie.
You can use the LEFT JOIN and RIGHT JOIN operations in any FROM clause to combine source-table records.
Syntax
FROM table1 [ LEFT | RIGHT ] JOIN table2
ON table1.field1 = table2.field2
Remarks
The LEFT JOIN and RIGHT JOIN operations use these arguments.
Argument Description
table1, table2 The names of the tables from which records will be combined.
field1, field2 The names of the fields that will be joined. The fields must be of the same data type and contain the same kind of data, but they don't have to have the same name.
Use a LEFT JOIN operation to create a left outer join. Left outer joins include all of the records from the first (left-hand) of two tables, even if there are no matching values for records in the second (right-hand) table.
Use a RIGHT JOIN operation to create a right outer join. Right outer joins include all of the records from the second (right-hand) of two tables, even if there are no matching values for records in the first (left-hand) table.
For example, you could use LEFT JOIN with the Departments (left) and Employees (right) tables to select all departments, including those that have no employees assigned to them. To select all employees, including those who aren't assigned to a department, you would use RIGHT JOIN.
The following example shows how you could join the Categories and Products tables on the Category ID field. The query produces a list of all categories, including those that contain no products:
SELECT [Category Name], [Product Name]
FROM Categories LEFT JOIN Products
ON Categories.[Category ID] = Products.[Category ID];
In this example, Category ID is the joined field, but it isn't included in the query output or datasheet because it isn't included in the SELECT statement. To include the joined field, enter the field name in the SELECT statementin this case, Categories.[Category ID].
Notes
To create a query that includes only records in which the data in the joined fields is the same, use an INNER JOIN operation.
You can nest INNER, RIGHT, and LEFT joins. See the discussion of nesting in the INNER JOIN topic to see how to nest joins of any type within other joins.
Exemplu de calcul de stoc pornind de la tabela de Intrari si tabela de Iesiri:
SELECT IntrariIdent.Denumire, IntrariIdent.Cod, IntrariIdent.Lot, [CantPr]-IIf(IsNull([CantOut]),0,[CantOut]) AS Stoc
FROM IntrariIdent LEFT JOIN StocActualLotIesiriGroupByLot ON IntrariIdent.Lot = StocActualLotIesiriGroupByLot.Lot
WHERE ((([CantPr]-IIf(IsNull([CantOut]),0,[CantOut]))>0))
ORDER BY IntrariIdent.Denumire;
Acest exemplu calculeaza stocul pt. fiecare articol, pornind de la tabela Intrari si de la query StocActualIesiriGroupByLot
Query StocActualIesiriGroupByLot=
SELECT Iesiri.Lot, Sum(Iesiri.Cant) AS CantOut
FROM Iesiri
GROUP BY Iesiri.Lot;
Use the IN clause in SQL statements to identify tables in any external database to which Microsoft Access can connect, such as a dBASE or Paradox database or another Microsoft Access database (other than the current database).
Syntax
To identify a destination table:
[SELECT | INSERT] INTO destination IN
To identify a source table:
FROM tableexpression IN
Remarks
A SELECT statement containing an IN clause uses these arguments.
Argument Description
destination The name of the external table into which data will be inserted.
tableexpression The name of the table or tables from which data will be retrieved. This argument can be a single table name or a compound resulting from a JOIN or subquery.
path The full path for the file containing table.
product The name of the database product used to create table, if not Microsoft Access (for example, dBASE III, dBASE IV, PARADOX 3.x, PARADOX 4.x, or BTRIEVE).
Notes
You can use IN to connect to only one external database at a time. An external database is one other than the current database.
To specify a non-Microsoft Access database, append a semicolon (;) to the name, and enclose it in single (' ') or double (' ') quotation marks. For example, either 'dBASE IV;' or 'dBASE IV;' is acceptable.
You can also use the DATABASE reserved word to specify the external database. For example, the following lines specify the same table:
FROM Table IN '' [dBASE IV; DATABASE=C:DBASEDATASALES;];
FROM Table IN 'C:DBASEDATASALES' 'dBASE IV;'
GROUP BY combines records with identical values in the specified field list into a single record. A summary value is created for each record if you include an SQL aggregate function, such as Sum or Count, in the SELECT statement.
Syntax
SELECT fieldlist
FROM table
WHERE criteria
Remarks
A SELECT statement containing a GROUP BY clause uses these arguments.
Argument Description
fieldlist The name of the field or fields to be retrieved along with any field-name aliases, SQL aggregate functions, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
table The name of the table from which records will be retrieved. For more information, see the FROM clause.
criteria Selection criteria. If the statement includes a WHERE clause, Microsoft Access groups values after applying the WHERE conditions to the records.
groupfieldlist The names of up to 10 fields used to group records. The order of the field names in groupfieldlist determines the grouping levels from the highest to the lowest level of grouping.
Notes
GROUP BY is optional.
Summary values are omitted if there is no SQL aggregate function in the SELECT statement.
Null values in GROUP BY fields are grouped and aren't omitted. However, Null values aren't evaluated in any SQL aggregate function.
Use the WHERE clause to exclude rows you don't want grouped, and use the HAVING clause to filter records after they've been grouped.
Unless it contains Memo or OLE Object data, a field in the GROUP BY field list can refer to any field listed in the FROM clause, even if the field is not included in the SELECT statement, provided the SELECT statement includes at least one SQL aggregate function. Microsoft Access can't group on Memo or OLE Object fields.
If you use a GROUP BY clause, all fields in the SELECT field list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function.
When you enter a field name that contains a space or punctuation, surround the name with brackets ([ ]):
SELECT [Product Name], Sum([Units in Stock])
FROM Products
GROUP BY [Product Name];
SELECT Department, Count([Department]) AS Tally
FROM Employees
GROUP BY Department; Creates a list of unique department names and the number of employees in each of those departments
SELECT Title, Count([Title]) AS Tally
FROM Employees
WHERE Department = 'Sales'
GROUP BY Title; For each unique job title, calculates the number of Sales department employees who have that title
SELECT Item, Sum(Units) AS Tally
FROM ItemsInStock
GROUP BY Item, Color; Calculates the number of items in stock for each combination of item number and color
You can use WHERE to determine which records from the tables listed in the FROM clause will be included in the results of the SELECT statement. Microsoft Access selects the records that meet the conditions listed in the WHERE clause. These conditions are the same ones you might enter in the Criteria field in the QBE grid.
Syntax
FROM tableexpression
WHERE criteria
Remarks
A SELECT statement containing a WHERE clause uses these arguments.
Argument Description
fieldlist The name of the field or fields to be retrieved along with any field-name aliases, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options
tableexpression The name of the table or tables from which data will be retrieved
criteria An expression that records must satisfy to be included in the query results
WHERE is optional but when included follows FROM. For example, you can select all employees in the sales department (WHERE Dept = 'Sales') or all customers between the ages of 18 and 30 (WHERE Age Between 18 And 30).
WHERE is similar to HAVING. WHERE determines which records are selected. Similarly, once records are grouped with GROUP BY, HAVING determines which records are displayed.
Use the WHERE clause to eliminate records you don't want grouped by a GROUP BY clause.
Use various expressions to determine which records the SQL statement returns. For example, the following SQL statement selects all employees whose salaries are greater than $21,000:
SELECT [Last Name], Salary FROM Employees
WHERE Salary > 21000;
A WHERE clause can contain up to 40 expressions linked by logical operators, such as And and Or.
When you enter a field name that contains a space or punctuation, surround the name with brackets ([ ]):
SELECT [Product ID], [Units In Stock]
FROM Products
WHERE [Units In Stock] <= [Reorder Level];
When you specify the criteria argument, date literals must be in U.S. format, even if you're not using the U.S. version of Microsoft Access. For example, May 10, 1994, is written 10/5/94 in the United Kingdom and 5/10/94 in the United States. To find records with this date in a U.K. database, you'd have to use the following SQL statement:
SELECT *
FROM Orders
WHERE [Shipped Date] = #5/10/94#
Use HAVING to specify which grouped records are displayed. Once GROUP BY combines records, HAVING displays all records grouped by the GROUP BY clause that satisfy the conditions of the HAVING clause.
Syntax
SELECT fieldlist
FROM table
WHERE selectcriteria
GROUP BY groupfieldlist
HAVING groupcriteria
Remarks
A SELECT statement containing a HAVING clause uses these arguments.
Argument Description
fieldlist The name of the field or fields to be retrieved along with any field-name aliases, SQL aggregate functions, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
table The name of the table from which records will be retrieved. For more information, see the FROM clause.
selectcriteria Selection criteria. If the statement includes a WHERE clause, Microsoft Access groups values after applying the WHERE conditions to the records.
groupfieldlist The names of up to 10 fields used to group records. The order of the field names in groupfieldlist determines the grouping levels from the highest to the lowest level of grouping.
groupcriteria An expression that determines which grouped records to display.
Notes
HAVING is optional.
HAVING is similar to WHERE, which determines which records are selected. Once records are grouped with GROUP BY, HAVING determines which records are displayed.
A HAVING clause can contain up to 40 expressions linked by logical operators, such as And and Or.
ORDER BY sorts the displayed data on a specified field or fields in ascending or descending order.
Syntax
SELECT fieldlist
FROM table
WHERE selectcriteria
ORDER BY field1 [ASC | DESC ][, field2 [ASC | DESC ][, ]]
Remarks
A SELECT statement containing an ORDER BY clause uses these arguments.
Argument Description
fieldlist The name of the field or fields to be retrieved along with any field-name aliases, SQL aggregate functions, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
table The name of the table from which records will be retrieved. For more information, see the FROM clause.
selectcriteria Selection criteria. If the statement includes a WHERE clause, Microsoft Access groups values after applying the WHERE conditions to the records.
field1, field2 The names of the fields to sort records on.
Notes
ORDER BY is optional (unless you use the TOP or TOP n PERCENT predicate in the SELECT field list). If you don't include ORDER BY, the data is displayed unsorted.
The default sort order is ascending (A-Z, 0-9). However, you may want to include the ASC reserved word at the end of each field you want to sort in ascending order. Both of the following examples sort employee names in last name order:
SELECT [Last Name], [First Name]
FROM Employees
ORDER BY [Last Name];
SELECT [Last Name], [First Name]
FROM Employees
ORDER BY [Last Name] ASC;
To sort in descending order (Z-A, 9-0), add the DESC reserved word to the end of each field you want to sort in descending order. The following example selects salaries and sorts them in descending order:
SELECT [Last Name], Salary
FROM Employees
ORDER BY Salary DESC, [Last Name];
If you specify a field containing Memo or OLE Object data in the ORDER BY clause, an error will occur. Microsoft Access doesn't sort on fields of these types.
ORDER BY is usually the last item in an SQL statement.
You can include additional fields in the ORDER BY clause. Records are sorted first by the first field listed after ORDER BY. Records that have equal values in that field are then sorted by the value in the second field listed, and so on.
Using an ORDER BY clause is equivalent to selecting Ascending or Descending in the Sort cell of the QBE grid.
You use the UNION operation to create a union query, combining the results of two or more independent queries or tables.
Syntax
query1 UNION [ALL] query2 [UNION [ALL] queryn [ ]]
Remarks
The UNION operation uses these arguments.
Arguments Description
query1, query2, queryn A SELECT statement or the name of a stored Microsoft Access query or table preceded by the TABLE reserved word
You can merge the results of a query and an SQL statement in a single UNION operation. The following example merges the results of an existing query named New Accounts and a SELECT statement:
TABLE [New Accounts] UNION ALL
SELECT *
FROM Customers
WHERE [Order Amount] > 1000
By default, no duplicate records are returned when you use a UNION operation; however, you can include the ALL predicate to ensure that all records are returned. This also makes the query run faster.
All queries in a UNION operation must request the same number of fields; however, the fields don't have to be of the same size or data type.
Notes
You can use a GROUP BY and/or HAVING clause in each query argument to group the returned data.
You can use an ORDER BY clause at the end of the last query argument to display the returned data in a specified order.
UNION Operation Examples
SELECT [Company Name], City
FROM Suppliers
WHERE Country = 'Brazil'
UNION SELECT [Company Name], City
FROM Customers
WHERE Country = 'Brazil' Retrieves the names and cities of all suppliers and customers in Brazil
SELECT [Company Name], City
FROM Suppliers
WHERE Country = 'Brazil'
UNION SELECT [Company Name], City
FROM Customers
WHERE Country = 'Brazil'
ORDER BY City Retrieves the names and cities of all suppliers and customers located in Brazil, arranged by city name
SELECT [Company Name], City
FROM Suppliers
WHERE Country = 'Brazil'
UNION SELECT [Company Name], City
FROM Customers
WHERE Country = 'Brazil'
UNION SELECT [Last Name], City
FROM Employees
WHERE Region = 'South America' Retrieves the names and cities of all suppliers and customers in Brazil and the last names and
cities of all employees in South America
TABLE [Customer List]
UNION
TABLE [Supplier List] Retrieves the names and IDs of all suppliers and customers
SQL Subqueries
A subquery is a SELECT statement nested inside a SELECT, SELECTINTO, INSERTINTO, DELETE, or UPDATE statement or inside another subquery. You can use subqueries in a query's SQL view, in a Criteria or Field cell in the QBE grid in a query's Design view, or in SQL statements in Access Basic.
Syntax
You can use three forms of syntax to create a subquery:
comparison [ANY | ALL | SOME] (sqlstatement)
expression [NOT] IN (sqlstatement)
[NOT] EXISTS (sqlstatement)
Remarks
A subquery uses these arguments.
Argument Description
comparison An expression and a comparison operator that compares the expression with the results of the subquery.
expression An expression for which the result set of the subquery will be searched.
sqlstatement A SELECT statement, following the same format and rules as any other SELECT statement. It must be enclosed in parentheses.
You can use a subquery instead of an expression in the field list of a SELECT statement or in a WHERE or HAVING clause. In a subquery, you use a SELECT statement to provide a set of one or more specific values to evaluate in the WHERE or HAVING clause expression.
Notes
Use the ANY or SOME predicate, which are synonymous, to retrieve records in the main query that satisfy the comparison with any records retrieved in the subquery. The following example returns all products whose unit price is greater than that of any product sold at a discount of 25 percent or more:
SELECT * FROM Products
WHERE [Unit Price] > ANY
(SELECT [Unit Price] FROM [Order Details]
WHERE [Discount] >= 25);
Use the ALL predicate to retrieve only those records in the main query that satisfy the comparison with all records retrieved in the subquery. If you changed ANY to ALL in the above example, the query would return only those products whose unit price is greater than that of all products sold at a discount of 25 percent or more. This is much more restrictive.
Use the IN predicate to retrieve only those records in the main query for which some record in the subquery contains an equal value. The following example returns all products sold at a discount of 25 percent or more:
SELECT * FROM Products
WHERE [Product ID] IN
(SELECT [Product ID] FROM [Order Details]
WHERE [Discount] >= 25);
Conversely, you can use NOT IN to retrieve only those records in the main query for which no record in the subquery contains an equal value.
Use the EXISTS predicate (with the optional NOT reserved word) in true/false comparisons to determine whether the subquery returns any records.
You can also use table name aliases in a subquery to refer to tables listed in a FROM clause outside the subquery. In this example, the Employees table is given the alias 'T1':
SELECT [Last Name], [First Name], Title, Salary
FROM Employees AS T1
WHERE Salary >=
(SELECT Avg(Salary)
FROM Employees
WHERE T1.Title = T1.Title) Order by Title;
In the preceding example, the AS reserved word is optional.
SQL Subqueries Examples
SELECT [Last Name], [First Name], Title, Salary
FROM Employees
WHERE Title LIKE 'Sales Rep*' AND Salary > ALL
(SELECT Salary FROM Employees
WHERE (Title LIKE '*Manager*') OR (Title LIKE '*Director*')); Lists the name, title, and salary of every sales representative whose salary is higher than that of all managers and directors.
SELECT DISTINCTROW [Product Name], [Unit Price]
FROM Products WHERE [Unit Price] =
(SELECT [Unit Price] FROM [Products]
WHERE [Product Name] = 'Aniseed Syrup'); Lists the name and unit price of every product whose unit price is the same as that of Aniseed Syrup.
SELECT DISTINCTROW [Contact Name], [Company Name], [Contact Title], Phone
FROM Customers WHERE [Customer ID] IN
(SELECT DISTINCTROW [Customer ID]
FROM Orders WHERE [Order Date] BETWEEN #04/1/93# AND #07/1/93#); Lists the company and contact of every customer who placed an order in the second quarter of 1993.
SELECT [Last Name], [First Name], Title, Salary
FROM Employees T1
WHERE Salary >=
(SELECT AVG(Salary)
FROM Employees
WHERE Employees.Title = T1.Title)
ORDER BY Title; Lists employees whose salary is higher than the average salary for all employees.
SELECT [First Name], [Last Name]
FROM Employees AS E
WHERE EXISTS
(SELECT *
FROM Orders AS O
WHERE O.[Employee ID] = E.[Employee ID]); Selects the name of every
employee who has booked at least one order. This could also be done with an INNER JOIN.
1.Introducere
Campurile calculate sunt valori care se calculeaza pe baza campurilor existente intr-o sursa de date. Acesteor valori li se atribuie cate un nume de camp care va apare in rezultatul unei interogari.
Campurile calculate se descriu prin expresie matematica si functii , iar in descriere, campurile utilizate se scriu intre paranteze drepte.
Exemple:
-camp calculat pe baza cantitatii vandute si a pretului de vanzare care va calcula valoarea=cantitate*pret:
Definirea campului:
[cantitate]*[pret] as [valoare]
Integrarea lui intr-un SELECT:
SELECT [Sortiment], [cantitate], [pret], [cantitate]*[pret] as [valoare] FROM Vanzari;
-camp calculat pe baza salariului brut care calculeaza CPS:
Definirea campului:
[SB]*0.03 as [CPS]
Integrarea lui intr-un SELECT:
SELECT nume, grupa,SB [SB]*0.03 as [CPS] FROM Salariati;
FUNCTII
Campurile calculate se pot descrie sub forma de expresie matematica, dar se pot utiliza si anumite functii:
!NOTA: Functiile se pot utiliza si pentru crearea conditiilor de selectie la clauzele WHERE si HAVING!
Functia ABS(numar) - valoare absoluta a numar
Ex.: SELECT ABS(Suma) as [Modul Suma] from [note]
Functia INT(numar) - intregul din numar
Ex.: SELECT INT(AjSom) as [Ajutor Somaj] from StatSal
Functia IIF(cond,val1,val2) - este functia logica de decizie. Valoarea campului calculat se poate atribui in functie de o conditie.
Sintaxa:
Valoarea Campului= IIF(conditie, Valoare_daca_este_saisfacuta_conditia, Valoare_daca_NU_este_saisfacuta_conditia)
- traducere-
Valoarea Campului=
DACA conditie,
ATUNCI Valoare_daca_este_saisfacuta_conditia,
ALTFEL Valoare_daca_NU_este_saisfacuta_conditia)
Exemplu- Campul [Debit] sa ia valoarea campului [Suma] daca [Suma]>0 si valoarea 0 in caz contrar:
Definirea campului:
iif(suma>0, suma ,0) as Debit
Integrarea lui intr-un SELECT:
select data, salariat, formula, iif(suma>0, suma ,0) as Debit, iif(suma<0, -suma ,0) as Credit from [Note]
Functia DateDiff(interval_comparare,Data_inceput,Data_sfarsit)
Returneaza numarul de interval_comparare existente intre Data_inceput si Data_sfarsit.
Valori pt. interval_comparare
Year yyyy - an
Quarter q -timestru
Month m -luna
Day of Year y
Day d -zi
Weekday w
Week ww
Hour h
Minute n
Second s
Exemplu : Calculul vechimii in ani , intre dataintr(data intrarii) si acum (Functia NOW() - care returneaza data sistem - data curenta):
datediff('yyyy',[dataintr],now()) as Vechime
Functia LEFT(text,nr_Poz) -partea stanga a textului pe <nr_Poz> pozitii
Returneaza primele <nr_Poz> caractere din <text>
Ex.: LEFT([nume],3) as [Primele trei caractere din nume]
Functia RIGHT(text,nr_Poz) -partea dreapta a textului pe <nr_Poz> pozitii
Returneaza primele <nr_Poz> caractere din <text> din dreapta.
Returneaza un sir de <nr_Poz> caractere din <text> incepand din pozitia <Poz_inceput> . De ex., Mid('abcdefg',3,3)='cde'
2.Lista de functii dupa obiectul lor
Functii de conversie
ANSI value to string Chr
String to ANSI value Asc
Number to string Format, Str
String to number Val
One numeric data type to another CCur, CDbl, CInt, CLng, CSng, CStr, CVar, CVDate
Decimal number to other radix string Hex, Oct
Date to serial number DateSerial, DateValue
Serial number to date Day, Month, Weekday, Year
Time to serial number TimeSerial, TimeValue
Serial number to time Hour, Minute, Now, Second
Functii Date/Time
Get the current date or time Date, Now, Time
Set the date or time Date, Time
Time a process Timer
Perform date math DateAdd, DateDiff, DatePart
Parts of date Day,Month,Year
Functii specifice SQL utilizate cu GROUP BY
Perform an SQL aggregate operation Avg, Count, First, Last, Min, Max, StDev, StDevP, Sum, Var, VarP
Functii SQL pe domeniu de inregistrari (utilizabile in ListaCampuri fara GroupBy)
Perform domain aggregate operations DAvg, DCount, DFirst, DLast, DMin, DMax, DStDev, DStDevP, DSum, DVar, DVarP
Return a field value from a domain Dlookup
Functii pt. Inspection of Variables
Determine if a variable is a Date IsDate
Determine if a variable is Empty IsEmpty
Determine if a variable is Null IsNull
Determine if a variable is numeric IsNumeric
Functii matematice
Perform general calculations Exp, Log, Sqr
Perform trigonometric functions Atn, Cos, Sin, Tan, Derived Math Functions
Return the integer portion of a number Int, Fix
Get the absolute value of a number Abs
Get the sign of a number Sgn
Generate random numbers Randomize, Rnd
Operatori
Arithmetic * Operator, + Operator, - Operator, / Operator, Operator, ^ Operator, Mod
Comparison <, <=, >, >=, =, <>
Concatenation & operator
Logical And, Eqv, Imp, Not, Or, Xor
Pattern matching Like
SQL BetweenAnd, In, Is
Functii pt. Stringuri (variabile caracter, siruri de caractere)
Format a string Format
Create a string of repeating characters Space, String
Work with ANSI values Asc, Chr
Manipulate a string InStr, Left, LTrim, Mid, Right, RTrim, Trim
Convert to lowercase or uppercase letters LCase, UCase
Left or right align a string LSet, RSet
Find the length of a string Len
Compare two strings StrComp
3.Sintaxa functiilor
Explicitly converts expressions from one data type to another.
Syntax
CCur(expression)
CDbl(expression)
CInt(expression)
CLng(expression)
CSng(expression)
CStr(expression)
CVar(expression)
Remarks
The argument expression can be any valid string expression or numeric expression.
The following table shows which data type is returned by each data type conversion function.
Function From To
CCur Any valid expression Currency
CDbl Any valid expression Double
CInt Any valid expression Integer
CLng Any valid expression Long
CSng Any valid expression Single
CStr Any valid expression String
CVar Any valid expression Variant
The numeric conversion functions CCur, CDbl, CInt, CLng, and CSng explicitly control the data type of a numeric expression. For example, you can use CCur to force currency arithmetic (which has greater precision but less range) in cases in which integer, double-precision, or single-precision arithmetic normally would occur. CInt and CLng force integer arithmetic in cases in which currency, single-precision, or double-precision arithmetic normally would occur. CDbl and CSng force double- or single-precision arithmetic in cases in which currency or integer arithmetic normally would occur.
Because CInt and CLng convert expressions to integers, you can use these functions to round fractional numbers.
Tip All data type conversion functions can provide self-documenting code that indicates that the result of a calculation should be expressed as a particular data type rather than the default data type of the result.
For the CCur, CInt, CLng, and CSng functions, if expression lies outside the acceptable range, a run-time error occurs and a message is displayed unless it is trapped in error-handling code.
Note CInt differs from the Fix and Int functions, which truncate, rather than round, the fractional part of a number. For a demonstration of the differences between Int and Fix, see the example for Int. When the fractional part is exactly 0.5, the CInt function always rounds it to the nearest even number. For example, 0.5 rounds to 0, and 1.5 rounds to 2.
Calculates the number of selected records in a query, form, or report.
Syntax
Count(expr)
Remarks
The Count function uses the following argument.
Argument Description
expr String expression identifying the field that contains the data you want to count or an expression that performs a calculation using the data in the field. Operands in expr can include the name of one or more table fields, constants, or functions (which can be either intrinsic or user-defined but not other SQL aggregate or domain aggregate functions). You can count any kind of data, including text.
You can use Count to count the number of records in an underlying query. For example, you could use Count to count the number of orders shipped to a particular country.
Although expr can perform a calculation on a field, Count simply tallies the number of records. It doesn't matter what values are stored in the records.
The Count function doesn't count records that have Null fields unless expr is the asterisk (*) wildcard character. If you use an asterisk, Count calculates the total number of records, including those that contain Null fields. Do not enclose the asterisk in quotation marks (' '). The following example calculates the number of records in the Orders table in the NWIND.MDB database:
SELECT Count(*) AS [Total Orders] FROM Orders;
If expr identifies multiple fields, the Count function counts a record only if at least one of the fields is not Null. If all of the specified fields are Null, the record isn't counted. Separate the field names with an ampersand (&). The following example shows how you can limit the count to records in which either Shipped Date or Freight is not Null:
SELECT Count('[Shipped Date] & [Freight]') AS [Not Null] FROM Orders;
You can use Count in a query expression, a calculated control on a form or report (except in a page header or footer), or an SQL statement in a query's SQL view in the Query window.
Return the current system date.
Syntax
Date[$][( )]
Remarks
The parentheses are optional only when you use Date[$] in Access Basic.
Date returns a Variant (VarType 7) containing a date stored internally as a Double.
Date$ returns a 10-character string of the form mm-dd-yyyy, where mm is the month (01-12), dd is the day (01-31), and yyyy is the year (1980-2099).
The output of the Date$ function is equivalent to:
Format$(Now,'mm-dd-yyyy')
To set the system date, use the Date[$] statement.
Returns a Variant of VarType 7 (Date) to which a specified time interval has been added.
Syntax
DateAdd(interval, number, date)
Remarks
The DateAdd function uses the following arguments.
Argument Description
interval String expression that is the interval of time you want to add to date.
number Numeric expression that is the number of intervals you want to add to date. It can be positive (to get dates in the future) or negative (to get dates in the past).
date Date being added to or the name of a Variant of VarType 7 (Date). It can be either short form (1/1/95) or long form (1-January-1995).
You can use the DateAdd function to add a specified time interval to or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 30 days from today or a time that is 60 minutes from now.
The following table lists the valid time periods and their interval values.
Time period interval
Year yyyy
Quarter q
Month m
Day of year y
Day d
Weekday w
Week ww
Hour h
Minute n
Second s
If you want to add days to date, you can use y (day of year), d (day), or w (weekday).
The DateAdd function won't return an invalid date. The following example adds one month to January 31:
AddMonth = DateAdd('m', 1, #31-Jan-95#)
In this case, DateAdd returns 28-Feb-95, not 31-Feb-95. If date was 31-Jan-96, it would return 29-Feb-96 because 1996 is a leap year.
If you omit the year from date, DateAdd uses the current year. If date is enclosed by number signs (#), the current year becomes a permanent part of date. If you enclose date in double quotation marks (''), however, the current year is inserted in your code each time it is executed.
If the calculated date would precede the year 0 (that is, you subtract more years than are in date), an error occurs.
If number isn't a Long value, DateAdd follows the rules for the Fix function and rounds number to the nearest whole number.
Returns a Variant that contains the number of time intervals between two specified dates.
Syntax
DateDiff(interval, date1, date2[, firstweekday][, firstweek])
Remarks
The DateDiff function uses the following arguments.
Argument Description
interval String expression that is the interval of time you use to calculate the difference between date1 and date2
date1, date2 Two dates you want to use in the calculation or the names of two Date/Time fields (or a combination)
firstweekday An integer that specifies the first day of the week. It can be any of the following:
1 Sunday (default)
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday
firstweek An integer that specifies the first week of the year. It can be any of the following:
0 Use the First Week setting in the Options dialog box
1 Start on January 1 (default)
2 Start with the first four-day week
3 Start with the first full week
You can use the DateDiff function to determine how many time intervals exist between two dates. For example, you can use DateDiff to calculate the number of days between an order date and its ship date or the number of weeks between today and the end of the year.
The exact value returned by DateDiff can depend on the settings of the First Weekday and First Week options in the Options dialog box (View menu) or by the values of the firstweekday or firstweek arguments. For example, if you set firstweekday to Wednesday and interval to 'ww' (week), then DateDiff returns the number of Wednesdays between two dates:
Const WEDNESDAY = 4
Dim NumWed As Integer
NumWed = DateDiff('ww', '11/1', '11/30', WEDNESDAY)
The following table lists the valid time periods and their interval values.
Time period interval
Year yyyy
Quarter q
Month m
Day of year y
Day d
Weekday w
Week ww
Hour h
Minute n
Second s
If you want to know the number of days between date1 and date2, you can use either y (day of year) or d (day).
The following example calculates the number of days between today and New Year's Eve:
NumDays = DateDiff('y', Now(), '31-Dec')
In this case, the year is omitted from date2. When you omit the year, DateDiff uses the current year. If a date is enclosed by number signs (#), the current year becomes a permanent part of that date. If you enclose the date in double quotation marks (''), however, the current year is inserted in your code each time it is executed. This makes it possible to write code that can be used over multiple years.
When interval is w (weekday), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date1 but not date2. If interval is ww (week), however, the DateDiff function returns the number of calendar weeks between the two dates; it counts the number of Sundays between date1 and date2. DateDiff counts date1 if it falls on a Sunday; but it doesn't count date2, even if it does fall on a Sunday.
If date1 refers to a later point in time than date2, the DateDiff function returns a negative number.
Returns a specified part of a given date.
Syntax
DatePart(interval, date [firstweekday][, firstweek])
Remarks
The DatePart function uses the following arguments.
Argument Description
interval String expression that is the interval of time you want to return
date Date that you want to inspect or the name of a Date/Time field
firstweekday An integer that specifies the first day of the week. It can be any of the following:
1 Sunday (default)
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday
firstweek An integer that specifies the first week of the year. It can be any of the following:
0 Use the First Week setting in the Options dialog box
1 Start on January 1 (default)
2 Start with the first four-day week
3 Start with the first full week
You can use the DatePart function to inspect a date and return a specific interval of time. For example, you can use DatePart to calculate the day of the week for an order's ship date or the current hour.
The exact value returned by DatePart can depend on the settings of the First Weekday and First Week options in the Options dialog box (View menu) or by the values of the firstweekday or firstweek arguments. For example, if you set firstweekday to Monday, then DatePart returns the value 3 (Wednesday) for the date 11/3/93:
Const SUNDAY = 1, MONDAY = 2
Dim DayOfWeek As Integer
DayOfWeek = DatePart('w', '11/3/93', MONDAY)
If you change firstweekdayto Sunday, then DatePart would return 4 for the same date.
The following table lists the valid time periods and their interval values.
Time period interval
Year yyyy
Quarter q
Month m
Day of year y
Day d
Weekday w
Week ww
Hour h
Minute n
Second s
The following example calculates which day of the week New Year's Eve falls on:
FindDay = DatePart('w', '31-Dec')
In this case, the year is omitted from date. When you omit the year, DatePart uses the current year. If a date is enclosed by number signs (#), the current year becomes a permanent part of that date. If you enclose the date in double quotation marks (''), however, the current year is inserted in your code each time it is executed.
The next example determines the calendar quarter in which an order was placed:
FindQtr = DatePart('q', Forms![Orders]![Order Date])
Returns the arithmetic mean of a set of values in a specified set of records (domain).
Syntax
DAvg(expr, domain[, criteria])
Remarks
The DAvg function uses the following arguments.
Argument Description
expr String expression identifying the field that contains the numeric data you want to average or an expression that performs calculations using the data in that field. Operands in expr can include the name of a table field, a control on a form, a constant, or a function (which can be either intrinsic or user-defined but not one of the other domain aggregate or SQL aggregate functions).
domain String expression identifying the records that constitute the domain. It can be a table name or a query name.
criteria Optional string expression used to restrict the range of data on which DAvg is performed. For example, criteria could be the WHERE clause in an SQL expression (without the word WHERE). If criteria is omitted, DAvg evaluates expr against the entire domain.
The average calculated by DAvg is the arithmetic mean (the sum of the values divided by the number of values). You might use DAvg to calculate average freight cost.
If the criteria argument contains text that isn't numeric other than field names, you must enclose the text in single quotation marks (' '). In the following example from the Orders table in the NWIND.MDB database, Ship Country is the name of a field, and UK is a string literal:
X = DAvg('[Freight]', 'Orders', '[Ship Country] = 'UK'')
Y = DAvg('[Freight]', 'Orders', '[Ship Via] = 1')
If you use DAvg in a macro and criteria includes a control name that is also the name of a field in domain, you must qualify the name of the control by referring to the form on which it appears. The following example shows how you might specify a field and a control that are both named Ship Region:
=DAvg('[Freight]', 'Orders', '[Ship Region] = Form.[Ship Region]')
Records that contain null fields aren't included in the calculation.
Note Unsaved changes to records in domain are not included when you use this function. If you want the DAvg function to be based on the changed values, you must first save the changes by using the Update method, choosing the Save Record command from the File menu, or moving the focus to another record.
Se utilizeaza analog cu Davg, dar fac operatii diferite.
Returns a field value in a specified set of records (domain).
Syntax
DLookup(expr, domain[, criteria])
Remarks
The DLookup function uses the following arguments.
Argument Description
expr String expression identifying the field that contains the data you want to return or an expression that performs calculations using the data in that field. Operands in expr can include the name of a table field, a control on a form, a constant, or a function (which can be either intrinsic or user-defined but not one of the other domain aggregate or SQL aggregate functions).
domain String expression identifying the records that constitute the domain. It can be a table name or a query name.
criteria Optional string expression used to restrict the range of data on which DLookup is performed. For example, criteria could be the WHERE clause in an SQL expression (without the word WHERE). If criteria is omitted, DLookup evaluates expr against the entire domain.
You can use DLookup to return a field value based on the value of another field. In the following example from the Employees table in the NWIND.MDB database, DLookup uses the Employee ID field to return the corresponding last name:
SELECT DLookup('[Last Name]', 'Employees', '[Employee ID] = 24') as X, Field1 FROM Table1
If the criteria argument contains text that isn't numeric other than field names, you must enclose the text in single quotation marks (' '). In the following example from the Orders table in the NWIND.MDB database, Ship Postal Code is the name of a field, and 94117 is a string literal:
DLookup('[Ship Via]', 'Orders', '[Ship Postal Code] = '94117'') as X
DLookup('[Ship Via]', 'Orders', '[Ship Via] = 1') AS Y
You can include a parameter that results from a parameter query or from a field on a form. If you want criteria to include a parameter, you can include it as shown in the following example. Note that in the expression for X, [param] is a string literal, and in the expression for Y, [param] is a numeric value.
DLookup('[Ship Via]', 'Orders', '[Ship Postal Code] = '' & [param] & ''') AS X
DLookup('[Ship Via]', 'Orders', '[Ship Via] = ' & [param]) AS Y
If you use DLookup in a macro and criteria includes a control name that is also the name of a field in domain, you must qualify the name of the control by referring to the form on which it appears. The following example shows how you might specify a field and a control that are both named Ship Region:
DLookup('[Freight]', 'Orders', '[Ship Region] = Form.[Ship Region]')
Even if more than one record satisfies criteria, DLookup returns only one field. If no record satisfies criteria or if domain contains no records, DLookup returns a Null.
Note Unsaved changes to records in domain are not included when you use this function. If you want the DLookup function to be based on the changed values, you must first save the changes by using the Update method, choosing the Save Record command from the File menu, or moving the focus to another record.
Returns an integer between 1 and 31, inclusive, that represents the day of the month corresponding to the date provided as an argument.
Syntax
Day(date)
Month(date)
Year(date)
Remarks
The argument number is any numeric expression that can represent a date and/or time from January 1, 100 through December 31, 9999, where January 1, 1900 is 2. Numbers to the left of the decimal point in number represent the date; numbers to the right represent the time. Negative numbers represent dates prior to December 30, 1899.
If number is Null, this function returns a Null.
Returns the position of the first occurrence of one string within another string.
Syntax 1
InStr( [start,] strexpr1, strexpr2)
Syntax 2
InStr(start, strexpr1, strexpr2, compare)
Remarks
The InStr function uses these arguments.
Argument Description
start Numeric expression that sets the starting position for each search; start must be between 1 and approximately 65,535. If start is omitted, the search of strexpr1 begins at the first character position. The start argument is not optional if the compare argument is specified.
strexpr1 String expression being searched.
strexpr2 String expression being sought.
compare Specifies the string-comparison method. The argument compare must be 0, 1, 2, or the value of the CollatingOrder property of a Field object or a Database object, and start must also be specified.
If compare is 0, string comparison is case-sensitive; so, for example, an uppercase M doesn't match a lowercase m.
If compare is 1, string comparison is not case-sensitive; so, for example, an uppercase M matches a lowercase m.
If compare is 2, the string-comparison method is Database, which uses the New Database Sort Order.
If compare is omitted, InStr uses the string-comparison method set by the Option Compare statement. For Option Compare Binary, compare is 0; for Option Compare Text, compare is 1; for Option Compare Database, compare is 2. If the module doesn't contain an Option Compare statement, compare is 0 (Binary).
If strexpr2 is found within strexpr1, InStr returns the position at which the match was found. If strexpr2 is zero-length, start is returned. If start is greater than strexpr2, strexpr1 is zero-length, or strexpr2 can't be found, InStr returns 0.
If either string expression is Null, the function returns a Null. If start or compare is Null, an error occurs.
Returns a value indicating whether or not a Variant argument can be converted to a date.
Syntax
IsDate(variant)
Remarks
The argument variant can be any Variant expression of VarType 7 (Date) or of VarType 8 (String). The IsDate function returns True (-1) if the Variant can legally be converted to a date; otherwise, it returns False (0). The range of valid dates is January 1, A.D. 100 through December 31, A.D. 9999.
Returns a value that indicates whether or not a Variant contains the special Null value.
Syntax
IsNull(variant)
Remarks
You can use the IsNull function in Access Basic or in an expression.
In Access Basic, the argument variant can be any Variant expression. The IsNull function returns True (-1) if the expression contains the Null value; otherwise, it returns False (0).
The Null value indicates that the Variant contains no data. Null is not the same as Empty, which indicates that a Variant has not yet been initialized. It is also not the same as a zero-length string, which is often referred to as a null string.
Important Using the IsNull function is the only way from within Access Basic to determine whether or not a Variant expression contains a Null value. Expressions that you might expect to evaluate True under some circumstances, such as If var = Null and If var <> Null, are always False. Because of Null propagation, any expression containing a Null is itself Null and therefore False.
In an expression, you can use IsNull to determine whether a control or field contains data. For example, you could use the following expression in the Condition column of the Macro window to validate the contents of the First Name control on the Employees form. If the expression evaluates true (nonzero), a message could be displayed to alert the user that the field contains no data.
IsNull(Forms!Employees![FirstName])
Tip In a query, a faster way to check for Null values is by typing 'IsNull' in the Criteria row of the QBE grid. For queries that will be passed to ODBC data sources, don't use the IsNull function; instead, use the Is operator with the Null reserved word. For more information on choosing between the IsNull function and Is Null, see Working with Nulls and Zero-Length Strings.
Returns a value indicating whether or not a Variant variable can be converted to a numeric data type.
Syntax
IsNumeric(variant)
Remarks
The argument variant can be any Variant expression. The IsNumeric function returns True (-1) if the expression can be converted to a number; otherwise, it returns False (0). Expressions that can be converted to a numeric data type include Variant variables of VarType 0 (Empty), of any numeric value (VarType 2-6), of VarType
7 (Date), and of VarType 8 (String) if the String can be interpreted as numeric.
Return the leftmost n characters of a string argument.
Syntax
Left[$](strexpr, n)
Remarks
Left returns a Variant; Left$ returns a String.
The argument strexpr can be any string expression. However, only Left can accept a Variant of VarType 1 (Null) as strexpr, in which case, a Null is returned.
The argument n is a Long expression indicating how many characters to return. It must be between 0 and approximately 65,535, inclusive. If n is 0, the return value is a zero-length string. If n is greater than or equal to the number of characters in strexpr, the entire string is returned.
To find the number of characters in strexpr, use Len(strexpr).
Returns the number of characters in a string expression or the number of bytes required to store a variable.
Syntax 1
Len(strexpr)
Syntax 2
Len(variablename)
Remarks
If you use the first syntax, Len returns the number of characters in the argument strexpr.
If you use the second syntax, Len returns the number of bytes required to store a variable of the given data type. If variablename is a Variant, Len always returns the number of bytes required to store the Variant data as a String, regardless of the VarType.
Because Len works with user-defined data types as well as fundamental Access Basic data types, the second syntax is particularly useful for determining record size when you are performing file input/output with random-access files. However, if a user-defined data type contains Variant or variable-length String elements, Len may not be able to properly determine the actual number of storage bytes required.
If the argument to Len is Null, the function returns a Null.
Return a copy of a string with leading (leftmost), trailing (rightmost), or both leading and trailing spaces removed.
Syntax
[L | R]Trim[$](stringexpr)
Remarks
LTrim[$] removes leading spaces from a string; RTrim[$] removes trailing spaces. Trim[$] removes leading and trailing spaces.
LTrim, RTrim, and Trim each return a Variant; LTrim$, RTrim$, and Trim$ each return a String.
The stringexpr argument can be any string expression. However, only LTrim, RTrim, and Trim can accept a Variant of VarType 1 (Null) as stringexpr, in which case, a Null is returned.
Return the minimum or maximum of a set of values contained in a specified field on a query, form, or report.
Syntax
Min(expr)
Max(expr)
Remarks
The Min and Max functions use the following argument.
Argument Description
expr String expression identifying the field that contains data you want to evaluate or an expression that performs a calculation using the data in that field. Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate or domain aggregate functions).
You can use Min and Max to determine the smallest and largest values in a field, including a Text field. For example, you could use Min and Max to return the lowest and highest freight cost.
You can use Min and Max in a query expression, a calculated control on a form or report (except in a page header or footer), or an SQL statement in a query's SQL view in the Query window.
Returns a date that represents the current date and time according to the setting of the computer's system date and time.
Syntax
Now[( )]
Remarks
The parentheses are optional only when you use Now in Access Basic.
The Now function returns a Variant of VarType 7 (Date) containing a date and time that are stored internally as a double-precision number. This number represents a date and time from January 1, 100 through December 31, 9999, where January 1, 1900 is 2. Numbers to the left of the decimal point represent the date; numbers to the right represent the time.
Returns a string indicating where a number occurs within a calculated series of ranges.
Syntax
Partition(number, start, stop, interval)
Remarks
The Partition function is most useful in queries. You can create a select query that shows how many orders fall in various ranges (for example, order values from 1 to 1000, 1001 to 2000, and so on).
You might also use Partition to show whether you are getting orders out on time. You can design a crosstab query that shows how many orders arrive on time, 1-5 days late, 6-10 days late, and so on. You can even create a graph that summarizes this information.
The Partition function uses the following arguments.
Argument Description
number Long number that you want to evaluate against the ranges.
start Long number that is the start of the overall range of numbers. It can't be less than 0.
stop Long number that is the end of the overall range of numbers. It can't be equal to or less than start.
interval Long number that is the interval spanned by each range in the series from start to stop. It can't be less than 1.
The following table shows how the ranges are determined using three sets of start, stop, and interval arguments. The First range and Last range columns illustrate the text Partition returns. The ranges are represented by lowervalue:uppervalue, where the low end (lowervalue) of the range is separated from the high end (
uppervalue) with a colon (:).
start stop interval Before first First range Last range After last
020100 991991010 5 1020 ' : -1'' : 19'' : 99' ' 0: 4'' 20: 29'' 100: 119' ' 95: 99'' 190: 199'' 1000: 1010' ' 100: '' 200: '' 1011: '
In this table, the third example shows the result when start and stop define a set of numbers that can't be evenly divided by interval. The last range extends to stop (11 numbers) even though interval is 20.
The Partition function identifies the particular range in which number falls and returns a string that describes that range. For example, if you specify a range of values from 1 to 100 and an interval of 5, the following ranges result when number is above, below, or within the range of numbers.
number Value is Range
111 Above ' 101: '
0 Below ' : 0'
44 Within ' 41: 45'
If necessary, Partition returns a range with enough leading spaces so that there are the same number of characters to the left and right of the colon as there are characters in the stop argument, plus one. This ensures that if you use Partition with other numbers, the resulting text will be handled properly during any subsequent sort operation.
If interval is 1, the range is number:number, regardless of the start and stop arguments. For example, if interval is 1, number is 100, and stop is 1000, Partition returns ' 100: 100'.
If any of the arguments is Null, Partition returns a Null.
Partition Function Example 1
This example creates a select query that inspects an Order Amount field in an Orders table. It uses the Partition and Count functions to evaluate and count the number of orders in each of the ranges. The ranges are defined by the arguments to Partition: start = 1, stop = 20000, interval = 1000.
You can enter the expression below in the SQL dialog box.
SELECT DISTINCTROW Partition([Order Amount], 1, 20000, 1000) AS Range,
Count(Orders.[Order Amount]) AS [Count] FROM Orders
GROUP BY Partition([Order Amount], 1, 20000, 1000)
ORDER BY Partition([Order Amount], 1, 20000, 1000),
Count(Orders.[Order Amount]);
The result of the query could look like the following table.
Range Count
1: 1000 588
1001: 2000 274
2001: 3000 111
3001: 4000 38
4001: 5000 33
5001: 6000 6
6001: 7000 8
7001: 8000 2
8001: 9000 4
9001: 10000 3
10001: 11000 6
11001: 12000 2
12001: 13000 1
13001: 14000 1
16001: 17000 1
This table contains no orders for the ranges 14001:15000 and 15001:16000.
Partition Function Example 2
This example creates a select query that inspects an Order Amount field in an Orders table. For each customer, it calculates the number of orders that fall within each of several ranges. The ranges are defined by the arguments to Partition: start = 1, stop = 20000, interval = 1000.
You can enter the expression below in the SQL dialog box.
Returns a value indicating the sign of a number.
Syntax
Sgn(number)
Remarks
The argument number can be any valid numeric expression. Its sign determines the value returned by the Sgn function:
If number > 0, then Sgn(number) returns 1.
If number = 0, then Sgn(number) returns 0.
If number < 0, then Sgn(number) returns -1.
Returns the sum of a set of values contained in a specified field on a query, form, or report.
Syntax
Sum(expr)
Remarks
The Sum function uses the following argument.
Argument Description
expr String expression identifying the field that contains the numeric data you want to add or an expression that performs a calculation using the data in that field
. Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate or domain aggregate functions).
Sum totals the values in a field. For example, you could use Sum to determine the total cost of freight charges.
The Sum function treats records that contain Null fields as having a value of 0. The following example shows how you can calculate the sum of the products of Unit Price and Quantity fields:
SELECT Sum([Unit Price] * [Quantity]) AS [Total Revenue] FROM [Order Details];
You can use Sum in a query expression, a calculated control on a form or report (except in a page header or footer), or an SQL statement in a query's SQL view in the Query window.
Returns the number of seconds that have elapsed since 12:00 A.M. (midnight).
Syntax
Timer [( )]
Remarks
The parentheses are optional only when you use Timer in Access Basic.
You can use the Timer function with the Randomize statement to generate a seed for the Rnd (random-number) function. You can also use Timer to time programs or parts of programs.
You can use INSERT INTO to create an append query, which adds records from a table or query in the current database to the end of another table or query in either the current database or another database. For example, you might want to append a new table of customer records to your active Customers table.
Syntax
Multiple-record append query:
INSERT INTO target [IN externaldatabase]
SELECT [source.]field1[, field2[, ]
FROM tableexpression
Single-record append query:
INSERT INTO target [(field1[, field2[, ]])]
VALUES (value1[, value2[, ])
Remarks
The INSERT INTO statement uses these arguments.
Argument Description
target The name of the table to append records to.
externaldatabase The path to an external database. For a description of the path, see the IN clause.
source The name of the table to copy records from.
field1, field2 In the syntax for a multiple-record append query, these are the names of the fields to be appended.
In the syntax for a single-record query, these are the names of fields to which you want to append specific values in a single new record.
tableexpression The name of the table or tables from which records will be inserted. This argument can be a single table name or a compound resulting from a JOIN or subquery.
value1, value2 The values to insert into the specific fields of the new record. Each value will be inserted into the field that corresponds to the value's position in the list: value1 will be inserted into field1 of the new record, value2 into field2, and so on. You must enclose each value in double quotation marks (' ') and separate pairs of values with a comma.
Notes
INSERT INTO is optional but when included precedes the SELECT statement.
If your destination table contains a primary key, make sure you append unique, non-Null values to the primary key field or fields; if you don't, Microsoft Access won't append the records.
If you append records to a table with a Counter field, don't include the Counter field in your query if you want Microsoft Access to renumber the appended records. Include the Counter field in the query if you want to retain the original values from the field. If there are duplicate values, however, Microsoft Access won't append the records.
Use the IN clause to append records to a table in another database.
To create a new table, use the SELECT INTO statement instead to create a make-table query.
To find out which records will be appended before you run the append query, first preview the results of a select query that uses the same selection criteria.
An append query copies records from one or more tables to another. The tables that contain the records you append aren't affected by the append query.
Instead of appending existing records from another table, you can specify the value for each field in a single new record using the VALUES clause. If you omit the field list, the VALUES clause must include a value for every field in the table; otherwise, the INSERT will fail. Use an additional INSERT INTO statement with a VALUES clause for each additional record you want to create.
If you create an INSERT INTOVALUES query in SQL view, save and close the query, and then reopen it, you'll see that Microsoft Access has converted the VALUES clause to a SELECT clause. This doesn't alter the results of the query.
Using INSERT INTO is equivalent to setting the DestinationTable property in the query property sheet in a query's Design view.
INSERT INTO Customers
SELECT [New Customers].*
FROM [New Customers]; Selects all records in the New Customers table and adds them to the Customers table
INSERT INTO Employees ([First Name],
[Last Name], Title)
VALUES ('Harry', 'Washington', 'Trainee'); Creates a new record in the Employees table
INSERT INTO Employees
SELECT Trainees.*
FROM Trainees
WHERE [Hire Date] < Now() - 30; Selects all trainees who were hired more than 30 days ago and adds their records to the Employees table
INSERT INTO LotRequestParam (SelIdent, SelCant, SelPret, SelPretTrDest, SelGestTrDest)
SELECT LotRequestIdentToParamFull.Ident, LotRequestIdentToParamFull.SelCant, LotRequestIdentToParamFull.SelPret, LotRequestIdentToParamFull.SelPretTrDest, LotRequestIdentToParamFull.SelGestTrDest
FROM LotRequestIdentToParamFull; Baga o parte din campurile tabelei LotRequestIdentToParamFull in tabela LotRequestParam
You can use SELECTINTO to create a make-table query.
Syntax
SELECT field1[, field2[, ]] INTO newtable [IN externaldatabase]
FROM source
Remarks
The SELECTINTO statement uses these arguments.
Argument Description
field1, field2 The name of the fields to be copied into the new table.
newtable The name of the table to be created. It must conform to standard naming conventions. If newtable is the same as the name of an existing table, Microsoft Access replaces the structure and data of the old table with those of the new table.
externaldatabase The path to an external database. For a description of the path, see the IN clause.
source The name of the existing table from which records will be selected. This can be either a single table or a compound resulting from a JOIN or a subquery.
You can use make-table queries to archive records, make backup copies of your tables, or make copies to export to another database or use as a basis for reports that display data for a particular time period. For example, you could produce a Monthly Sales by Region report by running the same make-table query each month.
Notes
You may want to define a primary key for the new table. When you create the table, the fields in the new table inherit the data type and field size of each field in the query's underlying tables, but no other field or table properties are transferred.
To add data to an existing table, use the INSERT INTO statement instead to create an append query.
To find out which records will be selected before you run the make-table query, first view the results of an action query that uses the same selection criteria.
SELECT Employees.* INTO [Emp Backup]
FROM Employees; Selects all records in the Employees table and copies them into a new table named Emp Backup.
SELECT Employees.[First Name], [Last Name] INTO Trainees
FROM Employees
WHERE Title = 'Trainee'; Creates a new table called Trainees that contains only employee records that have the title Trainee.
SELECT Employees.*
INTO Employees IN 'BACKUP.MDB'
FROM Employees; Makes a copy of the Employees table and places the new table in the BACKUP database.
SELECT Employees.*, Salary INTO Trainees
FROM Employees INNER JOIN Payroll
ON Employees.[Employee ID] = Payroll.[Employee ID]
WHERE Title = 'Trainee'; Creates a new table that contains employee and payroll data for all trainees. The Employees and Payroll tables have a one-to-one relationship. The new table contains all of the data from the Employees table plus the Salary field from the Payroll table.
You can use UPDATE to create an update query that changes values in fields. For example, you can reduce the price of all beverages by 10 percent or increase mailing charges for all clothing by 3 percent in tables you specify.
Syntax
UPDATE table
SET newvalue
WHERE criteria;
Remarks
The UPDATE statement uses these arguments.
Argument Description
table The name of the table whose data you want to modify.
newvalue An expression that determines the value to be inserted into a particular field in the updated records.
criteria An expression that determines which records will be updated. Only records that satisfy the expression are updated.
Notes
UPDATE is especially useful when you want to change many records or when the records that you want to change are in multiple tables.
You can change several fields at the same time. The following example increases the Order Amount values by 10 percent and the Freight values by 3 percent:
UPDATE Orders
SET [Order Amount] = [Order Amount] * 1.1, Freight = Freight * 1.03
WHERE [Ship Country] = 'UK';
No datasheet is displayed when you use the UPDATE statement. If you want to know which records will be changed, first view the results of a select query that uses the same criteria, and then run the update query.
If you want to confirm each change, use the Replace command on the Edit menu of the form or datasheet rather than an update query.
In some cases, you can edit the data in the query output to change the data in the underlying tables; in other cases, you can't. For more information, see Updating Underlying Tables.
UPDATE Employees
SET [Reports To] = 5
WHERE [Reports To] = 2; Changes values in the Reports To field to 5 for all employee records that currently have Reports To values of 2.
UPDATE Products
SET [Unit Price] = [Unit Price] * 1.1
WHERE [Supplier ID] = 8
AND Discontinued = No; Increases the Unit Price for all nondiscontinued products from supplier 8 by 10 percent.
UPDATE Suppliers INNER JOIN Products
ON Suppliers.[Supplier ID] = Products.[Supplier ID]
SET [Unit Price] = [Unit Price] * .95
WHERE [Company Name] = 'Tokyo Traders'
AND Discontinued = No; Reduces the Unit Price for all nondiscontinued products supplied by Tokyo Traders by 5 percent. The Products and Suppliers tables have a many-to-one relationship.
You can use the DELETE statement to create a delete query that removes records from one or more of the tables listed in the FROM clause that satisfy the WHERE clause.
Syntax
DELETE [table.*]
FROM tableexpression
WHERE criteria
Remarks
The DELETE statement uses these arguments.
Argument Description
table The optional name of the table from which records will be deleted.
tableexpression The name of the table or tables from which records will be deleted. This argument can be a single table name or a compound resulting from a JOIN or subquery.
criteria An expression that determines which records to delete.
Notes
DELETE is especially useful when you want to delete many records.
In a multiple-table DELETE statement, you must include the table argument. If you specify more than one table to delete records from, they must all be many-only tables.
If you want to delete all of the records in a table, deleting the table itself may be more efficient than running a delete query. In SQL view, use a DROP TABLE statement. If you delete the table, however, the structure is lost. In contrast, when you use DELETE, only the data is deleted; the table structure and all of the table properties, such as field attributes and indexes, remain intact.
No query output or Datasheet view is produced when you use the DELETE statement.
You can use DELETE to remove records from a single table or from multiple tables in a one-to-one relationship. To remove records from tables in a one-to-many relationship, you must run two queries unless you have established cascade delete referential integrity on the tables, in which case you can delete records as a group.
A delete query deletes entire records, not just data in specific fields. If you want to delete values in a specific field, create an update query that changes the values to Null values.
Important
Once you remove records using a delete query, you can't undo the operation. If you want to know which records will be deleted, first view the datasheet of a select query that uses the same criteria, and then run the delete query.
Maintain backup copies of your data at all times. If you delete the wrong records, you can retrieve them from your backup copies.
DELETE Statement Examples
DELETE *
FROM Employees
WHERE Title = 'Trainee'; Deletes all records for employees whose title is Trainee. When the FROM clause includes only one table, you don't have to list the table name in the DELETE statement.
DELETE Employees.*
FROM Employees INNER JOIN Payroll
ON Employees.[Employee ID] = Payroll.[Employee ID]
WHERE Title = 'Trainee'; Deletes all records for employees whose title is Trainee and who also have a record in the Payroll table. The Employees and Payroll tables have a one-to-one relationship.
|
Politica de confidentialitate | Termeni si conditii de utilizare |
Vizualizari: 2404
Importanta: 
Termeni si conditii de utilizare | Contact
© SCRIGROUP 2024 . All rights reserved
