Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2008
    Posts
    6

    Question Unanswered: MSAccess Histogram data query howto.

    Hello, I am trying to create a histogram from data under access. I havent managed to do it yet (surprise surprise).

    the data is structured like this (Table name is p3_res):

    ResID - BudgetQuantity - STResEarlyStart - STResEarlyFinish
    Res1 - 100 - 2007020100 - 2009050200
    Res2 - 750 - 2008020500 - 2008090500
    Res3 - 878 - 2008040600 - 2009082400
    Res4 - 656 - 2009020100 - 2009050700

    The dates are in number format (I know, the dates should be dates but this table is from an odbc connection to a very old program I have to use). the quantity is a number, the ActId is a text string.

    What I am trying to do is to write a query where I get the quantity per month for each month.

    I have searched the forum but everything I try gives absurd results. Finally, now that my brain is fried, I decided to write. This is driving me nuts.

    Any help is welcome. thank you.

    To further detail the problem here is the example I have found and modified.

    TRANSFORM Sum(p3_res.BudgetQuantity) AS SumOfBudgetQuantity
    SELECT p3_res.ResID
    FROM p3_res
    GROUP BY p3_res.ResID
    PIVOT Format(left([p3_res].[STResEarlyStart],8),"mmyy") In ("0108","0208","0308","0408","0508","0608","0708", "0808","0908","1008","1108","1208");

    As a result. I receive an Overflow error.
    Obviously the above does not take into account the quantity that falls in each time frame. but I couldnt go to the moon with a bike. :/
    Last edited by goksu; 05-10-09 at 05:49.

  2. #2
    Join Date
    Nov 2008
    Posts
    6
    ok. after a break and a cup of coffee I believe the problem is in fact three problems. one is the date issue. the below solves that(yes?).

    TRANSFORM Sum(p3_res.BudgetQuantity) AS SumOfBudgetQuantity
    SELECT p3_res.ResID
    FROM p3_res
    GROUP BY p3_res.ResID
    PIVOT Format(dateserial(year(left([p3_res].[STResEarlyStart],4)),month(right(left([p3_res].[STResEarlyStart],6),2)),day(right(left([p3_res].[STResEarlyStart],8),2))),"mmyy") In ("0108","0208","0308","0408","0508","0608","0708", "0808","0908","1008","1108","1208","0109","0209"," 0309","0409","0509","0609","0709","0809","0909","1 009","1109","1209");

    the good news is that the above sql command shows in the design view and runs. So thats one less...

    If the dates of the BudgetQuantity where unique and not something inbetween a date span the above should have worked(yes?).

    So I need to figure a way to find the BudgetedQuantity per day and sum those quantities for each date range(yes?).

    sounds easy, but the code resists the fresh cup of coffee.

  3. #3
    Join Date
    Nov 2008
    Posts
    6
    is the answer closer if we add the below Having close for each date range?
    But I'll have to do this separately for each date range.

    HAVING (((p3_res.STResEarlyStart) Between #1/1/2008# And #1/2/2008#) and ((p3_res.STResEarlyFinish) Between #1/1/2008# And #1/2/2008#));

    How do you think I should approach the issue?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •