Query to return data if whole months are selected based on week

[Tim Smith]

I'm trying to get data from a Sales table. The query must be able to accept
a start & end weeks and fiscal year parameters and return sales data if the
start and end weeks indicate that whole months are being asked for. If
either week parameter falls outside the start or end weeks in the month
table, the query cannot return anything.

For example:

WkBeg = 1, WkEnd = 8, Year = 2004
Return sales for Jan, Feb 2004.

WkBeg = 9, WkEnd = 13, Year = 2004
Return sales for Mar 2004.

WkBeg = 1, WkEnd = 7, Year = 2004
<return nothing, not even Jan sales>
because the end week does not match the WkEnd

WkBeg = 2, WkEnd = 17, Year = 2004
<return nothing>

WkBeg = 1, WkEnd = 17, Year = 2004
Return sales for Jan, Feb, Mar, Apr 2004

tblSales
MonthNum MonthName FiscalYear Sales
1 Jan 2004 100
2 Feb 2004 150
3 Mar 2004 125

tblMonths
WkBeg WkEnd MonthNum MonthName FiscalYear
1 4 1 JAN 2004
5 8 2 FEB 2004
9 13 3 MAR 2004
14 17 4 APR 2004



Thanks for the help.

 

[[MVP] S.Clark]

The logic is probably too complex to pull off in just one query.

I would create a form to prompt for the info, then create inline SQL
statements or WHERE clauses.

 

[Tim Smith]

Thanks. I eventually did it by using Exists (had help).