Show last 7 days records 1

[jminn0311]

I am querying a table and need the last seven days records. The problem is over the weekend data is not entered so I only get 5 or six records from my query. I need some sql to pull the last seven records even though it is not todays date -7.

SELECT tblPlantFeed.Date, [ep]/120000 AS C2, ([propane]+[iso]+[normal]+[gas])/54000 AS [DeC3 Feed], [propane]/24000 AS C3, ([iso]+[normal]+[gas])/32000 AS [DeC4 Feed], [gas]/14000 AS [14#], ([iso]+[normal])/18600 AS [Dib Feed], [ISO]/7550 AS IC4, [Normal]/11900 AS NC4
FROM tblPlantFeed
WHERE (((tblPlantFeed.Date)>=Date()-7));


Any help would be appreciated

Thanks
Jeff M.

 

[jedraw]

So, can you define the problem in plain English?
Are you really looking for data for 7 days?
Or are you looking for the last 5 workdays?
Or records for the last week?

Is there some combination of
WHERE tblPlantFeed.Date Between (Date()-7))
and Date that could do the job?

 

[jminn0311]

I just need the data from the last seven days it was entered irregardless of todays date.

 

[dhookom]

The first step is to identify the last seven distinct dates in your table. I expect you could create a subquery to return these dates and use it in the criteria:

SELECT tblPlantFeed.Date, [ep]/120000 AS C2, ([propane]+[iso]+[normal]+[gas])/54000 AS [DeC3 Feed], [propane]/24000 AS C3, ([iso]+[normal]+[gas])/32000 AS [DeC4 Feed], [gas]/14000 AS [14#], ([iso]+[normal])/18600 AS [Dib Feed], [ISO]/7550 AS IC4, [Normal]/11900 AS NC4
FROM tblPlantFeed
WHERE tblPlantFeed.Date IN (SELECT TOP 7 [Date] FROM tblPlantFeed GROUP BY [Date] ORDER BY [Date] DESC);

BTW: Date is the name of a function and should never be used as a field name.

Duane
Hook'D on Access
MS Access MVP

 

[jminn0311]

Thank you!!