Functions like LEFT and RIGHT in Microsoft Access are pivotal in data manipulation for extracting and analyzing specific portions of a string in a field. This can be useful for tasks such as parsing out codes from a SKU, extracting year values from a date formatted string, or formatting portions of text data to meet reporting needs by selectively pulling relevant parts of a text field .

Wildcard characters in Microsoft Access allow for pattern matching in queries. The asterisk (*) is commonly used to represent any number of characters. For example, to show all author names starting with 'T', you'd use the expression T* within a query, effectively filtering all entries where the name begins with 'T' .

To apply a percentage discount to a price field in a Microsoft Access query, you would use the IIF function or a calculated field with standard arithmetic operations. For example, applying a 15% discount if the price is 2000 or more can be done using: IIF([Price]>=2000;[Price]*15/100;FALSE). This applies the discount conditionally depending on the field's value .

Conditional logic in queries using the IIF function affects data retrieval and manipulation in Microsoft Access by allowing users to dynamically alter output based on specified criteria. This leads to more precise, tailored data outcomes as calculations or value selections are performed only under certain conditions set by the user, enhancing the decision-making capacity and flexibility of data operations .

Square brackets in Microsoft Access queries are used to indicate field names. This syntax ensures that the query recognizes the text inside the brackets as a field to fetch data from the corresponding column in the database, which is crucial for the query to execute correctly and retrieve the intended data .

In Microsoft Access, functions like LEFT and RIGHT are used to extract specific portions of a string. To concatenate these portions without a dedicated CONCATENATE function, you use the plus sign (+). For example, combining parts of text using extracted portions might involve LEFT([FieldName], n) + RIGHT([FieldName], m) to form a new string from the beginning and end of a text field .

The use of wildcard characters, such as the asterisk (*), enhances data filtering in Microsoft Access queries by allowing users to match patterns rather than exact values. This is especially useful for searching and retrieving rows with similar attributes, such as all entries that contain a certain suffix or prefix, enabling a more dynamic and flexible querying process .

The IIF function in Microsoft Access allows for conditional logic by evaluating a logical test and returning a different value depending on whether the test evaluates to TRUE or FALSE. The syntax requires an initial condition followed by the result if TRUE and result if FALSE, such as IIF([Price]>=2000;[Price]*15/100;FALSE). A limitation of the IIF function is that it can only be used in a new calculated field, meaning it cannot directly alter existing database fields .

Microsoft Access does not have a CONCATENATE function, which is commonly used in other software for joining strings. To concatenate strings in Access, the plus sign (+) is used as an alternative to merge text fields or strings together. This workaround allows users to join text fields without a dedicated concatenate function .

Aggregate functions in Microsoft Access are used when you need to perform calculations over a set of data and return a single summary value. These functions include SUM, COUNT, and others used in datasheet view. They are particularly useful for generating reports or summarizing data across rows of a dataset .