Ungrouping in DSUM

  • Thread starter Thread starter acss
  • Start date Start date
A

acss

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 ?
 
Hello "acss".

acss said:
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 :
Click to expand...

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;
Click to expand...
I tried the parameter prompt Between [Enter Begin Date] And [Enter End
Date])); which breaks grouping. What am i missing ?
Click to expand...

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.
 
Thank you for the response but how or where do you build this function into
the SQL statement?


Wolfgang Kais said:
Hello "acss".

acss said:
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 :
Click to expand...

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;
Click to expand...
I tried the parameter prompt Between [Enter Begin Date] And [Enter End
Date])); which breaks grouping. What am i missing ?
Click to expand...

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.
Click to expand...
 
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 said:
Thank you for the response but how or where do you build this function
into
the SQL statement?


Wolfgang Kais said:
Hello "acss".

acss said:
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 :
Click to expand...

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;
Click to expand...
I tried the parameter prompt Between [Enter Begin Date] And [Enter End
Date])); which breaks grouping. What am i missing ?
Click to expand...

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.
Click to expand...
Click to expand...
 
Thanks for the help since this really localized the time frame of the data set.

Michel Walsh said:
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 said:
Thank you for the response but how or where do you build this function
into
the SQL statement?


Wolfgang Kais said:
Hello "acss".

:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top