Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2005
    Location
    Arnhem, Gld, NL
    Posts
    21

    Unanswered: Creating weeklyreports using sql

    Hi all,

    The company i work for asked me to generate a report for a given week using the orders who are stored in a database.

    The table containing the orders has the following fields:
    -Custommernumber
    -Weight
    -Date (Custommers can take more order on one day)
    -more, but shall not bother you with that

    I want a report that tells me the totals per customer, per day:
    Code:
                 sunday   monday   thuesday  wednesday -> etc..
    ---------------------------------------------------
    custom1     500      600         445           0
    custom2      0        1500       0          1500
    custom3     150       0          400        4900
    Now,..
    How can i genrate a report like this using sql?
    I have a report like this now, but use visual basic to calculate the dates and run a query seven times, dor every date in the given week...
    It is slow, and i can't get it in a MS Access report.

    I would like to hear if it is possible to generate a report like this using a query..

    Hope to hear from you guys!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't know Access, but this Oracle hint might help you do the job. It is done using the so called pivot query:
    Code:
    SELECT * FROM 
      (SELECT customer,
              SUM(DECODE(day, 1, total)) sunday,
              SUM(DECODE(day, 2, total)) tuesday,
              SUM(DECODE(day, 3, total)) wednesday
       FROM some_table
      )
    ORDER BY 1;
    Basically, it says:
    IF day = 1 THEN SUM total and call it "Sunday"
    IF day = 2 THEN SUM total and call it "Monday", etc.

    Date column should be formatted in order to return day name.

    I *think* (reading forum messages) that Access has "IIF" (should do the same as Oracle DECODE function), so perhaps you might try with it.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in microsoft access, you can actually use a crosstab query

    moving thread to access forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2005
    Location
    Arnhem, Gld, NL
    Posts
    21
    Littlefoot and r937, you both had the same idea here it seems to me.
    I have looked into it via Google and found usefull tutorials and other articles about this kind of queries.

    I never heard about a pivot-query before, but it looks 'simple' and solid, thanx for the advice and hints!

  5. #5
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Take a look at the Weekday function. It will decode your date into a number representing the day of the week which you can then use a lookup to turn into a day string.

    As r937 says, a crosstab query is the way to go. However, note that the columns tend to get sorted in alphabetical order so you need to think how to avoid this. If your column heading are always Sun->Sat then you could fix these as static labels in your report. If you calc the date using weekday then the columns will be sorted in number sequence which is fine. weekday also allows you to specify what the first day of the week is.


    hth
    Chris

  6. #6
    Join Date
    Dec 2005
    Location
    Arnhem, Gld, NL
    Posts
    21
    Hi all,..
    I managed to get hold on a good example of a crosstab query and fixed this problem.
    I took me a while to get it right. The MS Access wizard helped me, but could not do the trick because i needed a SQL query, not an Access query (Mysql does not support Transform an Pivot)
    Here is the query i use now:
    Code:
        s = "SELECT klant_naam, " & _
                "SUM(IIF(d_datum = #" & Format(d, "yyyy-mm-dd") & "#, t_gewicht, 0)) AS 1, " & _
                "SUM(IIF(d_datum = #" & Format(d + 1, "yyyy-mm-dd") & "#, t_gewicht, 0)) AS 2, " & _
                "SUM(IIF(d_datum = #" & Format(d + 2, "yyyy-mm-dd") & "#, t_gewicht, 0)) AS 3, " & _
                "SUM(IIF(d_datum = #" & Format(d + 3, "yyyy-mm-dd") & "#, t_gewicht, 0)) AS 4, " & _
                "SUM(IIF(d_datum = #" & Format(d + 4, "yyyy-mm-dd") & "#, t_gewicht, 0)) AS 5, " & _
                "SUM(IIF(d_datum = #" & Format(d + 5, "yyyy-mm-dd") & "#, t_gewicht, 0)) AS 6, " & _
                "SUM(IIF(d_datum = #" & Format(d + 6, "yyyy-mm-dd") & "#, t_gewicht, 0)) AS 7, " & _
                "SUM(IIF(d_datum >= #" & Format(d, "yyyy-mm-dd") & "# AND d_datum <= #" & Format(d + 6, "yyyy-mm-dd") & "#,t_gewicht,0)) AS [tot]  " & _
            "FROM draairegel inner join klant on draairegel.klant_nr = klant.klant_nr  " & _
            "GROUP BY klant_naam  " & _
            "ORDER BY klant_naam "
        
        Me.RecordSource = s
    I pass an argument to the report, the start date, and in my report i calculate the days onward to build up the query...
    After the query is complete i set it as the recordsource of the report.

    Thanx for helping me out with this!
    i attached a sample of my result, i'm still working on it though...

    Thanx
    Attached Thumbnails Attached Thumbnails sc.gif  
    Last edited by MoonCrawler; 02-01-06 at 06:37.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sheesh, dude, you said you were using microsoft access, you never mentioned that you wanted something to run in mysql

    by the way, to save you another posting, IIF won't work in mysql either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by MoonCrawler
    SQL query, not an Access query (Mysql..
    Just to save you some pain in the future...

    Access, MSSQL, Oracle, DB2, MySql, Paradox etc ALL use SQL. SQL is a generic protocol, not a brand.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Teddy
    Access, MSSQL, Oracle, DB2, MySql, Paradox etc ALL use SQL.
    let's be even more precise:

    Access, MSSQL, Oracle, DB2, MySql, Paradox etc all use their own version of SQL.

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by r937
    let's be even more precise:

    Access, MSSQL, Oracle, DB2, MySql, Paradox etc all use their own version of SQL.

    Too be EVEN more precise:

    They all use SQL. AND they all have specialized extensions applicable to their database's implementation (that may or may not be applicable in other databases) that take advantage of the structure of the DB ...


    P.S. - Not everyday you get to one up Wayne ...
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would hardly classify the IIF function as "take advantage of the structure of the DB"

    who's Wayne?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by r937
    i would hardly classify the IIF function as "take advantage of the structure of the DB"

    who's Wayne?
    Damn ... Sorry Rudy ... I meant you ... I definitely need to pay much closer attention to whom I am talking about ... Likewise, you can call me Sam ...

    - Mike
    Back to Access ... ADO is not the way to go for speed ...

  13. #13
    Join Date
    Dec 2005
    Location
    Arnhem, Gld, NL
    Posts
    21
    and whos starting his post with shees dude,..
    Quote Originally Posted by r937
    sheesh, dude, you said ...r
    These kinds of discussions are even more worse
    but no hard feelings..
    Thanks for the clarification guys
    Next time a ask a question i hope to find the good words and terms

Posting Permissions

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