YTD Subquery problems

[equalizer]

I am trying to create a report that allows the user to select a
specific month and year for which to see MTD and YTD sales figures for
specific products. It must be a summary report so I only want it to
show product sales information for the selected month. Here's an
example:

Product Date MTD YTD
Product1 Feb, 2006 100 200
Prdouct2 Feb, 2006 200 250
Product3 Feb, 2006 300 350


After researching, I thought a subquery would be the best way to
accomplish this but I've never used subqueries and after several
attempts I have failed to get it to work the way I need it to. Can
anyone help me?

 

[Van T. Dinh]

You need to describe your Table details and the start of your Financial Year
as it is diffrent from country to country.

Please post also the SQL Strings of your attempts.

 

[equalizer]

Sorry about that. I'm new and didn't know which group would give me the
best response, so I posted to all of them I thought applied to my
situation.

 

[equalizer]

The table is a Sales Order transaction table that has product,
transactiondate, qty, amount, and sales order number fields in it. The
financial year begins on Jan 1.

Here is an example of the subquery I've attempted:

(SELECT Sum(qty) FROM SOTransaction WHERE
SOTransaction.[transactiondate] >= dateserial(Forms![Date
Parameters]![Year],1,1) AND SOTransaction.[transactiondate] <
dateserial(Forms![Date Parameters]![Year],Forms![Date
Parameters]![Month]+1,1))

As I described earlier, the month and the year are chosen by the user
on a form named "Date Parameters." They choose a month from a drop-down
and a year from a drop-down before the query is opened and calculated.
I've tried putting the subquery in the Field row of the query and in
the Criteria row under the "qty" field but neither of them give me what
I need. When I put it in the Field row it calculates the YTD amount for
all products and enters that in for each field, rather than calculating
the YTD amount for each product separately. Hope this helps. Thanks!