Left Join

[Shelby]

I have a query that gives me the total count of adjustment
types (tblAdjustments.AdjType) and total amount of
adjustment amount for each type (tblAdjustments.AdjAmt)
per a date range and where the source
(tblAdjustments.Source) is equal to 1.

I want to display all adjustment types even those that
have a zero value because there were no adjustments made
of that type within the date range.

I have created a left join from my table that lists all
adjustment types (ddAdjustmentTypes) but because of the
criteria source = 1, and the date range, I am not getting
the adjustments that have a zero value.

HELP please. I do not have any clue of what else to do at
this point. The SQL code for the query is below, if that
helps any.

SELECT ddtblAdjustmentType.AdjustType, Count
(tblAdjustments.AdjID) AS [Count], Sum
(tblAdjustments.AdjAmt) AS [total Amt]
FROM ddtblAdjustmentType LEFT JOIN tblAdjustments ON
ddtblAdjustmentType.AdjustTypeID = tblAdjustments.AdjType
WHERE (((ddtblAdjustmentType.AdjustTypeID)=2 Or
(ddtblAdjustmentType.AdjustTypeID)=3 Or
(ddtblAdjustmentType.AdjustTypeID)=4 Or
(ddtblAdjustmentType.AdjustTypeID)=7 Or
(ddtblAdjustmentType.AdjustTypeID)=8 Or
(ddtblAdjustmentType.AdjustTypeID)=12) AND
((tblAdjustments.AdjDate) Between [Enter Begin Date] And
[Enter End Date]) AND ((tblAdjustments.Source)=1))
GROUP BY ddtblAdjustmentType.AdjustType;

thanks, in advance

Shelby

 

[Brian]

I have a query that gives me the total count of adjustment
types (tblAdjustments.AdjType) and total amount of
adjustment amount for each type (tblAdjustments.AdjAmt)
per a date range and where the source
(tblAdjustments.Source) is equal to 1.

I want to display all adjustment types even those that
have a zero value because there were no adjustments made
of that type within the date range.

I have created a left join from my table that lists all
adjustment types (ddAdjustmentTypes) but because of the
criteria source = 1, and the date range, I am not getting
the adjustments that have a zero value.

HELP please. I do not have any clue of what else to do at
this point. The SQL code for the query is below, if that
helps any.

SELECT ddtblAdjustmentType.AdjustType, Count
(tblAdjustments.AdjID) AS [Count], Sum
(tblAdjustments.AdjAmt) AS [total Amt]
FROM ddtblAdjustmentType LEFT JOIN tblAdjustments ON
ddtblAdjustmentType.AdjustTypeID = tblAdjustments.AdjType
WHERE (((ddtblAdjustmentType.AdjustTypeID)=2 Or
(ddtblAdjustmentType.AdjustTypeID)=3 Or
(ddtblAdjustmentType.AdjustTypeID)=4 Or
(ddtblAdjustmentType.AdjustTypeID)=7 Or
(ddtblAdjustmentType.AdjustTypeID)=8 Or
(ddtblAdjustmentType.AdjustTypeID)=12) AND
((tblAdjustments.AdjDate) Between [Enter Begin Date] And
[Enter End Date]) AND ((tblAdjustments.Source)=1))
GROUP BY ddtblAdjustmentType.AdjustType;

thanks, in advance

Shelby

Shelby,

Something along these lines ought to do the trick (warning: untried!):

SELECT ddtblAdjustmentType.AdjustType, Count
(tblAdjustments.AdjID) AS [Count], Sum
(tblAdjustments.AdjAmt) AS [total Amt]
FROM ddtblAdjustmentType LEFT JOIN tblAdjustments ON
(ddtblAdjustmentType.AdjustTypeID = tblAdjustments.AdjType
AND ((tblAdjustments.AdjDate) Between [Enter Begin Date] And
[Enter End Date]) AND ((tblAdjustments.Source)=1)))
WHERE (((ddtblAdjustmentType.AdjustTypeID) IN (2,3,4,7,8,12)
GROUP BY ddtblAdjustmentType.AdjustType

 

[Shelby]

Thank you so much Brian!

FYI: There were a few too many parenthesis, so here's the
code just in case you or someone else runs into this.

SELECT ddtblAdjustmentType.AdjustType, Count
(tblAdjustments.AdjID) AS [Count], Sum
(tblAdjustments.AdjAmt) AS [total Amt] FROM
ddtblAdjustmentType LEFT JOIN tblAdjustments ON
(ddtblAdjustmentType.AdjustTypeID = tblAdjustments.AdjType
AND ((tblAdjustments.AdjDate) Between [Enter Begin Date]
And [Enter End Date]) AND (tblAdjustments.Source)=1) WHERE
((ddtblAdjustmentType.AdjustTypeID) IN (2,3,4,7,8,12))
GROUP BY ddtblAdjustmentType.AdjustType

-----Original Message-----

I have a query that gives me the total count of adjustment
types (tblAdjustments.AdjType) and total amount of
adjustment amount for each type (tblAdjustments.AdjAmt)
per a date range and where the source
(tblAdjustments.Source) is equal to 1.

I want to display all adjustment types even those that
have a zero value because there were no adjustments made
of that type within the date range.

I have created a left join from my table that lists all
adjustment types (ddAdjustmentTypes) but because of the
criteria source = 1, and the date range, I am not getting
the adjustments that have a zero value.

HELP please. I do not have any clue of what else to do at
this point. The SQL code for the query is below, if that
helps any.

SELECT ddtblAdjustmentType.AdjustType, Count
(tblAdjustments.AdjID) AS [Count], Sum
(tblAdjustments.AdjAmt) AS [total Amt]
FROM ddtblAdjustmentType LEFT JOIN tblAdjustments ON
ddtblAdjustmentType.AdjustTypeID = tblAdjustments.AdjType
WHERE (((ddtblAdjustmentType.AdjustTypeID)=2 Or
(ddtblAdjustmentType.AdjustTypeID)=3 Or
(ddtblAdjustmentType.AdjustTypeID)=4 Or
(ddtblAdjustmentType.AdjustTypeID)=7 Or
(ddtblAdjustmentType.AdjustTypeID)=8 Or
(ddtblAdjustmentType.AdjustTypeID)=12) AND
((tblAdjustments.AdjDate) Between [Enter Begin Date] And
[Enter End Date]) AND ((tblAdjustments.Source)=1))
GROUP BY ddtblAdjustmentType.AdjustType;

thanks, in advance

Shelby

Shelby,

Something along these lines ought to do the trick (warning: untried!):

SELECT ddtblAdjustmentType.AdjustType, Count
(tblAdjustments.AdjID) AS [Count], Sum
(tblAdjustments.AdjAmt) AS [total Amt]
FROM ddtblAdjustmentType LEFT JOIN tblAdjustments ON
(ddtblAdjustmentType.AdjustTypeID = tblAdjustments.AdjType
AND ((tblAdjustments.AdjDate) Between [Enter Begin Date] And
[Enter End Date]) AND ((tblAdjustments.Source)=1)))
WHERE (((ddtblAdjustmentType.AdjustTypeID) IN (2,3,4,7,8,12)
GROUP BY ddtblAdjustmentType.AdjustType


.