Date compare issue

[Jarreman]

Hi, can anyone explain why i am getting duplicate date from the following sql?

SELECT [Scout Details].Scout, Term.Term, IIf([Subs Paid]![Date] Between [Term]![Start] And [Term]![End],"Paid","Owes") AS Status
FROM [Subs Paid], Term, [Scout Details]
WHERE (((Term.Term)>"3 - 2011"));

Basically i have a table giving the start date and end date of each term. i have another table showing who has paid the membership fee (subs) and the date they paid it. what i want to do is see who has paid their fees between the start and end date of each term, and who hasn't.

Cheers
Richard

 

[BobMcClellan]

Hi, can anyone explain why i am getting duplicate date from the following sql?

SELECT [Scout Details].Scout, Term.Term, IIf([Subs Paid]![Date] Between [Term]![Start] And [Term]![End],"Paid","Owes") AS Status
FROM [Subs Paid], Term, [Scout Details]
WHERE (((Term.Term)>"3 - 2011"));

Basically i have a table giving the start date and end date of each term. i have another table showing who has paid the membership fee (subs) and the date they paid it. what i want to do is see who has paid their fees between the start and end date of each term, and who hasn't.

Cheers
Richard

If [Subs Paid], Term, and [Scout Details] are all tables and you are not joining them,
you are using a Cartisean Join. This returns all possible combination's for the included tables.
I imagine you need something like
From [Subs Paid] as sp
inner join [Term] as t on sp.TermId = t.TermId
Inner join [Scout Details] as sd on sp.sdID = sd.sdID

Obviously I don't know the Primary / Foreign Keys of your tables but hopefully
this conveys the point.
If you google for Cartisean Join you will see what I mean.
hth,
bob