Query Not Working

[Moxioron]

Hey all.

I have a query that is fed by two other queries. SQL is at the bottom.

What I am trying to do is list the Paid Items Grouped next to the Returned Item Items Grouped. Although the individual queries work great, I get a count by Processed By. However, when I bring the queries together into one I get the following result. User allen4 did in fact have 131 paid items and returned 3...but why it jumps to 5, 10, 13, 16...and then repeats by user...I don't know. Any assistance you can provide would be appreciated. Thank you. <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock><v:shape id=_x0000_s1027 style="MARGIN-TOP: 233.55pt; Z-INDEX: 1; MARGIN-LEFT: 77.1pt; WIDTH: 230.3pt; POSITION: absolute; HEIGHT: 282.6pt; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75"><v:imagedata cropright="45466f" cropbottom="26952f" croptop="5734f" o:title="" src="file:///C:\DOCUME~1\carpean\LOCALS~1\Temp\msohtml1\01\clip_image001.png"></v:imagedata><?xml:namespace prefix = w ns = "urn:schemas-microsoft-comffice:word" /><w:wrap type="square"></w:wrap></v:shape><TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION></CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>ProcessedBy</TH><TH borderColor=#000000 bgColor=#c0c0c0>CountOfAmount</TH><TH borderColor=#000000 bgColor=#c0c0c0>CountOfTranType</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0>allent4</TD><TD borderColor=#c0c0c0 align=right>131</TD><TD borderColor=#c0c0c0 align=right>3</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>allent4</TD><TD borderColor=#c0c0c0 align=right>131</TD><TD borderColor=#c0c0c0 align=right>5</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>allent4</TD><TD borderColor=#c0c0c0 align=right>131</TD><TD borderColor=#c0c0c0 align=right>10</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>allent4</TD><TD borderColor=#c0c0c0 align=right>131</TD><TD borderColor=#c0c0c0 align=right>13</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>allent4</TD><TD borderColor=#c0c0c0 align=right>131</TD><TD borderColor=#c0c0c0 align=right>16</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>allent4</TD><TD borderColor=#c0c0c0 align=right>131</TD><TD borderColor=#c0c0c0 align=right>18</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>allent4</TD><TD borderColor=#c0c0c0 align=right>131</TD><TD borderColor=#c0c0c0 align=right>19</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>allent4</TD><TD borderColor=#c0c0c0 align=right>131</TD><TD borderColor=#c0c0c0 align=right>30</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>allent4</TD><TD borderColor=#c0c0c0 align=right>131</TD><TD borderColor=#c0c0c0 align=right>51</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>allent4</TD><TD borderColor=#c0c0c0 align=right>131</TD><TD borderColor=#c0c0c0 align=right>66</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>dd72psc</TD><TD borderColor=#c0c0c0 align=right>141</TD><TD borderColor=#c0c0c0 align=right>3</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>dd72psc</TD><TD borderColor=#c0c0c0 align=right>141</TD><TD borderColor=#c0c0c0 align=right>5</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>dd72psc</TD><TD borderColor=#c0c0c0 align=right>141</TD><TD borderColor=#c0c0c0 align=right>10</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>dd72psc</TD><TD borderColor=#c0c0c0 align=right>141</TD><TD borderColor=#c0c0c0 align=right>13</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>dd72psc</TD><TD borderColor=#c0c0c0 align=right>141</TD><TD borderColor=#c0c0c0 align=right>16</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>dd72psc</TD><TD borderColor=#c0c0c0 align=right>141</TD><TD borderColor=#c0c0c0 align=right>18</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>dd72psc</TD><TD borderColor=#c0c0c0 align=right>141</TD><TD borderColor=#c0c0c0 align=right>19</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>dd72psc</TD><TD borderColor=#c0c0c0 align=right>141</TD><TD borderColor=#c0c0c0 align=right>30</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>dd72psc</TD><TD borderColor=#c0c0c0 align=right>141</TD><TD borderColor=#c0c0c0 align=right>51</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>dd72psc</TD><TD borderColor=#c0c0c0 align=right>141</TD><TD borderColor=#c0c0c0 align=right>66</TD></TR></TBODY><TFOOT></TFOOT></TABLE>
<v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock><v:shape id=_x0000_s1026 style="MARGIN-TOP: 233.55pt; Z-INDEX: 1; MARGIN-LEFT: 77.1pt; WIDTH: 230.3pt; POSITION: absolute; HEIGHT: 282.6pt; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75"><v:imagedata cropright="45466f" cropbottom="26952f" croptop="5734f" o:title="" src="file:///C:\DOCUME~1\carpean\LOCALS~1\Temp\msohtml1\01\clip_image001.png"></v:imagedata><w:wrap type="square"></w:wrap></v:shape>
SELECT [Qry_Account Not Found - Paid Items Grouped].ProcessedBy, [Qry_Account Not Found - Paid Items Grouped].CountOfAmount, [Qry_Account Not Found - Returned Items Grouped].CountOfTranType
FROM [Qry_Account Not Found - Paid Items Grouped], [Qry_Account Not Found - Returned Items Grouped]
GROUP BY [Qry_Account Not Found - Paid Items Grouped].ProcessedBy, [Qry_Account Not Found - Paid Items Grouped].CountOfAmount, [Qry_Account Not Found - Returned Items Grouped].CountOfTranType;

 

[Richard Schollar]

Hi

You have a cross-join so you just need to restrict the join by linking common fields from one table to the other (this is missing at the moment). Assuming you have a ProcessedBy field in both tables you can probably do this with:

SELECT [Qry_Account Not Found - Paid Items Grouped].ProcessedBy, [Qry_Account Not Found - Paid Items Grouped].CountOfAmount, [Qry_Account Not Found - Returned Items Grouped].CountOfTranType
FROM [Qry_Account Not Found - Paid Items Grouped] Left Join [Qry_Account Not Found - Returned Items Grouped] On [Qry_Account Not Found - Paid Items Grouped].ProcessedBy = [Qry_Account Not Found - Returned Items Grouped].ProcessedBy 
GROUP BY [Qry_Account Not Found - Paid Items Grouped].ProcessedBy, [Qry_Account Not Found - Paid Items Grouped].CountOfAmount, [Qry_Account Not Found - Returned Items Grouped].CountOfTranType;

Relevant bits are in red.

 

[Moxioron]

Perfect...thank you.

What happens though if I have an operator who has paid everything and returned nothing? They wouldn't be in the query that counts the returned items.

 

[Richard Schollar]

Because I used a Left Join (with the Paid table on the Left) it will include all records that have been paid but have no returns.

 

[Moxioron]

Got it...thank you for your prompt responses. I really appreciate it.