Query to bring back 5 days' worth of records

[Jon]

I have a query (reproduced below) that I run which pulls out from a
pair of tables the total of how much footage is run by a given machine
during a given shift, chosen by the user (cboProcess). Now I need to
modify that query so it only pulls out the last 5 production days worth
of data. The gotcha which is complicating it for me is two fold:

1. A given machine may run for up to 3 shifts during any day. As a
result, I can have between 5 and 15 entries for a given machine, and it
will not always be the same number for that machine.

2. A given machine may not run on consecutive days. So I can't simply
tell the query to take the most recent date, subtract 5 from it and
give me from that date forward.

Here's the query:

SELECT Format$(shift_calc.Date,'yyyymmdd') AS [Date By Day],
shift_calc.Shift_Num, Raw.process_nm, Sum(Raw.sqf) AS SumOfsqf
FROM Raw INNER JOIN shift_calc ON Raw.ID = shift_calc.ID
GROUP BY Format$(shift_calc.Date,'yyyymmdd'), shift_calc.Shift_Num,
Raw.process_nm
HAVING (((Raw.process_nm)=[forms]![frmsqftcombobox]![cboProcess]))
ORDER BY Format$(shift_calc.Date,'yyyymmdd') DESC;

Any suggestions on how to do what I want??

 

[[MVP] S.Clark]

Given #2, you'll need to write some custom code to figure out what 5 days
means to each machine number. Use that date as part of your criteria.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Jon]

Just a short follow-up: I had a friend of mine take a look at the
problem and he came back with a solution. Here's his query (albeit
genericized):

SELECT Sheet1.*
FROM Sheet1
WHERE (((Sheet1.Date) In (SELECT TOP 5 Sheet1.Date
FROM Sheet1
GROUP BY Sheet1.Date
ORDER BY Sheet1.Date DESC)))
ORDER BY Sheet1.Date DESC;

So all I need to do now is modify his solution to do what I need it to
do.