Help needed fixing SQL querry

[Aamer]

need help to fix the querry.

this querry shows perticular inventory of a specified month and year.

the result it shows is also correct. but the problem is it subtacts

purchases made in the specified month (example: July) from Sales Made

in July.

While what i need is it should do the following:
sales made in specified month - sales made in the same specified month

and also should give me the balace uptodate with the previous month.
which i can use as carry forward to get the actual inventory balance in

that specified month.

can someone please fix the following SQL querry for me.


SELECT [x1 Pur].Catagory, [x1 sle].Catagory, [x1 Pur].ComapnyName, [x1

Pur].[Purchase Description], [x1 Pur].[Purchase Price], [x1

Pur].[Purchase Qty], [x1 Pur].[Purchase Units], ([Purchase

Price]*[Purchase Qty]) AS [Total Purchase], [x1 sle].ComapnyName, [x1

sle].[Transaction Description], [x1 sle].Price, [x1 sle].Odered, [x1

sle].Units, ([Price]*[Odered]) AS [Total Sales], [Total Sales]-[Total

Purchase] AS [Sub Total], [x1 Pur].PurDate, [x1 sle].TransactionDate,

[x1 Pur].PurTransId, [x1 sle].TransId
FROM [x1 Pur] LEFT JOIN [x1 sle] ON [x1 Pur].[Purchase Description] =

[x1 sle].[Transaction Description]
GROUP BY [x1 Pur].Catagory, [x1 sle].Catagory, [x1 Pur].ComapnyName,

[x1 Pur].[Purchase Description], [x1 Pur].[Purchase Price], [x1

Pur].[Purchase Qty], [x1 Pur].[Purchase Units], [x1 sle].ComapnyName,

[x1 sle].[Transaction Description], [x1 sle].Price, [x1 sle].Odered,

[x1 sle].Units, [x1 Pur].PurDate, [x1 sle].TransactionDate, [x1

Pur].PurTransId, [x1 sle].TransId
HAVING ((([x1 Pur].PurDate) Between DateSerial([Enter Year:],[Enter

Month:],1) And DateSerial([Enter Year:],[Enter Month:]+1,0)) AND (([x1

sle].TransactionDate) Between DateSerial([Enter Year:],[Enter

Month:],1) And DateSerial([Enter Year:],[Enter Month:]+1,0)));






Thanks in advance

aamer sheikh