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="Dan" data-source="post: 2225266"><p>Hi,</p><p></p><p>I am summarizing data like this</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>Year([Invoicedate]) & '/' & Format(Month([Invoicedate]),'00') AS</p><p>[Year-Month], 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>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 Sales_Total, Sum([Sales]-[Cost]) AS</p><p>GM_Total, 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>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)>=#8/1/2004# And</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.InvoiceDate)<=#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>Year([Invoicedate]) & '/' & Format(Month([Invoicedate]),'00'),</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;</p><p></p><p>But I also want to count the number of orders. I am thinking I could</p><p>use a sub query to select just the orders for the above realtions, group</p><p>on ordernumber then Count(*) but I don't know how to actually do this.</p><p>Can someone help me?</p><p></p><p>TIA,</p><p>Dan</p></blockquote><p></p>
[QUOTE="Dan, post: 2225266"] Hi, I am summarizing data like this SELECT dbo_Order_Line_Invoice.Cono, dbo_Order_Line_Invoice.CustId, Year([Invoicedate]) & '/' & Format(Month([Invoicedate]),'00') 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 FROM dbo_Order_Line_Invoice WHERE (((dbo_Order_Line_Invoice.InvoiceDate)>=#8/1/2004# And (dbo_Order_Line_Invoice.InvoiceDate)<=#7/31/2005#)) GROUP BY dbo_Order_Line_Invoice.Cono, dbo_Order_Line_Invoice.CustId, Year([Invoicedate]) & '/' & Format(Month([Invoicedate]),'00'), dbo_Order_Line_Invoice.VendId; But I also want to count the number of orders. I am thinking I could use a sub query to select just the orders for the above realtions, group on ordernumber then Count(*) but I don't know how to actually do this. Can someone help me? TIA, Dan [/QUOTE]
Verification
Post reply
Forums
Archive
Newsgroup Archive
Access Newsgroups
Access Queries
Counting Orders in Sub-Select Query?
Top
