Richard Pullman Richard Pullman - 1 month ago 5
SQL Question

Query that Lists Stores to Be Opened

I am trying to write a query that determines which stores on a list need to be "opened" in the database.

I have a "Store Details" table that lists the Store's opening date, and a "Fiscal_Periods" table that lists the Start and End Dates for the Eligibility to be Opened in that Fiscal Period. I need to generate a list of stores that are eligible to be Opened in this period.

SELECT [Store Details].[Store Number], [Store Details].[Store Name], [Store Details].[Open Date], [Store Details].[Closed Date], [Fiscal_Periods].[EligibilityStart], [Fiscal_Periods].[EligibilityEnd]
FROM [Store Details]
WHERE ([Store Details].[Open Date] >=Fiscal_Periods.[EligibilityStart]) AND ([Store Details].[Open Date]<= [Fiscal_Periods].[EligibilityEnd]);


How can I make the statement compare the EligibilityStart and EligibilityEnd to the dates in the current Fiscal_Period?

Answer

In your WHERE-clause you didn't specifiy which Entry in the in the fiscal_period table you want to use for your comparison. In the following code I take the last row where the current date is between Start and EndDate of the Fiscal_Period and compared to that:

SELECT [Store Details].[Store Number], [Store Details].[Store Name], [Store Details].[Open Date], [Store Details].[Closed Date], [Fiscal_Periods].[EligibilityStart], [Fiscal_Periods].[EligibilityEnd]
FROM [Store Details]
WHERE 
(([Store Details].[Open Date])>=DLast("EligibilityStart","Fiscal_Periods","(((EndDate)>Now()) AND ((StartDate)<Now()))"))
AND
(([Store Details].[Closed Date])<=DLast("EligibilityEnd","Fiscal_Periods","(((EndDate)>Now()) AND ((StartDate)<Now()))"));

When you ask next time and get hints like the above, please show what you have. Especially if you already asked a question to that exact amount.