Use the skills you learned to work in a database for a mall information desk. Da

Question

Use the skills you learned to work in a database for a mall information desk. Data Files needed for this Case Problem: Ashbrook.accdb Ashbrook Mall Information Desk The Mall Operations Office is responsible for everything that happens in the Ashbrook Mall. To maintain a catalog of job openings at the mall stores and to track maintenance work done at the mall, Sam Bullard, director of the Mall Operations Office, has created the Ashbrook database. You'll help Sam create several new queries and make design changes to the tables. Complete the following steps: 1. Open the Ashbrook database, and then click the Enable Content button next to the Security Warning, if necessary Modify the first record in the tblStore table datasheet by changing the Store Contact to your Close the table. 3. Create a query to find all records in the tblStore table in which the Location field value starts ith the letter B. Display all fields in the query recordset, and sort in ascending order by StorelD. Save the query as qryBLocations, run the query, and then close it. Create a query to find all records in the tblPosition table in which the PositionDesc field value is Clerk, Salesclerk, or Stock Clerk. Use a list-of-values match for the selection criteria. Display all fields in the query recordset, and sort in ascending order by PositionID. Save the query as qryClerkPositions, run the query, and then close it. Make a copy of the qryClerkPositions using the new name qry NonClerkPositions. Modify the new query to find all records in which in the tblPosition table in which the PositionDesc field values are not Clerk, Salesclerk, or Stock Clerk. Save and run the query, and then close it. Create a parameter query to select the tblPosition table records for a PositionDesc field value that the user specifies. If the user doesn't enter a PositionDesc field value, select all records from the table. Display all fields from the table in the query recordset, sorting in ascending order by PositionID. Save the query as qryPosition Parameter. Run the query and enter no value as the PositionDesc field value, and then run the query again and enter Clerk as the PositionDesc field value. Close the query Create a find duplicates query based on the tblMaintenanceJob table. Select JobLocation as the field that might contain duplicates, and select the JobReportedDate, JobStatus, JobCompletedDate, and JobDesc fields as additional fields in the query recordset. Save the query as qryDuplicateJobLocations, run the query, and then close it. 4. 5. 6. 7. 8. Create a find unmatched query that finds all records in the tblStore table for which there is no matching record in the tblPosition table. Display all fields from the tblStore table in the recordset. Save the query as qryStores WithoutMatchingPositions, run the query, and then close it. Create a crosstab query based on the qryStorePositions query. Use the PositionDesc field values for the row headings, the Location field values for the column headings, the count of the LocationName field values as the summarized value, and include row sums. Save the qryLocationByPositionCrosstab. Change the column heading for the sum column to Total Number of Positions. Resize the columns in the query recordset to their best fit, and then sa and close the query 9. query

Explanation / Answer

You can create all queries using Access Query Wizards.

3. qryBLocations
select * from tblStore where Location Like 'B*' order by StoreID

4.qryClerkPositions
select * from tblPosition where PositionDesc In ('Clerk','Salesclerk','Stock Clerk') reder by PositionID

5.qryNonClerkPositions
select * from tblPosition where PositionDesc Not In ('Clerk','Salesclerk','Stock Clerk') order by PositionID

6.qryPositionParameter
The string [Enter Position Description] is prompt for parameter. Here the square brackets indicates that query should ask for parameter value and the string in the bracket is prompt value for displays

select * from tblPosition where PositionDesc = [Enter Position Description] Or Like [Enter Position Description] Is NULL order by PositionID

7.qryDuplicateJobLocations
SELECT JobLocation, JobReportedDate,JobStatus,JobCompletedDate, JobDesc FROM tblMaintenanceJob
WHERE JobLocation IN
(SELECT JobLocation, Count(JobLocation)
FROM tblMaintenanceJob
GROUP BY JobLocation
HAVING COUNT(JobLocation)>1)

You can also use Duplicates Query Wizard for this.

8.qryStoresWithoutMatchingPositions
select * from tblStore
where PositionID not in
(select PositionID from tblPosition)

9.qryLocationByPositionCrosstab

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.