Query Design Questions

[Brian]

Ok, I'm fairly new with Access (Using 2003 & a 2000 DB format). I have a
table set up as follows:

ID_Code Data1, Data2,...DataX Month Year

I want to write a query that will show me the data totaled by ID_Code (all
codes) for a given month/year, and the YTD for that same code. The month and
year are input through a user form.

I can create two seperate queries that will give me the current month data,
or a YTD number, but when I try to join them together on a query or report I
get an error about reference the same table. Not sure how to get around
this. Any help is greatly appreciated.

 

[Marshall Barton]

Ok, I'm fairly new with Access (Using 2003 & a 2000 DB format). I have a
table set up as follows:

ID_Code Data1, Data2,...DataX Month Year

I want to write a query that will show me the data totaled by ID_Code (all
codes) for a given month/year, and the YTD for that same code. The month and
year are input through a user form.

Try something like:

SELECT ID_Code,
Sum(IIf(month=Forms!yourform.txtMonth,DataX,0) As MonX,
Sum(DataX) As YTDX,
. . .
FROM yourtable
WHERE year = Forms!yourform.txtYear
And month <= Forms!yourform.txtMonth
GROUP BY ID_Code