Re: grouping data on weekly basis

Tech-Archive recommends: Fix windows errors by optimizing your registry

First, I would use calendar table with property 'week' or something like
that. Point would be to have a mapping of date - week so you can without
additional calculation in query know the week. All that is left at that
point is goruping by week and selecting max() and min() values. Query would
be something like:

select
calendar.week, max(table.value) as MaxValue, min(table.value) as
MinValue
from
table
inner join calendar on table.date = calendar.date
group by
calendar.week


---- in this case, calendar table needs to have only two columns date and
week. However, if week value is not unique over years (and you want it to
be), you can add year column and then add in the join:
AND year(table.date) = calendar.year

MC

PS. If you dont have a calendar table google for it and youll find more then
a couple of scripts for generating it

"preet" <preetkanwaljit> wrote in message
news:uYY93yjfHHA.4536@xxxxxxxxxxxxxxxxxxxxxxx

What I wish to do is to sort data on week basis, calculate the highs and
lows of the week as a whole, how do i do it?

This data is taken from historic data of nseindia.com site, so if this
sounds confusing u can download from there to check.


I have data in this format

ACC EQ 2007-04-13
00:00:00 726.35000000000002 729.85000000000002 750.0 726.0 748.300000000
00007 822711.0 6112.9023189999998
ACC EQ 2007-04-12
00:00:00 732.45000000000005 728.0 733.89999999999998 720.25 726.35000000
000002 440844.0 3210.4995345000002
ACC EQ 2007-04-11
00:00:00 747.30000000000007 745.0 747.10000000000002 711.5 732.450000000
00005 759632.0 5609.9049940000004
ACC EQ 2007-04-10
00:00:00 741.64999999999998 743.0 753.5 733.5 747.30000000000007 929355.
0 6931.2962205000003
ACC EQ 2007-04-09
00:00:00 721.80000000000007 729.0 744.30000000000007 726.5 741.649999999
99998 688120.0 5074.5381635000003
ACC EQ 2007-04-05
00:00:00 714.35000000000002 714.70000000000005 735.95000000000005 710.20
000000000005 721.80000000000007 1377973.0 9994.7093645000004
ACC EQ 2007-04-04
00:00:00 704.30000000000007 680.0 719.70000000000005 678.10000000000002
714.35000000000002 2941263.0 20742.453524
ACC EQ 2007-04-03
00:00:00 704.45000000000005 708.0 718.0 692.14999999999998 704.300000000
00007 1054327.0 7428.0063499999997
ACC EQ 2007-04-02
00:00:00 735.25 720.0 729.89999999999998 698.35000000000002 704.45000000
000005 637955.0 4576.0338105000001
ACC EQ 2007-03-30
00:00:00 734.75 738.80000000000007 745.95000000000005 731.0 735.25 81966
0.0 6050.6284114999999
ACC EQ 2007-03-29
00:00:00 734.70000000000005 734.0 742.0 727.0 734.75 1459171.0 10704.409
7105




--------------------------
My websites
http://www.eecpworld.com/
http://www.jungle-forex.com/


*** Sent via Developersdex http://www.developersdex.com ***


.

Relevant Pages

  • RE: numbering records
    ... function Acc() ... init MyCount to 1 and requery the records. ... > Is there any way to put in a calculated column in a query that will start ... > subform, I can take that field and use it to number each record on the ...
    (microsoft.public.access.queries)
  • RE: Mailmerge in Word: Suppress opening ACCESS 2000 again
    ... I think it's word which is starting a new access application since the query ... which is in the word document so to me it's a bit a world up side down. ... > - I'm referencing the query in the word doc. ... > start the mailmerge from ACC. ...
    (microsoft.public.access.modulesdaovba)
  • Find all records in a field from a table that contains a key word.
    ... Find all records in a field from a table that contains a key word from a ... form control contains values ... acc". ... If i choose acc from my userform 'd like my query to still return all ...
    (microsoft.public.access.queries)
  • MDX and YTD
    ... I would like to run a query resulting the following table: ... That was all I was able to come up with so far (Foodmart): ... Produkt |Acc. ... Verena ...
    (microsoft.public.sqlserver.olap)
  • Re: Find all records in a field from a table that contains a key word.
    ... >If i choose acc from my userform 'd like my query to still return all ... >records that contain "acc" somewhere in that record. ... You can't pass an operator such as LIKE in a criterion. ...
    (microsoft.public.access.queries)