Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2005
    Posts
    61

    Exclamation Unanswered: Urgent Help For Genuis Programmers

    hi all
    i really need help in this problem
    i have this Tabe
    ---------------------------------------------------------------
    Make Model ModelYear Qty Year Of Sale
    ---------------------------------------------------------------
    Audi A3 2004 09 2004
    Audi A4 2004 35 2004
    Toyota Echo 2005 32 2005
    Toyota Corolla 2005 40 2005
    BMW 318 2006 60 2006
    BMW 320 2006 80 2006

    i worte thi s code to get the Sum Of the Quantities For Spacific Dates
    like 1/1/2004 and 31/12/2006

    List2 Contains Of All The Cars Modles and i Chhose what model i want to see

    here what i do
    rs.Close
    Dim Sql As String
    Dim i As Integer
    Dim k As Integer
    Dim M0 as String
    M0 = Make
    k = List2.ListCount
    Sql = "Select CYear, Sum(Qty) As Qty From CarSales "

    For i = 0 To k
    Sql = Sql & " where " & M0 & " = '" & List2.List(i) & "' and CDate Between
    #" & fd1 & "# and #" & td1 & "# Group By CarSales.CYear "
    rs.Open Sql
    Next i
    Set DataGrid1.DataSource = rs

    when i choose the models for m date 1/1/2004 and 31/12/2006

    its just show the first record ( Audi ) And The Quanties For the 2004 and 2005

    why it just pring the ffirst record ???


    it must gives me that
    2004 44
    2005 72
    2006 140

    Any Help U can Offer
    Plz I need This Urgent

    Regards
    Sami Al Damiri ...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    because of your GROUP BY

    you have GROUP BY CarSales.CYear which means you want one aggregate row in the result for each year in the intermediate result after returning rows from the table which satisfy the WHERE conditions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2005
    Posts
    61
    Thanx For Ur Reply but if I Remove the Group By It will not work
    If u have a way to solve this please show me
    Sami Al Damiri ...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    solve it? that depends on what you are trying to achieve

    try this -- temporarily remove the SUM() and the GROUP BY and use the evil SELECT *

    that way you will see if your WHERE clause is working correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i hope your programming is more precise than your question!

    compare
    List2 Contains Of All The Cars Modles and i Chhose what model i want to see
    vs
    M0 = Make
    where " & M0 & " = '" & List2.List(i)

    so which one is it: model or make???

    M0 = "Make" !!!

    For i = 0 To k -1 !!!

    CDate is a reserved word !!!
    - are you trying to use the intrinsic VBA function CDate() or do you have a field that you didn't mention hidden away in your table with the name 'CDate'. if field, change the name and maybe explain what it is supposed to be and what datatype it is.

    CYear - another magically appearing field (???). what is it? what datatype is it?

    in the example data you provide, "dates" are shown as YYYY. is this real? what datatype are you using in the table to store YYYY?

    rs.Close
    Dim Sql As String
    Dim i As Integer
    Dim k As Integer
    Dim M0 as String

    not recommended - place all declarations at the top of the subroutine/function

    its just show the first record ( Audi ) And The Quanties For the 2004 and 2005
    why it just pring the ffirst record ???

    ...difficult to believe: your SQL is fatally flawed and should return strictly nothing at all.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    Now that Izy has slapped the fact of bare realism in your face (several times) and if the urgency to solve your dilemma also supports a realistic fact, it may then be prudent to supply a small sample of your situation so that necessary modifications can be made to assist you without the need to write a book.

    The post you provided certainly contains code which to say the least, rattles all sorts of alarms...but nobody is perfect and we sometimes tend to stick with whatever works. I personally would also like to see how your SQL Statement actually pulled something out the CarSales table. But then again, I'm just the curious type.

    Provide a small Sample DB with a copy of your table and some non-relevant data.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  7. #7
    Join Date
    Jun 2005
    Posts
    61
    Thanks For all of You And I want To Send My Program To U All And Maybe U will See What I want .
    i Need To Know How I can Attach my Program To Here
    I will Check This Now
    Sami Al Damiri ...

  8. #8
    Join Date
    Jun 2005
    Posts
    61

    Arrow Here is My Attach

    Hi All
    The DataBase Password is: cta2006
    Plz See The TREND TAB WHEN U OPEN THE PROGRAM
    The Program Path Must Be
    C:\MARKAZIA\System
    So Create MARKAZIA Folder
    Then CReate In The MARKAZIA\ System

    Note
    WHEN U Open the GET TREND COMMAND
    REMOVE THIS CODE
    rs.Open " Insert into Qtable Sql"
    Coz I was Trying To Do Somthing .


    Here Is My Email:
    S_Damiri@Hotmail.com

    Regards
    Last edited by Sam76; 03-15-06 at 04:18.
    Sami Al Damiri ...

Posting Permissions

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