Access 2000 YTD questions

[Guest]

I need to create a query for a report to capture YTD totals (counts). I have
a table set up with customer complaints by region.. To get the initial
March-05, I have the following fields set in my query:

ComplaintMonth: Format([ComplaintRecd],"mmm") - Criteria = [Enter Month]
CountCurrentYear: IIf(Format([ComplaintRecd],"yyyy")=Year(Now()),1,0)

However, the query needs to show the following... the YTD figures in this
case would be from January to March of 2005, January to March of 2004, etc..

Mar-05 YTD-2005 YTD-2004 YTD-2003
10 15 16 30

I have tried a few different things and just cant come up with something
that works.. any help would be greatly appreciated.

Thanks

 

[Allen Browne]

A subquery will be the easiest way to do this.

1. In your main query, declare a parameter named MonthNumber, where the user
has to enter a value from 1 to 12. Choose Parameters on the Query menu. In
the dialog, enter:
MonthNumber Long Integer

2. Type something like this into a fresh column in the Field row of your
query:
ThisYear: (SELECT Count("*") FROM [Table1] AS Dupe
WHERE Dupe.[MyDate] >= DateSerial(Year(Date()),1,1) And
Dupe.[MyDate] < DateSerial(Year(Date()),[MonthNumber]+1, 1))

3. Replace "Table1" with the name of your table, and "MyDate" (twice) with
the name of your date field.

4. Add more fields for previous years. For example:
LastYear: (SELECT Count("*") FROM [Table1] AS Dupe
WHERE Dupe.[MyDate] >= DateSerial(Year(Date())-1,1,1) And
Dupe.[MyDate] < DateSerial(Year(Date())-1,[MonthNumber]+1, 1))

If subqueries are a new idea, Microsoft's introduction is at:
http://support.microsoft.com/?id=209066

The query uses the DateSerial() function to build the year-to-date ranges.
Instead of using "Between ... And ...", it uses "less than the next date" so
it includes any records from the last day of the month that have a time
component.

 

[Guest]

Alan... thanks.. i still havent gotten this to work... i'm going to try
an iif/and/or statement and see how that goes

A subquery will be the easiest way to do this.

1. In your main query, declare a parameter named MonthNumber, where the user
has to enter a value from 1 to 12. Choose Parameters on the Query menu. In
the dialog, enter:
MonthNumber Long Integer

2. Type something like this into a fresh column in the Field row of your
query:
ThisYear: (SELECT Count("*") FROM [Table1] AS Dupe
WHERE Dupe.[MyDate] >= DateSerial(Year(Date()),1,1) And
Dupe.[MyDate] < DateSerial(Year(Date()),[MonthNumber]+1, 1))

3. Replace "Table1" with the name of your table, and "MyDate" (twice) with
the name of your date field.

4. Add more fields for previous years. For example:
LastYear: (SELECT Count("*") FROM [Table1] AS Dupe
WHERE Dupe.[MyDate] >= DateSerial(Year(Date())-1,1,1) And
Dupe.[MyDate] < DateSerial(Year(Date())-1,[MonthNumber]+1, 1))

If subqueries are a new idea, Microsoft's introduction is at:
http://support.microsoft.com/?id=209066

The query uses the DateSerial() function to build the year-to-date ranges.
Instead of using "Between ... And ...", it uses "less than the next date" so
it includes any records from the last day of the month that have a time
component.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to create a query for a report to capture YTD totals (counts). I
have
a table set up with customer complaints by region.. To get the initial
March-05, I have the following fields set in my query:

ComplaintMonth: Format([ComplaintRecd],"mmm") - Criteria = [Enter Month]
CountCurrentYear: IIf(Format([ComplaintRecd],"yyyy")=Year(Now()),1,0)

However, the query needs to show the following... the YTD figures in this
case would be from January to March of 2005, January to March of 2004,
etc..

Mar-05 YTD-2005 YTD-2004 YTD-2003
10 15 16 30

I have tried a few different things and just cant come up with something
that works.. any help would be greatly appreciated.

Thanks