By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,493 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

some query problems

P: 5
I have posted once but no responce


I have tried
SELECT [CarSale].[SaleDate] AS [SaleDate By Month], CarSale.SalespersonNo, Sum(CarSale.CarPrice) AS [Sum Of CarPrice]
FROM CarSale
WHERE SaleDate >= 01/12/2006
GROUP BY [CarSale].[SaleDate], CarSale.SalespersonNo, [CarSale].[SaleDate], [CarSale].[SaleDate];

I need the following

commission

Car Price() % Commission
0-5000 2
5000-10000 3
10000-15000 4
15000+ 5


total cost of the cars sold by each salesman in the past month (For example: S1 has sold R123 JKN 6,500
X567 JMK 10,100
therefore the output would be S1 16,600)

add the commission earned by each salesman to the above report

outputs both the most expensive and least expensive car sold in the past month

and finally

all car sales in the past month. Include salesperson number, salesperson name, customer number, customer name and customer address in your output. Order by salesperson number.

these are my tables

Customer
Create Table Customer(
CustomerNo COUNTER Primary Key NOT NULL,
FirstName VarChar(50) not null,
LastName VarChar(50) not null,
Address VarChar(30),
Town VarChar(30),
Postcode VarChar(9),
Telephone VarChar(12)
)

Centre
Create Table Centre(
CentreNo COUNTER Primary Key NOT NULL,
Name varchar(40),
Address VarChar(30),
PostCode VarChar(30),
Telephone VarChar (20)
)

Sales Staff
Create Table SalesStaff(
SalespersonNo COUNTER Primary Key NOT NULL,
SalespersonName VarChar(40) not null,
CentreNo int,
Foreign Key (CentreNo) REFERENCES Centre(CentreNo)
)


Car Sale
Create Table CarSale(
CarReg VarChar(10) Primary Key NOT NULL,
CarPrice VarChar(10),
CarModel VarChar(20),
CarMake VarChar(10),
SalespersonNo int,
CustomerNo int,
SaleDate Date,
Foreign Key (SalespersonNo) REFERENCES SalesStaff (SalespersonNo),
Foreign Key (CustomerNo) REFERENCES Customer(CustomerNo)
)

thanks in advance
Dec 12 '06 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,492
What exactly are you after?
A number of different queries with the data as laid out above returned?
Or one big complicated query with all the above data shown?
The data we're working with is well laid out which makes a pleasant change, but the request is not very clear I'm afraid.
I can certainly work with this but need clarification on what you want.
Dec 12 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.