Querying for current and previous year's data into single report

[azemc2]

I am fairly new at Access and have come accross a problem I am having a difficult time with. I have a table that lists 3 year's worth of monthly data (10 fields) in chronological order for a total of 81 accounts categorized into 4 different departments. One row per month per account. I need to pull a single report that gives me the a specified month's desired value for each account under a department and also the previous year's values for the same month. Table looks like:

Acct Dept BM/YR Value
100 20 1/1/2000 $1000
100 20 2/1/2000 $1200
100 20 1/1/2001 $800
200 30 1/1/2000 $2500
200 30 1/1/2001 $3000
ETC......

I have created two queries. One that pulls the user's desired month and another that pulls the same month for the prior year. I do not know how to get around the problem of pulling data from two queries from the same table into one report. I tried making a single query from the two individual ones and got error message because both queries called for data from the same table. Any help is greatly appreciated.

 

[gol4]

use the year function

select *
from table
where year(BM/YR)> Year(date)-1