Prev: Limiting the total time for dancing to 15 mins [900 sec]
Next: Expression Builder
From: acss on 29 Jun 2008 16:38
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 ?
From: Wolfgang Kais on 29 Jun 2008 19:35
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


From: acss on 30 Jun 2008 07:56
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
>
>
>
From: Michel Walsh on 30 Jun 2008 09:13
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" <joekru98(a)hotmail.com> wrote in message
news:AA61FE4C-1348-486A-A911-EC14AA935FB4(a)microsoft.com...
> 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
>>
>>
>>


From: acss on 30 Jun 2008 09:39
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" <joekru98(a)hotmail.com> wrote in message
> news:AA61FE4C-1348-486A-A911-EC14AA935FB4(a)microsoft.com...
> > 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
> >>
> >>
> >>
>
>
>
 | 
Pages: 1
Prev: Limiting the total time for dancing to 15 mins [900 sec]
Next: Expression Builder