satisfy multiple choices from one column and limit time

[jurbin]

Hello everyone. I am having trouble figuring out if I am capable of performing this query without VBA. I have many columns of data, the most important 3 being "Name" "Date" and "Type"

The type can be "buy" "sell" or "RDSP" I need to find a way to query the data so that I can only identify individuals "Name" who had a 'buy' AND either 'Sell' or 'RDSP' within 31 days of each other. So this table contains many lines of transactional data. I need to find if someone placed a buy order, then also a sell or RDSP within 31 days of each other. Is there any way to design a simple query that can do this?

 

[JHB]

One way (the easiest one to understand) is to use 2 queries.
The first query: Pick up all who had a "buy" in the type-field

SELECT YourTableName.Name, YourTableName.Type, YourTableName.Date
FROM YourTableName
WHERE (((YourTableName.Type)="Buy"));

The second one: Link the first query, together with the table one the name-field, put in the query-criteria for the type-field ("sell" or "RDSP") and then pick up records which are Between Date AND Date+31 from the date-field in the first query.

SELECT FirstQuery.Name, YourTableName.Type, YourTableName.Date
FROM FirstQuery INNER JOIN YourTableName ON FirstQuery.Name = YourTableName.Name
WHERE (((YourTableName.Type)="Sell") AND ((YourTableName.Date) Between [FirstQuery]![Date] And [FirstQuery]![Date]+31)) OR (((YourTableName.Type)="RDSP") AND ((YourTableName.Date) Between [FirstQuery]![Date] And [FirstQuery]![Date]+31));

You can certain also use a sub-query. Look in the Help-file or search the Internet.
And to protect your self from problem don't use "Name", "Type" and "Date" as field name, they are reserved words.