First, understand the in-depth understanding
a) subquery: Source from a query instead of the table
b) Use the Query Design Window Guidelines: The criteria in the same line is set to "AND" relationship, and different lines are "OR" relationships. (First, go down, then one line) "Alias: Field Name" format can specify a field to specify other queries: Select the query (from one or more tables) Cross-table query, operational query (create new with query results) Table, etc.), parameter query: Enter "[Tip Information]" in the guideline, will prompt the input parameter content as the field criterion query à generated table query: You can generate a new table with query results
c) Query Optimization: From left to right, from the top-down criterion initial query Do not include a large field such as OLE object
d) query operator
i. Arithmetic operation , -, *, /, mod, ^, / (whole), &, Between, in, Like ... (arithmetic, assignment, comparison, logic, connection, identifier,)
Ii. Text Comparison
Iii. Identifier: like a field name, name constant: Ture, Flase, NO, Yes, Null ...
IV. Function VBA Defines more than 150 functions, time functions, text operation functions, data types, conversions, mathematics, and triangle, finance, routine purpose functions (for creating queries, forms, reports, etc.), other (Windows applications) DDE, summary SQL summation, etc.) use immediately window debugging
v. example: Forms! PersonalactionsàPersonalanaAmeàcustomers! CompanyNameàcustomers table companyName Field Is Null, is not null, Like "SOF *", LIKE "SOF ??", in ("
Shanghai
","
Beijing
","
Chengdu
") BetWeen 1 and 5
CHR (13) à Enter (CR) CHR (10) à wrap (LF) Date / Time format Use "#" surrounded by: # 10: 30: 29 #, # 1-jan-80 # ... identifier There is a split point, including [] enclose: [Order Details]! Quantity
e) Multi-table query and crosstab query
i. First confirm which / which field has the same value, you can establish a related relationship, which is generally the same name of the primary key and foreign key, which is easier when designing the connection.
II. Connection Equivalence Connection (Internal Connection): Displays the query record in a table and the corresponding record in another table (where field1 = filed2) [use the most] {INNER JOIN} external connection: (left) can display participating inquiry All records of the table, regardless of whether there is a corresponding record in the connection table (right): Display all records in the multi-table, regardless of whether there is a corresponding record in the home table. Can be used for database maintenance (LEFT JOIN / Right Join) Double-click the connection attribute of two tables in the designer (there will be corresponding arrows) {1 equivalent, 2 left, 3 right even} self-connection: Combined with data Theta connection in a single table: compare operator
III. Querying the query window on the search property à toolbar, such as setting the unique value of the query (remove excess line). . IV. Review Wizard / Add Some Data Sheet / Add Subquest
v. Update the data in the query General query record recorded the RecordSet object that is not updatable DYNASET: Query Properties "DINSTINCT" is "Yes" to use the total function (SUM ()), Using SQL total functions in a cross-table query in a multi-relational table does not have the only (no repetition) index
Vi. EG: Number of issues: select count (tblbbspost.pid) AS PIOUNT, FORMAT ([PostTime], "YYYY / MM / DD") AS expression 1 from tblbbspost group by Format ([PostTime], "YYYY / MM / DD"); parameter query: Select Count (TBLBSPOSPOST.PID) AS PIOUNT, FORMAT ([PostTime], "YYYY / MM / DD") AS Expression 1 from TBLBSPOST WHERE ((Format) [PostTime], "YYYY / MM / DD"))) = [Enter the number of issues you want to query (YYYY-MM-DD):])) Group by Format ([PostTime], "YYYY / MM / DD" );
