434,921 Members | 1,493 Online
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