Query Multiple Conditions Simultaneously

Date handling functions:

Date(), gives the current machine date

Now() returns the system date and time. Day(date): returns the day of the month, with values from 1 to 31 month(date): returns the month of the date year(date): returns the year of the date

Dateadd(argument1, n, date) returns a date value by adding to date n the day or month or year or quarter depending on argument1.

For example: Dateadd(“d”,10,date()) adds 10 days to the current date.

DatePart(argument1,date): Returns a component that is a day or month or year or quarter from a date value depending on argument1. Argument1 can take the above values. Argument1 can take the following values: “d” – day, “m” – month, “q” – quarter, “yyyy” – year.

For example: Datepart(“q”,date()) for the current quarter. String manipulation functions:

Len(St): gives the length of string St

Trim(St): Removes spaces from both ends of the string st Left(String,n) for a substring of the string consisting of n characters on the left Right(String,n) for a substring of the string consisting of n characters on the right

Mid(String, start, n) Returns a substring of String starting at position Start and consisting of n characters. Str(number) converts a number to a string.

Val(st) converts string St to number, if not satisfied then returns value 0. For example: Left(“Ha Noi”,2)= “Ha”, Right(“Ha Noi”,3)= “Noi”,

Mid(“Dang Thu Hien”,6,3)= “Thu”, Str(2006) results in string “2006”. Ucase(Str): Converts string Str to uppercase.

Lcase(Str): Converts string Str to lowercase. Conditional function:

IFF(condition, condition1, condition2), this function will return value bt1 if the condition expression is true, otherwise it will return value bt2. Each condition1, condition2 can itself be an IFF.

For example: IFF(x>5,10,100), gives the value 10 if x>5, otherwise (x<=5) gives the value 100. Summary example:

Suppose there is a Quanlyhanghoa databaseconsisting of the following tables:

Hang( MaH , TenH, Loai, SLTon) to store all items in the store.

Khach ( Code , Name, Address, Phone) stores customer information.

HoaDon ( SoHD , NgayHD, MaK) is used to store information about sales.

ChitietHD( SoHD, MaH , SLban, Dongia) is used to store details of each item in the invoice. The relationship between HoaDon and ChitietHD is one-to-many (1-n) through the SoHD field, between Hang and ChitietHD is 1-n through the MaH field. Between Khach and HoaDon is 1-n through the MaK field

Write queries that answer the following requirements:

1) Give a table with columns SoHD, NgayHD, TenK, Diachi and show only invoices written on December 12, 2011 of customers in Hanoi.

Figure 3.8: querying multiple conditions at the same time

2) Given a table with columns MaH, TenH, Loai, SLton. Only display items with SLton between 50 and 100, and sort in descending order of SLton.

Figure 3.9: Numeric condition query

3) Give a table with columns SoHD, TenH, SLban, Dongia, ThanhTien

where ThanhTien=SLban*Dongia, and only display rows with SLban>10 and Thanhtien >10000000, sorted in descending order of ThanhTien.

Figure 3.10: Multiple condition query

4) Give a table with columns SoHD, NgayHD, TenK, TenH, SLban, Dongia, ThanhTien. Only display items with SLban>5 or Dongia>7000000, and sort in descending order of Thanhtien.

Figure 3.11: Querying multiple conditions asynchronously

3. Other types of queries

3.1 Totals query

This query allows to group records and then perform the following calculations on each group:

Sum: Calculate the sum of values in a Number field Avg: Calculate the average value of a Number field Min: Find the smallest value of a Number field Max: Find the largest value of a Number field Count: Count the number of non-empty values in the field

First: Returns the value of the first record field in the group Last: Returns the value of the last record field in the group Steps to create a sum query

1. How to create a simple query. Select the source data for the query.

2. Select Totals in the View menu. The Total line will appear on the QBE.

3. Select fields:

- Group by (Group by on Total box)

- Conditions and criteria for participating in grouping and calculating the total:

