Can Someone Help Me Fix My Querry

[Aamer]

need help to fix the SQL 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

 

[Michel Walsh]

Hi,

It list some stats (SELECT ... ) for all records of [x1 Pur], matched
with any records in [x1 sle]

when the "match" is defined as [x1 Pur].[Purchase Description] = [x1
sle].[Transaction Description]


The GROUP BY clause would have been removed and the word DISTINCT added just
after the SELECT word.

The HAVING clause could have been replaced by a WHERE clause. Since it
implies [x1 sle] without allowing for NULLs, the LEFT OUTER JOIN could have
been replaced by an INNER JOIN.



SELECT DISTINCT ...long list of items or expressions to make available to
the recordset ...
FROM x1 Pur] INNER JOIN [x1 sle]
ON [x1 Pur].[Purchase Description] = [x1 sle].[Transaction
Description]
WHERE [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);




Hoping it may help,
Vanderghast, Access MVP

need help to fix the SQL 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