Ungrouping in DSUM

[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 ?

 

[Wolfgang Kais]

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.

 

[acss]

Thank you for the response but how or where do you build this function into
the SQL statement?

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.

 

[Michel Walsh]

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

Thank you for the response but how or where do you build this function
into
the SQL statement?

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.

 

[acss]

Thanks for the help since this really localized the time frame of the data set.

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

Thank you for the response but how or where do you build this function
into
the SQL statement?

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.