Subsitution of DatePart("yyyy",Date()) for a year in a form

[pasquis]

Hi!

I have a table were each time someone from a village enters money is recorded, the amount and the date. I want to query that adding the total amounts per month. I managed to do it for a whole year whenever the year is the current one.

SELECT DISTINCTROW water_selling.village, water_selling.name_wug, Sum(IIf(water_selling.date>="1/1/" & DatePart("yyyy",Date()) And water_selling.date<="1/31/" & DatePart("yyyy",Date()),water_selling.amount)) AS amount_january, Sum(IIf(water_selling.date>="2/1/" & DatePart("yyyy",Date()) And water_selling.date<="2/29/" & DatePart("yyyy",Date()),water_selling.amount)) AS amount_february, ...
FROM water_selling
GROUP BY water_selling.village, water_selling.name_wug;

(I just copied january and february...) This works quite fine. But what I'm trying to do is that instead of the current date, the user can fix the year on a form:

SELECT DISTINCTROW water_selling.village, water_selling.name_wug, Sum(IIf(water_selling.date>="1/1/" & [Forms]![FIELDMON_waterselling]![year] And water_selling.date<="1/31/" & [Forms]![FIELDMON_waterselling]![year],water_selling.amount)) AS amount_january, Sum(IIf(water_selling.date>="2/1/" & [Forms]![FIELDMON_waterselling]![year] And water_selling.date<="2/29/" & [Forms]![FIELDMON_waterselling]![year],water_selling.amount)) AS amount_february, ...
FROM water_selling
GROUP BY water_selling.village, water_selling.name_wug;

Why is now not working? I've been trying quite a lot of different things around this query, but I cannot get it...

I'll really appreciate your help!

JoRDi.

 

[neileg]

You haven't said how it is not working!

I can see two problems straight away. You have a control on your form called [year]. Year is a reserved word in Access and should not be used as an object name. Plus you have a field called date. This is also a reserved word. This can cause unpredictable results.

You might also have issues with 29 February if the year is not a leap year.

Not sure why you didn't use DatePart() to extract the month from the date field and group by that. Saves a lot of SQL.

 

[raskew]

Hi -

Here's an example using Northwind's Customers and Orders tables that will:

1) Prompt the user to enter the desired year.
2) Return the sum of Freight charges by Customer and Month, e.g.

[b]Company Name	        Expr3	        SumOfFreight[/b]
Alfreds Futterkiste	September 1995	$29.46
Alfreds Futterkiste	November 1995	$84.96

Note there's no need to specify the first and last day of each month.
You might give it a try and see if it's adaptable to your situation.

SELECT
    Customers.CompanyName
  , Format([OrderDate],"mmmm yyyy") AS Expr3
  , Sum(Orders.Freight) AS SumOfFreight
FROM
   Customers 
INNER JOIN
   Orders 
ON
   Customers.CustomerID = Orders.CustomerID
WHERE
   (((Year([OrderDate]))=[Enter Year]))
GROUP BY
   Customers.CompanyName
  , Format([OrderDate],"mmmm yyyy")
  , Month([OrderDate])
ORDER BY
   Customers.CompanyName
  , Month([OrderDate]);

HTH - Bob

 

[raskew]

Hi -

In addition to the code posted above, this will return a crosstab, by CompanyName and column headings Jan - Dec.

PARAMETERS [Enter Year] Text;
TRANSFORM Sum(Orders.Freight) AS SumOfFreight
SELECT
    Customers.CompanyName
FROM
   (Employees 
INNER JOIN
   Orders 
ON
   Employees.EmployeeID = Orders.EmployeeID) 
INNER JOIN
   Customers 
ON
   Orders.CustomerID = Customers.CustomerID
WHERE
   (((Year([OrderDate]))=[Enter Year]))
GROUP BY
   Customers.CompanyName
ORDER BY
   Customers.CompanyName
PIVOT Format([OrderDate],"mmm") In ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');

Bob

 

[pasquis]

Thanks neileg!
I don't know which of all was the failure, but at least I simplified it and changed all as you suggested and it works! I've done it like:

SELECT DISTINCTROW water_selling.village, water_selling.name_wug, Sum(IIf((DatePart("m",water_selling.[date_rec])=1 And DatePart("yyyy",water_selling.[date_rec])=[Forms]![FIELDMON_waterselling]![date_y]),water_selling.amount)) AS amount_january, Sum(IIf((DatePart("m",water_selling.[date_rec])=2 And DatePart("yyyy",water_selling.[date_rec])=[Forms]![FIELDMON_waterselling]![date_y]),water_selling.amount)) AS amount_february, ...
FROM water_selling
GROUP BY water_selling.village, water_selling.name_wug;

I guess it was too complexe before! Thanks a lot!

And thanks raskew! I had seen before these PIVOT command, the thing is that I'm quite bad on SQL, so I could not manage to make it work... Although now it works without using it, I hope one of this days I'll have a new challenge which will allow me to understand it carefully (This time I learned how to use the DatePart, which is quite useful!)

Thanks!

JoRDi.

 

[neileg]

You didn't go far enough!

SELECT DatePart("m",[DateA]) AS MyMonth, Sum(tblA.amountA) AS SumOfamountA
FROM tblA
GROUP BY DatePart("m",[DateA]);

This will sum the amounts for each month regardless of how many months there are

 

[pasquis]

Uops!
I didn't knew that... But then I need to display it on a form, so I guess I need to have just 12 months and with a name so that I can see them. It has to end up being

January february March .....
payed by a
payed by b
payed by c

JoRDi.

 

[neileg]

Then Bob's advice will take you there.

 

[raskew]

Hi Neileg -

Tried your code. It works well however, as written, it returns just 12 records (months 1 - 12), so if the table contains data over more than a 12 month period, all January data, regardless of year, is summed. Probably need to get a year qualifier in there.

Best wishes - Bob

 

[neileg]

Yes Bob you're perfectly correct. What I was trying to show JoRDi was that he didn't need 12 subqueries to get twelve months data and I kept the SQL to the minimum.