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 :

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.
 
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 :

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.
 
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 :

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.
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Group By Qtr 1
Union Query Edit 1
Parameter Query with DSum 1
Join Query Sort 2
dsum sytax error 5
DSUM criteria 3
dsum issue 1
DSUM question 3

Back
Top