Six Weeks Average

[lansel]

I am trying to build a query that pulls in the last six weeks production information for each one of our employees. I have been using the beginning and ending date criteria, but in a lot of cases one week will have zero information or the employee has less than 8.00 hours, which in that case the production does not apply, then I would have to go back and requested additional weeks. We have situations where an employee may miss several weeks of production, but I would still need to have his most current six weeks production even if I have to go back 20 weeks.

So what I need is the employee most current six weeks production and their standard hours have to be greater than 7.99.

The report I would like to end up with would have each employee listed with six weeks of production with those weeks being the most current and with at least 8.00 standard hours for those weeks listed.

I have looked at the information on TopValues and DateSerial, but I can't see how it would get me what I want???? Suggestions would be greatly appreciated.

Thanks,

 

[Liv Manto]

if your table looks like this:

name---- start---------------------- end
john----- 2/2/2005 12:00:00 PM------ 2/2/2005 6:00:00 PM
john------1/2/2005 1:00:00 PM-------1/2/2005 10:00:00 PM

and you want this:


name---- start-------------------end-------------------hrs-----week
john----- 1/2/2005 1:00:00 PM---2/2/2005 6:00:00 PM---15------4



Create a query with sql like this: (paste this to an sql editor )

SELECT Table1.name, Last(Table1.start) AS start, First(Table1.end) AS [end], Sum(DateDiff("h",Table1!start,Table1!end)) AS hrs, DateDiff("w",Last([Table1]![start]),First([Table1]![end])) AS week
FROM Table1
GROUP BY Table1.name;

 

[lansel]

Thanks for taking the time to respond. Currently I am working on another project and I am unable to get back to work on your recommendation.

I really appreciate your help.

Thanks,

 

[reddyadi]

find the average after every 60 minutes

HI,
I am having a weatehr data in which the temperature and othe parameters were recorded ar every 15 minutes interval but i wnat the average temp at every on hr(60 minutes).
My data is ln this form
Time temperature humidity
0 20 6
15 22 2
30 15 4
45 26 10
60 30 11
75 15 8

i would be thanlsful toa nyone who can help me in writing a query in access.

Thanks

 

[Liv Manto]

SELECT Table1.Time, Table1.Temp, Table1.Humid
FROM Table1
WHERE (((Table1.Time)<>0) AND (([Table1]![Time] Mod 60)=0));


if you wish to get the average only of the above query:




SELECT Avg([mod].Temp) AS AvgOfTemp, Avg([mod].Humid) AS AvgOfHumid
FROM [SELECT Table1.Time, Table1.Temp, Table1.Humid
FROM Table1
WHERE Table1.Time<>0 And Table1!Time Mod 60=0
]. AS mod;

 

[reddyadi]

Average At Every 60 Inute Interval

HI,
I am having a weather data in which the temperature and othe parameters were recorded ar every 15 minutes interval but i want the average temp at every one hr(60 minutes) interval that ejmans the average temperature of frist 60 minutes then the average of the next 60 minutes.......
My data is ln this form
Time temperature humidity
0 20 6
15 22 2
30 15 4
45 26 10
60 30 11
75 15 8

i would be thanlsful toa nyone who can help me in writing a query in access.

Thanks[/QUOTE]