Excluding data

[GLW]

I have a table with the fields AcctNum, TransactType, TransactDate. I need
to query for TransactType BUY where there has not been a TransactType SELL in
the same AcctNum for the 90 preceding the TransactDate. I can't think of a
way to make a sub-query look at AcctNumber and TransactDate so I tried
linking the table to itself in a query like this:
SELECT [MyTable].[AcctNum], [MyTable].[TransactType],
[MyTable].[Transact].[Date]
FROM [MyTable] INNER JOIN [MyTable_1] ON [MyTable].[AcctNum] =
[MyTable_1].[AcctNum]
WHERE ([MyTable].[TransactType] = "BUY") AND (([MyTable_1].[TransactType] =
"SELL") AND ([MyTable_1].[TransactDate] Not Between
([MyTable].[TransactDate]) - 90 and ([MyTable].[TransactDate])))
But, of course, I got a bunch of duplicates and when I try to do SELECT
DISTINCT the connection always times out. Am I at all on the right track?
Can anyone suggest something better? Thanks.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This assumes that you are running this query for a given transaction
date.

Perhaps this (untested):

PARAMETERS [Enter Transaction Date] Date;
SELECT AcctNum, TransactType, TransactDate
FROM table_name As A
WHERE DateDiff("d",
(SELECT MAX(TransactDate) FROM table_name
WHERE AcctNum = A.AcctNum
AND TransactType = "Sell"
AND TransactDate <= [Enter Transaction Date]),
TransactDate) > 90
AND TransactType = "Buy"
AND TransactDate = [Enter Transaction Date]

The DateDiff() function calcs the number of days between the last "Sell"
transaction and the "Buy" transaction on the indicated transcation date.
If that value is >90 then the Account record is returned.

The DateDiff() syntax is:

DateDiff(interval type, earliest date, latest date)

See the Access VBA help article "DateDiff Function" for more info.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYFCCoechKqOuFEgEQL+1ACdFt5y7A45Tuk3bz94qOt97blG8C8AoMg6
T83+U8w+isyZewNaMlhBqWmv
=a1il
-----END PGP SIGNATURE-----