Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    5

    Unanswered: Is this SQL query possible?

    Using Access2000, VB6

    I'am not that good when it comes to SQL querys, just knowing the basics
    and a little bit more.

    Is this SQL query possible?
    Summarizing all CustAmount, CustValue from the articles.
    That belongs to a customer and then grouping them by week

    3 tables
    tblCustomer
    tblLinkedCustomer
    tblData

    'tblCustomer
    ID--Customer
    1----K1
    2----K2
    3----K77

    'tblLinkedCustomer
    ID---Article---ArticleID--Customer---CustomerID
    1----112233---3----------K1-------------1
    2----112233---3----------K2-------------2
    3----223311---4----------K77------------3
    4----112233---3----------K2-------------2
    5----554466---1----------K2-------------2
    6----554466---1----------K77------------3

    Rows 2 and 4 differ only in the ID column. Also, why does this table
    have both Customer and CustomerID if that information is in tblCustomer?
    Shouldn't there also be a tblArticle to hold information about articles?

    'tblData
    ID--Article--ArticleID--Customer--CustomerID--CustAmount--CustValue--InsertedWeek
    1--112233---3----------K1--------1-----------12----------120--------0333
    2--112233---3----------K2--------2------------9-----------90---------0333
    3--223311---4----------K77-------3------------4-----------32---------0334
    4--112233---3----------K2--------2-----------15----------150---------0334
    5--554466---1----------K2--------2-----------15----------225--------0333
    6--554466---1----------K77-------3-----------25----------375--------0334

    Looks like tblLinkedCustomer is merely equivalent to,

    select id, article, articleid, customer, customerid from tbldata

    I've filled a treeview with Customer and Article
    K1
    -112233
    K2
    -112233
    -554466
    K77
    -223311
    -554466

    Now to the problem, I need to fill a listview by week
    Summarizing all article that belong to that customer

    When I click on the first customer, into listview
    Customer--CustAmount--CustValue--InsertedWeek
    K1-----------12------------120---------0333

    When I click on the second customer, into listview
    Customer--CustAmount--CustValue--InsertedWeek
    K2-----------24------------315---------0333
    K2-----------15------------150---------0334

    and so on customer by customer..

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    ...hope this leads you in the right direction

    select customer, insertedweek,
    sum(custamount) as custamount, sum(custvalue)as custvalue
    from tblData
    group by customer, insertedweek


    --then to see the detail you will have to use sometype of paramater

    "select customer, insertedweek, custamount, custvalue
    from tblData
    where customer = '" & customer & "'"

    not sure how you will are passing parameter, assuming in vb6, so you will have to dim customer as string and then set it equal to the customer that you selected so when you click on it you pass it to the details query.

    Have fun

Posting Permissions

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