Help - Categories and dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that looks like this

petty cash fund $100 1/03/2005
petty cash fund $200 1/04/2005
petty cash fund $400 1/06/2005

when i made a query that finds "petty cash fund" for a certain date range...
(example from 1/03/2005 - 1/06/2005) for my report it returned the same view,
i want the "petty cash fund" be grouped and to have the sum but when i use
group by in the query i get an error message... I believe that it fails to
group the "petty cash fund" due to the dates... but if i take out the dates
from my query i would not be able to search by date range...Is there any way
to do this? Your Help would be very much appreciated...
 
The text of the error message would be helpful.

It isn't necessary to include (i.e., "check") the date field, if all you
want to do is select by a date range.
 
You use sub-querys for this or in other other words a query based on a query.
Your initial select query is as below to give you the data for the required
date range.
You then create another query with the query below as the source and then
group on petty cash fund, totalling the amount. You can ignore the dates
because you know it has been pre-selected with the initial query.
 
This is the SQL for my Grouped_Union_BS query:

SELECT Union_BS.AccountDescription, Sum(Union_BS.NetofVAT) AS SumOfNetofVAT,
Union_BS.Cat, Union_BS.AL, Union_BS.Date
FROM Union_BS
GROUP BY Union_BS.AccountDescription, Union_BS.Cat, Union_BS.AL,
Union_BS.Date;

And this is the SQL for my select query:

SELECT AccountDescription, SumOfNetofVAT, Cat, AL
FROM Grouped_Union_BS
WHERE (((Grouped_Union_BS.Date) Between [Enter Start Date (dd/mm/yyyy)] And
[Enter End Date (dd/mm/yyyy)]))
ORDER BY Cat;

It is important that i use the select by date range...
I am still having trouble grouping the AccountDescription, Cat, AL, and
getting the Sum of SumOfNetofVAT. With this it gives me the same
AccountDescription for each date in my report from a given date range. I
tried grouping in my select query changing "ORDER BY Cat" to "GROUP BY
AccountDescription" but the error message appears telling me....

"You tried to execute a query that does not include the specified expression
'Cat' as part of anaggregate function."

"Cat" is a just a field... short for category...

Please help me get back on track.. thanks in advance!!! ^_^
 
Back
Top