Conditional formula and output

[blam83]

Hi all,

I am rather raw to Access, and I am struggling to deal with a conditional formula to deal with an issue I have at work.
It would be greatly appreciated if someone can kindly assist.

I have attached the file I have in question.

Essentially there are 2 data tables. “JoinDate” and “Spending”.
I want to do something that will turn the output into the table as “DesiredOutput”. You will notice this table is identical to “Spending”, except the addition of the last column titled [Include].
I want a formula to give me the result for [Include].

The conditions I want are:
1) Bring together “JoinDate” and “Spending”.
2) “JoinDate” includes the membership joining date of members.
3) “Spending” includes the spending pattern of each members.
4) I want a formula that will put a “Y” (denotes as “yes”) for every spending that:
a) occurred ON or AFTER the joining date; and
b) occurred within 360 days of FIRST SPENDING DATE that occurred after the join date

As an example.
Mike spent money on these months-year: Jan-12, March-12, Oct-12, Jun-13.
Mike’s joining date is Feb-12.
The only “Y” would be March-12 and Oct-12.
Jan-12 is a No because it is BEFORE the joining date.
Jun-13 is a No because it is after the 360 days of the first spending date that occurred after the join date (being March-12).


MANY THANKS


Brian

[June7]

Names make very poor unique identifiers.

Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention. Also should not use reserved words as names. Name is a reserved word.

Criteria a) is easy.

SELECT JoinDate.Name, JoinDate.[Membership start date], Spending.[Shopping Date], Spending.[Spending$], IIf([Shopping Date]>=[Membership start date],"Y","N") AS Include
FROM Spending INNER JOIN JoinDate ON Spending.Name = JoinDate.Name;

Criteria b) is not easy. This will require a nested subquery or domain aggregate function. Example with DMin().

SELECT JoinDate.Name, JoinDate.[Membership start date], Spending.[Shopping Date], Spending.[Spending$], IIf([Shopping Date] Between [Membership start date] And [First]+360,"Y","N") AS Include, CDate(DMin("[Shopping Date]","Spending","[Name]='" & [JoinDate].[Name] & "' AND [Shopping Date]>#" & [Membership start date] & "#")) AS [First]
FROM Spending INNER JOIN JoinDate ON Spending.Name = JoinDate.Name;