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


T

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.
 
Ad

Advertisements

M

[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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top