SQL query to do data mining on customer records in micorosoft access table for VB 6.0


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: SQL query to do data mining on customer records in micorosoft access table for VB 6.0

  1. #1
    Join Date
    Apr 2008
    Posts
    13

    SQL query to do data mining on customer records in micorosoft access table for VB 6.0

    Suppose I have a table in microsoft access, I use this table in VB 6.0 for my program.

    In the table below, Cons relates to consumption of 24 months of each customer, where consumption is from Cons1 until Cons24, and RU is the reading unit. I have 250,000 records, but just for simplicity I show a few samples.


    Code:
    RU   CustomerNo   Cons1   Cons2   Cons3    Cons4   Cons5  .......  Cons24
    001    000005     453.4   765.5   454.7    564.6   867.4  .......  675.1
    001    000007     575.5   576.7   745.3    464.1   543.1  .......  565.7
    001    000016     253.3   763.5   565.7    352.6   853.3  .......  876.4
    006    000108     373.0   457.2   547.4    355.7   685.3  .......  675.2
    007    000206     674.5   435.7   546.2    456.3   565.7  .......  765.5
    007    000231     434.7   564.7   645.7    423.1   544.8  .......  876.6
    009    000467     566.8   534.3   676.8    767.2   453.7  .......  436.7
    009    000577     745.5   575.1   565.6    565.4   569.6  .......  563.8
    010    000753     465.7   234.6   655.7    545.7   565.7  .......  345.3
    I wish to write an SQL query to do the following:

    1. Firstly, I wish to average of the 24 consumptions (Cons1 to Cons24) all customers. For each customer this will be done using: (sum (Cons1 to Cons24)) / 24.

    2. Secondly, I wish to find the number of customers in every RU.

    3. Thirdly, for every RU (reading unit), I wish to find the average customer consumption for all of the customers inside that RU. This will use the results from Step 1.

    For example, for two RUs:
    Code:
          RU    CustomerNo  AvgCustCons
          001    000005      436.5
          001    000007      765.4
          001    000016      704.1
          006    000108      657.7
          007    000206      867.1
          007    000231      176.3
    This should be like this:
    Code:
          RU     RUCons 
          001    436.5 + 765.4 + 704.1 = Show the summed value
          006    657.7
          007    867.1 + 176.3 = Show the summed value
    In the end, the final result should be something like this:

    Code:
    RU  NoOfCust  RUCons
    001     3     436.5 + 765.4 + 704.1 = Show the summed value
    006     1     657.7
    007     2     867.1 + 176.3 = Show the summed value
    009     2     some value here
    010     1     some value here
    Then later, I can find the AvgRU consumption using this query:
    SELECT RU, (NoOfCust/RUCons) FROM MYTABLE


    How can this be done, is it possible to do all these things, using one SQL statement?? All help is appreciated.

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Try something like this
    Code:
    SELECT RU, COUNT(CustomerNo) AS CustNum, SUM(AVG(Cons1 + Cons2 + etc etc etc + Cons24)) AS RUConsump 
    FROM yourtable
    GROUP BY RU
    What database are you using?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Apr 2008
    Posts
    13
    Thanks for the help, appreciate it. I use Microsoft access database (mdb). I use VB 6.0, using the ADO/DAO recordsets. I will change what is necessary.

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    No problem...lets us know if you run into any issues.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  5. #5
    Join Date
    Apr 2008
    Posts
    13
    Thanks, however the query you designed, gave an error in Microsoft Access 2007... "aggregrate function" something, because of the "AVG". I tried "AVERAGE" also, but the same thing.

    Anyway, my desired results are achievable with this query, I have made now:

    Code:
    SELECT RU, COUNT(CustomerNo) AS NoOfCustomers, SUM((N1+N2+N3+N4+N5+N6+N7+N8+N9+N10+N11+N12+N13+N14+N15+N16+N17+N18+N19+N20+N21+N22+N23+N24)/24) AS RUConsumption, (RUConsumption/NoOfCustomers) as AvgRUConsumption FROM Features GROUP BY RU
    Thanks alot!

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    This doesn't apply here because you have solved your problem, but, for future reference, this
    Quote Originally Posted by awyeah View Post
    gave an error in Microsoft Access 2007... "aggregrate function" something)
    doesn't mean anything.

    If you are reporting an error message, we would need to know the exact wording.

    Thanks and glad you got it fixed.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  7. #7
    Join Date
    Apr 2008
    Posts
    13
    Eventhough the problem is fixed,

    The error was:

    Cannot have aggregate function in expression (SUM(AVG(N1+N2))).
    Currently, I am using the same query as you gave me, with only two Consumption columns I have N1 and N2.

    Even with all the 24 consumption columns N1 to N24 it gives me the same error message in Microsoft Access 2007.

Similar Threads

  1. Importing SQL Table to the Data Grid
    By software_develo in forum .NET
    Replies: 5
    Last Post: 11-18-2005, 05:11 PM
  2. Accessing Data in MS Access from SQL SERVER
    By phillysri in forum VB Classic
    Replies: 1
    Last Post: 10-29-2005, 12:31 AM
  3. ADO / Data Environment and SQL Query
    By Success in forum VB Classic
    Replies: 1
    Last Post: 01-31-2002, 02:51 PM
  4. Replies: 0
    Last Post: 11-20-2000, 06:32 AM
  5. One FWOOMP table copy - Access to SQL Svr - over Internet
    By Johnny Adams in forum VB Classic
    Replies: 1
    Last Post: 07-30-2000, 11:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center