|Total of questions: 231|
|Total of questions: 231|
|First steps (10 questions)|
|Basic Functions (11 questions)|
|Indexes (2 questions)|
|Table Design (30 questions)|
|Relationships (17 questions)|
|Forms (33 questions)|
|Queries 1 (29 questions)|
|Queries 2 (28 questions)|
|Reports (19 questions)|
|DIPLOMA level questions (7 questions)|
|MS Access Expert 2016, 2013, 2010, 2007, 2003 (BENCHMARK) (30 questions)|
|MS Access Expert 2016, 2013, 2010, 2007, 2003 (ASSESSMENT) (15 questions)|
Insert the appropriate criteria in the current query so that it returns only the customers whose PC begins with 54. Save and close the query.
Modify the current query so that it displays only the FirstName and PC fields of the customers whose PC begins with 54. Save and close the query.
Create a new query that will use the Description and Price fields from the Products table and the FileRoot field of the ProductsPhoto table. Apply a criterion to the FileRoot field to retrieve all records which contain a photo. Make sure that when the query is executed the Description and Price fields only (following this order) are displayed. Save the query as q1 and close it.
Create a new query named sumquantity to retrieve the sum of the quantity field of the Orders table. Use sum1 as name for the calculated field.
Create a new query named all_products to retrieve the total number of records of the Products table. Use total as name for the calculated field.
Create a new query named myprice that will display the average of the product prices displayed in the Products table. Use avgprice as name for the calculated field.
Modify the NoPhoto query to retrieve only the product records that do not contain a photo. Save the query.
Modify the NoPhoto query to display only the product records that contain a photo. Save the query.
Modify the r_orders query to retrieve only the first 30 records. Save the query.
Modify the GreaterThan query to prompt the user to enter a number displaying the message Enter minimum value when it is executed. Make sure the result contains only products where the Price field value is greater than or equal to the value that the user has previously entered. Save the query.
Modify the PhotosJPG query to exclude the jpg photos. Save the query.
Create a new query named insert2 to add a new record in the Products table with the following data (use command VALUES):
Code=1, Description=myproduct, Price=100
Run the query.
Open the FinalPrice query and insert a calculated field named VatValue that will multiply the Price field value by the VAT field value. Save and close the query.
Open the PhotoSize query and rename the TotalPixels field to Pixels. Save and close the query.
Delete the Customers table from the r_orders query. Save and close the query.
Create a query to find duplicates using all fields from the Products table and retrieve records which display duplicates in the Price field. Save the query as Duplicates.
Use the query wizard to create a crosstab query based on the SalesPerProduct query. The new query will display the ProductID and Description fields as row headings and the myYear field as column heading. Also make sure the query calculates the sum of the values displayed in the myTotal field. Save the query as NewSales and close it.
Open the myPhotos query and apply the appropriate settings to include all records from the Products table. Save the query.
Create a new query named p1 that will display the fields Code and Description (in the above order) from the Products table. The query will retrieve only the records the Description field of which begins with a or b or c and ends with any number.
Create a new query named p2 that will display all fields of the Products table but only those records where the VAT field has no value and the Price field has any value.
Create a new totals query named p3 which will display the variety of product prices in descending order.
In the EndDates query insert one more column named EndDate that will display the value of the DateOfOrder field plus 30 days. Save the query.
Make sure the TotalValue field of the r_orders query is displayed in Euro format. Save the changes.
Modify the r_orders3 query to display only the first 4 records. Save the changes.
Modify the r_orders3 query to display only 10% of the records. Save the changes.
Modify the PhotosJPG query to retrieve only the records which display a file path which begins with C:\
Save the result of the query as PhotosJPGonC to a new table.
Insert a new field named difference in the r_orders query, that will calculate the total number of days between the date of order and today. Save the changes.
Insert a new field named Total in the ClientsSalesTotals query that will sum up the sales values (quantities by prices) for every client. Also, apply the appropriate relationship between the two tables. Save the changes.
Modify the ExpensiveProductsQuery query to retrieve only sold products of a price higher than or equal to 500. Save the result of the query to a new table named ExpensiveProducts. Save and run the query (use the ItemCode).