Question

Create a copy of Total Balance By Client query. Name the query Total Bslances By State. Open the query in design view and remove the client name the query. Add grouping by the clients state

Answer 1

ANSWER:-

Stepl: . Start Access. Open the downloaded Access file named exploring a03 Grader al.accdb All Access Objects Step2: . Go to Create tab on the Ribbon » Select the option Query Design from the Queries group Home Create External Data Database Tools Tell me what you want to do Form Wizard -Navigation- Design Form More Forms Application Table Table SharePointueryQury Form Form Blank Rep Design Lists Wizard Design parts ▼ Templates Tables Forms All Access Obie ㄍ Search. Tables » It will display a Show Table window . Add the Accounts, Agent, and Clients tables to the query design window just by clicking Add button.

Show Table Tables Queries Both Accounts Agent Clients Add Close Close the Show Table dialog box . Add (Double click) the FirstName field from the Clients table . Add (Double click) the LastName field from the Clients table. . Add (Double click) the Savings Balance field from the Accounts table. » Add (Double click) the OpenDate field from the Accounts table. Select the option descending from the Sort in the savings balance.

File Home Create External Data Database Tools Tell me what you want to do 画! コ+!/! 鹵トDeles RovsThelete Column s CD Union k Σ 扇 짠Property Sheet X Delete Rows Delete Columns Return: A Through Table View Run Make Append Update Crosstab Delete Table Data Definition Table Builder Query Type Query Setup All Access ObjectsQ Tables Accounts Agent Clients Accounts Agent Chents 2 AccountiD Agent D ClientD Client Agentio Savingi Balance Street SysRowld Cny Savingi BalanceOpenDae Step3: . Go to the last empty column and add the below expression Account-Time: #12/31/2017#-[OpenDate] * In the Property Sheet, set the Format option to General Number format . Save the query as Account Longevity, and close the query

Step4: . Go to Create tab on the Ribbon » Select the option Query Design from the Queries group File HomeaE External Data Database Tools Tell me what you want to do Create Form Wizard NavigationRe Application Table Table Shaet Query Query Form Form Blank Design ListsWizard Design Design Form More Forms Parts Templates Tables Forms All Access Obje Search. Tables » It will display a Show Table window . Add the Accounts, Agent, and Clients tables to the query design window just by clicking Add button.

Show Table Tables Queries Both Agent Clients Add Close . Close the Show Table dialog box . Add (Double click) the FirstName field from the Clients table. Add (Double click) the LastName field from the Clients table. . Add (Double click) the Savings Balance field from the Accounts table. Step5: Go to Design tab on the Ribbon. . Click on the "Totals" icon on the "Show/Hide" group . It will add the appropriate grouping. lh D e r dilia first

File Home Create Exernal Data Database Toos ? Tell me what you want to do 벨 Insert Columns (« Insert Rows 3X Delete Rows Builder - Table Names Pass-Through t Data Definition Delete Columns View Run Make Append Update Crosstab Delete Table tak arameters Table Return: All Query Type Query Setup All Access Objects Queryi Tables Accounts Agent Chents AccountD Chent Agent AgentlD ClientID Savings Balance tartDate Account Longevity Street City field FirstName Savings Balance fotal Sort Show Step6: » Select count option from the Total of the last name » Select the average option from the Total of savings balance

» Select and copy the "Total Balances By Client" query in the Navigation pane File Home Create External Data Database T % Cut a Copy Ascending Al Descending View Paste Filter /Remove Format Painter Sort & Filte Views Clipboard All Access Objects Tables Accounts Ш Agent Clients Queies Account Longevity Total Balances By Client Open Design View Export Rename Hide in this Group Delete B Copy Paste E Object Properties » Right click on the Navigation Pane and select the option "Paste". » It will opens a window "Paste As" and type the query name as "Total Balances By State".

Home Create External Data Database Tools Teil me what you want to do Union ร์" Insert Rows utu Insert Columns Pass-Through Data Definition , Delete Rows Delete Columns View Run Make Append Update Crosstab Delete Table otals Parameters Table Builder Return: All Query Type Query Setup All Access Objects Query Tables Agent Clients Accounts Agent Clients AccountiD จู AgentlD ClientlD Client Savings Balance Account Longevity City Field: FirstName Table: Clients Total: Group By Savings Balance Group By Show Count » Save the query as Total Balances By Client, and close the query Save As Query Name: Total Balances By Client OK Step7:

» Select and copy the "Total Balances By Client" query in the Navigation pane File Home Create External Data Database T % Cut a Copy Ascending Al Descending View Paste Filter /Remove Format Painter Sort & Filte Views Clipboard All Access Objects Tables Accounts Ш Agent Clients Queies Account Longevity Total Balances By Client Open Design View Export Rename Hide in this Group Delete B Copy Paste E Object Properties » Right click on the Navigation Pane and select the option "Paste". » It will opens a window "Paste As" and type the query name as "Total Balances By State".

2 Paste As Query Name: Total Balances By State OK Cancel » Select and open the query in Design view Views Clipboard Sort All Access Objects Tables Accounts Agent Clients Queies Account Longevity Total Balances By Client oaOpen Design View Rename Hide in this Group Delete % Cut E Copy G Paste Object Properties It will open in the design view mode and remove the client first name and the last name from the query » Add (Double click) the State field from the Clients table » Add grouping by the client's state.

Design Make Append Updace Crostab Delete AllAccess Objects 8-13x"ta. enhsu Agent Quernies OpenDete