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 |
#1
|
|||
|
|||
Ungrouping in DSUM
I created a query that works well using DSum , grouping and percentages yet i
can not figure out how to target a specific period by date since it breaks up the grouping.My sql is : SELECT VendorInv.InvDesc, Sum(VendorInv.InvAmt) AS Revenue, [Revenue]/DSum("[InvAmt]","[VendorInv]") AS [Percent of Total] FROM VendorInv GROUP BY VendorInv.InvDesc ORDER BY VendorInv.InvDesc; I tried the parameter prompt Between [Enter Begin Date] And [Enter End Date])); which breaks grouping. What am i missing ? |
#2
|
|||
|
|||
Ungrouping in DSUM
Hello "acss".
"acss" wrote: I created a query that works well using DSum, grouping and percentages yet i can not figure out how to target a specific period by date since it breaks up the grouping.My sql is : Why do criteria break up the grouping? SELECT VendorInv.InvDesc, Sum(VendorInv.InvAmt) AS Revenue, [Revenue]/DSum("[InvAmt]","[VendorInv]") AS [Percent of Total] FROM VendorInv GROUP BY VendorInv.InvDesc ORDER BY VendorInv.InvDesc; I tried the parameter prompt Between [Enter Begin Date] And [Enter End Date])); which breaks grouping. What am i missing ? PARAMETERS [Enter Begin Date] DateTime, [Enter End Date] DateTime; SELECT VendorInv.InvDesc, Sum(VendorInv.InvAmt) AS Revenue, [Revenue]/DSum("[InvAmt]","[VendorInv]") AS [Percent of Total] FROM VendorInv WHERE VendorInv.InvDate Between [Enter Begin Date] And [Enter End Date] GROUP BY VendorInv.InvDesc ORDER BY VendorInv.InvDesc; I don't like DSum. If you want to use it, you will have to use the two parameters to build a criteria string that you can pass to the DSum function as it's third argument. -- Regards, Wolfgang |
#3
|
|||
|
|||
Ungrouping in DSUM
Thank you for the response but how or where do you build this function into
the SQL statement? "Wolfgang Kais" wrote: Hello "acss". "acss" wrote: I created a query that works well using DSum, grouping and percentages yet i can not figure out how to target a specific period by date since it breaks up the grouping.My sql is : Why do criteria break up the grouping? SELECT VendorInv.InvDesc, Sum(VendorInv.InvAmt) AS Revenue, [Revenue]/DSum("[InvAmt]","[VendorInv]") AS [Percent of Total] FROM VendorInv GROUP BY VendorInv.InvDesc ORDER BY VendorInv.InvDesc; I tried the parameter prompt Between [Enter Begin Date] And [Enter End Date])); which breaks grouping. What am i missing ? PARAMETERS [Enter Begin Date] DateTime, [Enter End Date] DateTime; SELECT VendorInv.InvDesc, Sum(VendorInv.InvAmt) AS Revenue, [Revenue]/DSum("[InvAmt]","[VendorInv]") AS [Percent of Total] FROM VendorInv WHERE VendorInv.InvDate Between [Enter Begin Date] And [Enter End Date] GROUP BY VendorInv.InvDesc ORDER BY VendorInv.InvDesc; I don't like DSum. If you want to use it, you will have to use the two parameters to build a criteria string that you can pass to the DSum function as it's third argument. -- Regards, Wolfgang |
#4
|
|||
|
|||
Ungrouping in DSUM
Using the grid? at the Total line, have the option WHERE shown, instead of
GROUP BY (or instead of SUM, MIN, MAX, ... ). Vanderghast, Access MVP "acss" wrote in message ... Thank you for the response but how or where do you build this function into the SQL statement? "Wolfgang Kais" wrote: Hello "acss". "acss" wrote: I created a query that works well using DSum, grouping and percentages yet i can not figure out how to target a specific period by date since it breaks up the grouping.My sql is : Why do criteria break up the grouping? SELECT VendorInv.InvDesc, Sum(VendorInv.InvAmt) AS Revenue, [Revenue]/DSum("[InvAmt]","[VendorInv]") AS [Percent of Total] FROM VendorInv GROUP BY VendorInv.InvDesc ORDER BY VendorInv.InvDesc; I tried the parameter prompt Between [Enter Begin Date] And [Enter End Date])); which breaks grouping. What am i missing ? PARAMETERS [Enter Begin Date] DateTime, [Enter End Date] DateTime; SELECT VendorInv.InvDesc, Sum(VendorInv.InvAmt) AS Revenue, [Revenue]/DSum("[InvAmt]","[VendorInv]") AS [Percent of Total] FROM VendorInv WHERE VendorInv.InvDate Between [Enter Begin Date] And [Enter End Date] GROUP BY VendorInv.InvDesc ORDER BY VendorInv.InvDesc; I don't like DSum. If you want to use it, you will have to use the two parameters to build a criteria string that you can pass to the DSum function as it's third argument. -- Regards, Wolfgang |
#5
|
|||
|
|||
Ungrouping in DSUM
Thanks for the help since this really localized the time frame of the data set.
"Michel Walsh" wrote: Using the grid? at the Total line, have the option WHERE shown, instead of GROUP BY (or instead of SUM, MIN, MAX, ... ). Vanderghast, Access MVP "acss" wrote in message ... Thank you for the response but how or where do you build this function into the SQL statement? "Wolfgang Kais" wrote: Hello "acss". "acss" wrote: I created a query that works well using DSum, grouping and percentages yet i can not figure out how to target a specific period by date since it breaks up the grouping.My sql is : Why do criteria break up the grouping? SELECT VendorInv.InvDesc, Sum(VendorInv.InvAmt) AS Revenue, [Revenue]/DSum("[InvAmt]","[VendorInv]") AS [Percent of Total] FROM VendorInv GROUP BY VendorInv.InvDesc ORDER BY VendorInv.InvDesc; I tried the parameter prompt Between [Enter Begin Date] And [Enter End Date])); which breaks grouping. What am i missing ? PARAMETERS [Enter Begin Date] DateTime, [Enter End Date] DateTime; SELECT VendorInv.InvDesc, Sum(VendorInv.InvAmt) AS Revenue, [Revenue]/DSum("[InvAmt]","[VendorInv]") AS [Percent of Total] FROM VendorInv WHERE VendorInv.InvDate Between [Enter Begin Date] And [Enter End Date] GROUP BY VendorInv.InvDesc ORDER BY VendorInv.InvDesc; I don't like DSum. If you want to use it, you will have to use the two parameters to build a criteria string that you can pass to the DSum function as it's third argument. -- Regards, Wolfgang |
Thread Tools | |
Display Modes | |
|
|