By day of week 1

[ptrifile]

I am trying to figure out how to query a table and pull data that only shows the data for the last 6 weeks and only for Mondays, or tuesday or wednesday etc...

My table only has two colums, "Date/Time" and "Offered" so for example i would like to show the data for each of the last 6 Mondays only. Can someone help me accomplish this?

Thank you in advance,

Paul

 

[ptrifile]

I think I figured out part of this by using the "weekday()" function. So what I need to still figure out is how to lookup the last 6 weeks of data for each day that i want to look at and more importantly i would like to get a total for each of the 6 weeks of data. My data has multiple inputs per day.

hope this made some sort of sense.

Paul

 

[PHV]

A starting point for the last 6 Mondays:
SELECT [Date/Time],Sum(Offered) AS Total
FROM yourTable
WHERE Weekday([Date/Time])=2 AND [Date/Time]>=Date()-42
GROUP BY [Date/Time]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ptrifile]

Thanks PHV, this gives me all of the data but not the total per day (what i figured out with the "weekday" function...my table looks like the following:

date/time Offered
11/19/2012 10
11/19/2012 12
11/19/2012 8
11/19/2012 26
11/12/2012 11
11/12/2012 10
11/12/2012 5
11/12/2012 30

and so on...what I am looking to do with the query is get a total for each Monday in the data...does that make sense? I will keep playing around but if you have any ideas i would greatly appreciate it.

Thanks!

Paul

 

[PHV]

Oops, missed the time portion ...
SELECT DateValue([Date/Time]),Sum(Offered) AS Total
FROM yourTable
WHERE Weekday([Date/Time])=2 AND [Date/Time]>=Date()-42
GROUP BY DateValue([Date/Time])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ptrifile]

Thank you SO much PHV, that is EXACTLY what I was looking for!!!!

 

[ptrifile]

While this is working perfect I am wondering if there is a way to also add an average to "offered" to this query, when i try I get an error about sub queries not being allowed.

thanks again!

Paul

 

[PHV]

What did you try to get an error about sub queries ?
What about this ?
SELECT DateValue([Date/Time]),Sum(Offered) AS Total,Avg(Offered) AS Average
FROM yourTable
WHERE Weekday([Date/Time])=2 AND [Date/Time]>=Date()-42
GROUP BY DateValue([Date/Time])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ptrifile]

Thanks PHV, the reason i got the errors about subqueries (i think) is because what I am looking to do is Average the totals not the "offered". Is there a way to get an Avg of the totals?