9713_P2_JUNE_2010

Creating Relational Database with following tables:

JXCARS.TXT and JXCUST.CSV

Mention appropriate Datatypes.Purchase Price & Sale Price with currency symbol £.

Validation Rules & Text:

Purchase Price: >=500  ; More than or equal to £500

Sale Price: >=1000; More than or equal to £1000

Address three: “Essex” Or “Suffolk” Or “Cambridgeshire” ; Only Essex/Suffolk/Cambridgeshire

Year : >=1991; cars made after 1991

Input Mask:

customer ID: L0000

telephone number:

00000\000000

Relationship: JXCust>CustomerID——–JXCars>CustID [One to Many]

Report: Cars sold to business customers

New Field in the query: Profit: [SalePrice]-[PurchasePrice]

Criteria in Sold field : “”Y”

Criteria in Company: Like “*”

Report showing the average sale price of car where the average price is more than £15,000 .

METHOD 1:

CLICK QUERY DESIGN> ADD TABLES> SELECT Make & SalePrice FIELDS> CLICK ON GROUP BY > GROUP SalePrice ON AVG

Create Report Using the Query.

Graph Using Same Report: Export Report into Excel and Produce the Chart, with appropriate formatting.

METHOD 2: [ To know more additional features]

Create Cross Tab Query: Select MAKE for ROW HEADING, SOLD for COLUMN HEADING, SALE PRICE for Value AVERAGE.

Number filter on Total of SalePrice Field—-Greater that 15000 and

Under Property Sheet : Select Yes for–Filter on Load..

From Home Tab-Select Advanced-Select Advanced Filter/Sort—–Click Save- it will allow you to save the query with new name—Save it.

Select Report Wizard: Select Field MAKE & TOTAL OF SALEPRICE from the new query, to display the data.

Under Design view Change the Name of field Total to SalePrice to Avg Saleprice.

Graph under Same Report

Select Graph option from the design view—-create the graph—selecting fields Make & Total Sale Price From the new Query.

Right Click on Graph using deign view—-Go to Chart Object—-Click open—-Remove unwanted columns and provide Desired Name.

Report comparing the average profit for the sale of vehicles to both personal and business customers

Select Query Design> Select the tables> Select CustomerID Field> Create Profit Field using Formula : Profit: [SalePrice]-[PurchasePrice]

Group Profit Field on AVG.

Replace CustomerID field name with this formula, in design view (top row) Customer: Left([CustomerID],1)

Run the query.

Create Report Using the Query.

TASK : To prepare a spreadsheet, Which will be used to model the costs depending upon the supplier chosen.

9713_p2_june 2010