Nested Query Question Access 2007

[databased]

Hello



I am trying to write a query that I need to create a report that will ask the user to input two dates (GIFTDATE) and then list the users (MIDNO) whose gifts (GIFTAMOUNT) in the first year is less than in the second year.

The table gifts has midno (giver ID number), giftamount and giftdate.

This is what I have, but it doesn't work:

SELECT
gifts.midno,
(SELECT d1.giftamount AS date1amt, d1.midno FROM gifts AS d1 WHERE giftdate Like "*"&[date1]&"*"),
(SELECT d2.giftamount AS date2amt, d2.midno FROM gifts AS d2 WHERE giftdate Like "*"&[date2]&"*"),
entities.name
FROM entities, gifts
WHERE
date1amt < date2amt
and d1.midno = d2.midno


Basically, I want to create two columns of giftamounts, one for the first year and one for the second year, and then pick out the ones where the giftamount in the first year is less than the second year.

Any help?

Thanks!!

[weekend00]

Would you please clairify these:
1 do you mean user input two years like 2009 and 2010, not two dates?
2 is there only 1 record for each year for each user(MIDNO)?
3 is GIFTDATE a date/time field?
4 what is table entities for?

SELECT d1.midno,d1.giftamount,d2.giftamount from
gifts AS d1 inner join gifts as d2 on d1.midno=d2.midno where year(d1.giftdate)=[first year] and year(d2.giftdate)=[second year] and d1.giftamount<d2.giftamount

[databased]

1 do you mean user input two years like 2009 and 2010, not two dates?
Yes, the GIFTDATE field is a date field (mm/dd/yyyy), but the purpose of the query is to compare gifts given per year.

2 is there only 1 record for each year for each user(MIDNO)?
There could be multiple records in a year for each user(MIDNO)

3 is GIFTDATE a date/time field?
Date (mm/dd/yyy)

4 what is table entities for?
the entities table has user name fields as well as MIDNO, so I can show the user name in the report.

I should note that the tables were imported via ODBC from a Visual Foxpro database that did not allow me to use SQL to create the reports.

What I want to do is create a (standaone?) program using Access to allow several of these custom reports to be run easily.

Thanks much.

[weekend00]

Since there could be multiple records in a year for each user(MIDNO), the query that I posted can not do the work, we need sub query.

Did you mean you table is a linked table to foxpro?

[databased]

Would DISTINCT work if added to your SELECT statement?

I originally was going to link to the Foxpro DB but to be safe I decided to import the DB into Access. I was going to write a macro that imports the Foxpro db every time the Access db opens, so that the Foxpro data is always current. I might have to link it if that doesn't work.

[weekend00]

DISTINCT doesn't work for this case.
Need to create a query to make MIDNO unique:
Query1: SumOfGifts
select MIDNO,sum(gifts.giftamount) as giftamount from gifts

Query2: MyQuery
SELECT d1.midno,d1.giftamount,d2.giftamount from
sumofgifts AS d1 inner join sumofgifts as d2 on d1.midno=d2.midno where year(d1.giftdate)=[first year] and year(d2.giftdate)=[second year] and d1.giftamount<d2.giftamount