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.