+ There is the word Where in the Total box

+ There is a conditional expression in the Criteria cell

- Calculate (choose a function and put it in the Total box, rename the field to make it meaningful to calculate)

- Select display order (use Sort box of Group by field and calculated field)

- Define the criteria for displaying results (enter conditions into the Criteria box of grouping fields and calculation fields).

How sum query works:

1. Based on the conditions set in the fields (with the where value in the total cell) to filter out the records to participate in the analysis and calculation.

2. Group and sort the records in each group according to the grouping fields (with the Group by value in the Total cell). This group is a series of records with the same values in the grouping fields.

3. Perform calculations by group on calculation fields (with Sum, Avg,...) in the Total cell.

4. If there are conditions on the grouping and calculation fields, only rows that satisfy these conditions will be displayed.

5. If the Top Value property of the query is used, only the top few rows will be displayed. Suppose the value of Top Value is 2, then only the first 2 rows of each group will be displayed in the result table.

For example : Given the Quanlyhanghoa database as above. Please provide a summary table of the number of customers at each address (here, the customer's address only includes the province name), only count customers with phone numbers (meaning the phone number column is not empty), and only display addresses starting with the letter H.

We perform the following steps:

1. Select the Customer table as the source for the query

2. Select the fields Address, Mak, Dienthoai

3. Select the Totals item of the view menu

4. At Diachi school:

- Click on the Total row and select Group by

- Type condition: like “H*” in Criteria row

5. At Mak field: Select Count in the Total row and rename it Soluongkhach

6. At Dienthoai school

- Select Where in the Total box

- Type Is Not Null in the Criteria box

- The condition field will not be displayed in the result table. Then we have the design window as shown in figure 3.12

Figure 3.12: Sum query

3.2 Parameter Query

Parameter Queries are so named because they require a parameter to be entered before the query can be executed. Instead of setting fixed conditions when constructing the query, we can set more “dynamic” conditions at each time the query is run. That is, the values in the conditional expressions do not need to be pre-determined but will be prompted for each time the query is executed. We can prompt for one or more parameters.

Construction steps

When creating a conditional expression in place of a specific value, we pass in a message enclosed in two brackets: [ ]. Such a message is called a parameter.

The message line will appear as an instruction when entering data to execute the query. Example illustration

Suppose with the Quanlyhanghoa database as above, we want to design a query, so that when running, we can enter any date and it lists all the invoices created on that day.

We do as follows: Create a new query with the source data being the HoaDon table, in the Criteria line of the NgayHD field we enter the message line: [Date to view:], The design window is as shown in figure 3.13

Figure 3.13: Parameter query

When executing the query, the window Figure 3.14 appears.

Figure 3.14: Parameter window

Enter the date you want to view and press OK. All invoices created on the date you just entered will be displayed.

To query parameters more stably and accurately, it is recommended to declare data types for parameters.

Specify data type for parameter

To specify data types for parameters, do the following:

- Open parameter query in Design View

- Select Parameters from the Query menu to open the Query Parameters window.

- Enter the parameter name and select the appropriate data type and press OK. The Query Parameters window will appear as shown in Figure 3.15:

Figure 3.15: Declaring data type for parameter

3.3 Crosstab Query

a) Uses:

- Crosstab queries are used to summarize data and present the results in a condensed form like a spreadsheet, with a very readable form. Therefore, it is often used to create data for comparison and predict the direction of data development.

- There are 2 main parts in Crosstab query:

+ Perform grouping and calculations (like sum queries)

+ Then in each group we divide into subgroups, perform the calculation on each subgroup and present the results in each column (each subgroup corresponds to a column)

For example: Suppose in the Hang table , the ChitietHD table of the Quanlyhanghoa database includes the fields MaH, TenH, Loai, SoHD, MaH, SLban, we can use a Crosstab query to produce a statistical table to see how many types of goods are in each invoice and what is the quantity of each type of goods?:

SoHD