|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#11
December 2nd, 2009, 05:53 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Sum Only Subset Records Included in Max Sale Date
Wow -- Nice formula! Could you help me understand it a bit more. What does
the [XX] do? I get a syntax error on the Sum(iif formula at the AND point? I would have thought you would sumiif on the SALE_DATE not REPORT_DATE -- please explain. Thanks "KARL DEWEY" wrote: Try this -- SELECT TaxCredit.COMPANY_ID AS Co, TaxCredit.REPORT_DATE AS PED, (SELECT TOP 1 [XX].SALE_DATE FROM TaxCredit AS [XX] WHERE [XX].SALE_DATE = TaxCredit.REPORT_DATE ORDER BY [XX].SALE_DATE DESC) AS Sdate, Sum(IIF(TaxCredit.REPORT_DATE Between AND DateAdd("d", -60,TaxCredit.REPORT_DATE), TaxCredit.TaxCredit, 0)) AS TaxCredit FROM TaxCredit GROUP BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE ORDER BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE; -- Build a little, test a little. "dallin" wrote: I see your point; however, I need to subtotal by period end date any sale date record that is 60 days less than the period end date: example Co PED Sale date TaxCredit 1 12/31/08 10/31/08 15.00 1 12/31/08 10/31/08 12.00 1 12/31/08 09/30/08 5.00 9 01/31/09 11/30/08 10.15 9 01/31/09 11/30/08 21.15 9 01/31/09 09/30/08 10.02 Result should look like: Co PED SaleDate TaxCredit 1 12/31/08 10/31/08 27.00 9 01/31/09 11/30/08 31.30 "KARL DEWEY" wrote: You can not have details (PED) and total in same query so change like this -- SELECT TaxCredit.COMPANY_ID AS Co, Max(TaxCredit.SALE_DATE) AS Sdate, Sum(TaxCredit.TaxCredit) AS TaxCredit FROM TaxCredit GROUP BY TaxCredit.COMPANY_ID ORDER BY TaxCredit.COMPANY_ID; -- Build a little, test a little. "dallin" wrote: I have the following query: The sum(taxcredit) is not working -- it is summing all records by PED (period end date) where as I only want it to sum the records associated with the sale date that equals Max(Sale_Date). How can I do this? SELECT TaxCredit.COMPANY_ID AS Co, TaxCredit.REPORT_DATE AS PED, Max(TaxCredit.SALE_DATE) AS Sdate, Sum(TaxCredit.TaxCredit) AS TaxCredit FROM TaxCredit GROUP BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE ORDER BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE; 
|
|
#12
December 2nd, 2009, 05:53 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Sum Only Subset Records Included in Max Sale Date
Wow -- Nice formula! Could you help me understand it a bit more. What does
the [XX] do? I get a syntax error on the Sum(iif formula at the AND point? I would have thought you would sumiif on the SALE_DATE not REPORT_DATE -- please explain. Thanks "KARL DEWEY" wrote: Try this -- SELECT TaxCredit.COMPANY_ID AS Co, TaxCredit.REPORT_DATE AS PED, (SELECT TOP 1 [XX].SALE_DATE FROM TaxCredit AS [XX] WHERE [XX].SALE_DATE = TaxCredit.REPORT_DATE ORDER BY [XX].SALE_DATE DESC) AS Sdate, Sum(IIF(TaxCredit.REPORT_DATE Between AND DateAdd("d", -60,TaxCredit.REPORT_DATE), TaxCredit.TaxCredit, 0)) AS TaxCredit FROM TaxCredit GROUP BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE ORDER BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE; -- Build a little, test a little. "dallin" wrote: I see your point; however, I need to subtotal by period end date any sale date record that is 60 days less than the period end date: example Co PED Sale date TaxCredit 1 12/31/08 10/31/08 15.00 1 12/31/08 10/31/08 12.00 1 12/31/08 09/30/08 5.00 9 01/31/09 11/30/08 10.15 9 01/31/09 11/30/08 21.15 9 01/31/09 09/30/08 10.02 Result should look like: Co PED SaleDate TaxCredit 1 12/31/08 10/31/08 27.00 9 01/31/09 11/30/08 31.30 "KARL DEWEY" wrote: You can not have details (PED) and total in same query so change like this -- SELECT TaxCredit.COMPANY_ID AS Co, Max(TaxCredit.SALE_DATE) AS Sdate, Sum(TaxCredit.TaxCredit) AS TaxCredit FROM TaxCredit GROUP BY TaxCredit.COMPANY_ID ORDER BY TaxCredit.COMPANY_ID; -- Build a little, test a little. "dallin" wrote: I have the following query: The sum(taxcredit) is not working -- it is summing all records by PED (period end date) where as I only want it to sum the records associated with the sale date that equals Max(Sale_Date). How can I do this? SELECT TaxCredit.COMPANY_ID AS Co, TaxCredit.REPORT_DATE AS PED, Max(TaxCredit.SALE_DATE) AS Sdate, Sum(TaxCredit.TaxCredit) AS TaxCredit FROM TaxCredit GROUP BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE ORDER BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE;
|
|
#13
December 2nd, 2009, 05:53 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Sum Only Subset Records Included in Max Sale Date
Wow -- Nice formula! Could you help me understand it a bit more. What does
the [XX] do? I get a syntax error on the Sum(iif formula at the AND point? I would have thought you would sumiif on the SALE_DATE not REPORT_DATE -- please explain. Thanks "KARL DEWEY" wrote: Try this -- SELECT TaxCredit.COMPANY_ID AS Co, TaxCredit.REPORT_DATE AS PED, (SELECT TOP 1 [XX].SALE_DATE FROM TaxCredit AS [XX] WHERE [XX].SALE_DATE = TaxCredit.REPORT_DATE ORDER BY [XX].SALE_DATE DESC) AS Sdate, Sum(IIF(TaxCredit.REPORT_DATE Between AND DateAdd("d", -60,TaxCredit.REPORT_DATE), TaxCredit.TaxCredit, 0)) AS TaxCredit FROM TaxCredit GROUP BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE ORDER BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE; -- Build a little, test a little. "dallin" wrote: I see your point; however, I need to subtotal by period end date any sale date record that is 60 days less than the period end date: example Co PED Sale date TaxCredit 1 12/31/08 10/31/08 15.00 1 12/31/08 10/31/08 12.00 1 12/31/08 09/30/08 5.00 9 01/31/09 11/30/08 10.15 9 01/31/09 11/30/08 21.15 9 01/31/09 09/30/08 10.02 Result should look like: Co PED SaleDate TaxCredit 1 12/31/08 10/31/08 27.00 9 01/31/09 11/30/08 31.30 "KARL DEWEY" wrote: You can not have details (PED) and total in same query so change like this -- SELECT TaxCredit.COMPANY_ID AS Co, Max(TaxCredit.SALE_DATE) AS Sdate, Sum(TaxCredit.TaxCredit) AS TaxCredit FROM TaxCredit GROUP BY TaxCredit.COMPANY_ID ORDER BY TaxCredit.COMPANY_ID; -- Build a little, test a little. "dallin" wrote: I have the following query: The sum(taxcredit) is not working -- it is summing all records by PED (period end date) where as I only want it to sum the records associated with the sale date that equals Max(Sale_Date). How can I do this? SELECT TaxCredit.COMPANY_ID AS Co, TaxCredit.REPORT_DATE AS PED, Max(TaxCredit.SALE_DATE) AS Sdate, Sum(TaxCredit.TaxCredit) AS TaxCredit FROM TaxCredit GROUP BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE ORDER BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE;
|
|
#14
December 2nd, 2009, 09:21 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Sum Only Subset Records Included in Max Sale Date
sumiif is an Excel function. [XX] is an alias for TaxCredit as I am using
the same table twice but not the same records at the same time. I found an error in field name - had TaxCredit.TaxCredit and should have been TaxCredit.REPORT_DATE. Try it now -- SELECT TaxCredit.COMPANY_ID AS Co, TaxCredit.REPORT_DATE AS PED, (SELECT TOP 1 [XX].SALE_DATE FROM TaxCredit AS [XX] WHERE [XX].SALE_DATE = TaxCredit.REPORT_DATE ORDER BY [XX].SALE_DATE DESC) AS Sdate, Sum(IIF(TaxCredit.REPORT_DATE Between AND DateAdd("d", -60, TaxCredit.REPORT_DATE), TaxCredit.REPORT_DATE, 0)) AS TaxCredit FROM TaxCredit GROUP BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE ORDER BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE; -- Build a little, test a little. "dallin" wrote: Wow -- Nice formula! Could you help me understand it a bit more. What does the [XX] do? I get a syntax error on the Sum(iif formula at the AND point? I would have thought you would sumiif on the SALE_DATE not REPORT_DATE -- please explain. Thanks "KARL DEWEY" wrote: Try this -- SELECT TaxCredit.COMPANY_ID AS Co, TaxCredit.REPORT_DATE AS PED, (SELECT TOP 1 [XX].SALE_DATE FROM TaxCredit AS [XX] WHERE [XX].SALE_DATE = TaxCredit.REPORT_DATE ORDER BY [XX].SALE_DATE DESC) AS Sdate, Sum(IIF(TaxCredit.REPORT_DATE Between AND DateAdd("d", -60,TaxCredit.REPORT_DATE), TaxCredit.TaxCredit, 0)) AS TaxCredit FROM TaxCredit GROUP BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE ORDER BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE; -- Build a little, test a little. "dallin" wrote: I see your point; however, I need to subtotal by period end date any sale date record that is 60 days less than the period end date: example Co PED Sale date TaxCredit 1 12/31/08 10/31/08 15.00 1 12/31/08 10/31/08 12.00 1 12/31/08 09/30/08 5.00 9 01/31/09 11/30/08 10.15 9 01/31/09 11/30/08 21.15 9 01/31/09 09/30/08 10.02 Result should look like: Co PED SaleDate TaxCredit 1 12/31/08 10/31/08 27.00 9 01/31/09 11/30/08 31.30 "KARL DEWEY" wrote: You can not have details (PED) and total in same query so change like this -- SELECT TaxCredit.COMPANY_ID AS Co, Max(TaxCredit.SALE_DATE) AS Sdate, Sum(TaxCredit.TaxCredit) AS TaxCredit FROM TaxCredit GROUP BY TaxCredit.COMPANY_ID ORDER BY TaxCredit.COMPANY_ID; -- Build a little, test a little. "dallin" wrote: I have the following query: The sum(taxcredit) is not working -- it is summing all records by PED (period end date) where as I only want it to sum the records associated with the sale date that equals Max(Sale_Date). How can I do this? SELECT TaxCredit.COMPANY_ID AS Co, TaxCredit.REPORT_DATE AS PED, Max(TaxCredit.SALE_DATE) AS Sdate, Sum(TaxCredit.TaxCredit) AS TaxCredit FROM TaxCredit GROUP BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE ORDER BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE;
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
