Menu
Forums
New posts
Search forums
Members
Current visitors
Log in
Register
What's new
Search
Search
Search titles only
By:
New posts
Search forums
Menu
Log in
Register
Install the app
Install
Forums
Archive
Newsgroup Archive
Access Newsgroups
Access Queries
Counting Orders in Sub-Select Query?
JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
Reply to thread
Message
<blockquote data-quote="tina" data-source="post: 2225291"><p>well, i couldn't figure out how to do it with a subquery. i also couldn't</p><p>tell which field in your SQL contains the values that identify specific</p><p>orders. so i arbitrarily decided to use the Cono field, and created the</p><p>following solution with two queries, as</p><p></p><p>qryCountOrders</p><p>SELECT db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice.Cono, Count(db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice.ID) AS</p><p>OrderCount</p><p>FROM db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice</p><p>WHERE (((db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice.InvoiceDate) Between #8/1/2004# And</p><p>#7/31/2005#))</p><p>GROUP BY db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice.Cono;</p><p></p><p>and a modification of your original SQL, as</p><p></p><p>SELECT db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice.Cono, db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice.CustID,</p><p>Format([InvoiceDate],"yyyy/mm") AS [Year-Month],</p><p>db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice.VendID, Sum(db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice.Sales) AS</p><p>Sales_Total, Sum([Sales]-[Cost]) AS GM_Total,</p><p>Count(db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice.LineNum) AS Line_Count,</p><p>qryCountOrders.OrderCount</p><p>FROM db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice LEFT JOIN qryCountOrders ON</p><p>db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice.Cono = qryCountOrders.Cono</p><p>WHERE (((db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice.InvoiceDate) Between #8/1/2004# And</p><p>#7/31/2005#))</p><p>GROUP BY db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice.Cono, db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice.CustID,</p><p>Format([InvoiceDate],"yyyy/mm"), db<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite12" alt="o_O" title="Er... what? o_O" loading="lazy" data-shortname="o_O" />rder_Line_Invoice.VendID,</p><p>qryCountOrders.OrderCount;</p><p></p><p>note the minor simplifications of the criteria on the InvoiceDate field, and</p><p>on the formatting of the InvoiceDate field.</p><p></p><p>hth</p></blockquote><p></p>
[QUOTE="tina, post: 2225291"] well, i couldn't figure out how to do it with a subquery. i also couldn't tell which field in your SQL contains the values that identify specific orders. so i arbitrarily decided to use the Cono field, and created the following solution with two queries, as qryCountOrders SELECT dbo_Order_Line_Invoice.Cono, Count(dbo_Order_Line_Invoice.ID) AS OrderCount FROM dbo_Order_Line_Invoice WHERE (((dbo_Order_Line_Invoice.InvoiceDate) Between #8/1/2004# And #7/31/2005#)) GROUP BY dbo_Order_Line_Invoice.Cono; and a modification of your original SQL, as SELECT dbo_Order_Line_Invoice.Cono, dbo_Order_Line_Invoice.CustID, Format([InvoiceDate],"yyyy/mm") AS [Year-Month], dbo_Order_Line_Invoice.VendID, Sum(dbo_Order_Line_Invoice.Sales) AS Sales_Total, Sum([Sales]-[Cost]) AS GM_Total, Count(dbo_Order_Line_Invoice.LineNum) AS Line_Count, qryCountOrders.OrderCount FROM dbo_Order_Line_Invoice LEFT JOIN qryCountOrders ON dbo_Order_Line_Invoice.Cono = qryCountOrders.Cono WHERE (((dbo_Order_Line_Invoice.InvoiceDate) Between #8/1/2004# And #7/31/2005#)) GROUP BY dbo_Order_Line_Invoice.Cono, dbo_Order_Line_Invoice.CustID, Format([InvoiceDate],"yyyy/mm"), dbo_Order_Line_Invoice.VendID, qryCountOrders.OrderCount; note the minor simplifications of the criteria on the InvoiceDate field, and on the formatting of the InvoiceDate field. hth [/QUOTE]
Verification
Post reply
Forums
Archive
Newsgroup Archive
Access Newsgroups
Access Queries
Counting Orders in Sub-Select Query?
Top
