Simple sql query for recordset


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Simple sql query for recordset

  1. #1
    Join Date
    Apr 2008
    Posts
    13

    Simple sql query for recordset

    Hello,

    I am having some database problems with my Masters project. Being an engineer, I am not aware much of database manipulation, however here is my problem.


    1. I have a simple query problem. I am using vb6.0 using ado/dao recordset which is in a microsoft access file.

    I have a table in this format:

    Code:
    CustNo		ReadingDate		Consumption		ReadingType     Otherfields
    0001		24/5/1998		645.00			N
    0002		23/5/1987		754.00			E
    0002		23/5/1987		754.00			E
    0003		23/5/1991		346.00			N
    0003		23/5/1985		346.00			N

    Is it possible to build sql select query to give me this result:
    Code:
    CustNo		ReadingDate		Consumption		ReadingType	Otherfields
    0001		24/5/1998		645.00			N
    0002		23/5/1987		754.00			E
    0003		23/5/1991		346.00			N
    0003		23/5/1985		346.00			N
    The condition here is an example on CustNo 0002:

    - If 'CustNo' is repeated (which can be more than 2 times also), but if ('ReadingDate', 'Consumption' and
    'ReadingType' fields) for that 'CustNo' are the SAME --> extract only 1 record of that customer, along with
    the 'CustomerNo' and all the 'Otherfields'. Also extract other customer numbers with the same condition.



    2. I need a similar query, if say this is again my recordset:

    Code:
    CustNo		ReadingDate		Consumption		ReadingType     Otherfields
    0001		24/5/1998		645.00			N
    0002		23/5/1983		856.00			N
    0002		23/5/1987		744.00			N
    0002		23/5/1987		744.00			E
    0003		23/5/1991		346.00			E
    0003		23/5/1991		346.00			N

    Is it possible to build sql select query to give me this result:
    Code:
    CustNo		ReadingDate		Consumption		ReadingType	Otherfields
    0001		24/5/1998		645.00			N
    0002		23/5/1987		1600.00			N
    0003		23/5/1991		346.00			N
    0003		23/5/1991		346.00			E
    0003		23/5/1991		346.00			N
    The condition here is an example on CustNo 0002:

    - If 'CustNo' is repeated (which can be more than 2 times also), but if ('ReadingDate' and 'Consumption' fields)
    are DIFFERENT for that 'CustNo' --> Sum all the Consumption of that customer with 'ReadingType'=N and discard (remove)
    the ones with 'ReadingType'=E and put the summed Consumption into one record with the LATEST 'ReadingDate' of that 'CustNo'.

    So basically in the end I will have the summed Consumption of that Customer in one record with the latest reading date, also
    all the other 'CustNo's' and 'Otherfields'. Also extract other customer numbers with the same condition.


    Currently, to remove duplicate customers I am using this query (it selects the max. Consumption value of CustNo's which duplicate and returns one record for them)
    I am unable to use DISTINCT here in the query, since I only want one column to be DISTINCT and others to be NON-DISTINCT, so this is the only way I found.

    Code:
    sql = "SELECT max(" & colname(0) & ") AS " & dbcolnames(0) & ", " _
     & "" & colname(1) & " AS " & dbcolnames(1) & ", " _
     & "max(" & colname(2) & ") AS " & dbcolnames(2) & ", " _
     & "max(" & colname(3) & ") AS " & dbcolnames(3) & ", " _
     & "max(" & colname(4) & ") AS " & dbcolnames(4) & ", " _
     & "max(" & colname(5) & ") AS " & dbcolnames(5) & ", " _
     & "max(" & colname(6) & ") AS " & dbcolnames(6) & ", " _
     & "max(" & colname(7) & ") AS " & dbcolnames(7) & ", " _
     & "max(" & colname(8) & ") AS " & dbcolnames(8) & ", " _
     & "max(" & colname(9) & ") AS " & dbcolnames(9) & ", " _
     & "max(" & colname(10) & ") AS " & dbcolnames(10) & ", " _
     & "max(" & colname(11) & ") AS " & dbcolnames(11) & ", " _
     & "max(" & colname(12) & ") AS " & dbcolnames(12) & ", " _
     & "max(" & colname(13) & ") AS " & dbcolnames(13) & " " _
     & "FROM " & tblNameNew(lngPosition) & " GROUP BY " & colname(1) & ""
    colname(1) is basically the 'CustNo' field and all other columns are other fields.
    Also is it possible to integrate query1 and query 2 from my question, (1) and (2) into one sql query?? So first query1 will run and query2 will run and get me the result
    rather than querying two times separately?

  2. #2
    Join Date
    Feb 2008
    Posts
    162
    1) I believe you can just do:

    SELECT DISTINCT CustNo, ReadingDate, Consumption, ReadingType
    FROM myTable


    2) Without actually doing any testing with real data, I thik this will do what you want:

    SELECT DISTINCT CustNo, MAX(ReadingDate) AS ReadingDate, SUM(Consumption) AS Consumption
    FROM myTable
    WHERE ReadingType = 'N'
    GROUP BY CustNo

    Take a look at the results of 2 and see if that is what you are looking for as the combination of both.
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

Similar Threads

  1. Access SQL query in ASP page
    By Terry in forum ASP.NET
    Replies: 2
    Last Post: 03-12-2003, 07:17 AM
  2. SQL Query Failing
    By Ken D. in forum VB Classic
    Replies: 1
    Last Post: 09-06-2002, 08:31 AM
  3. Replies: 4
    Last Post: 02-21-2002, 03:17 PM
  4. SQL help needed (count query with two tables)
    By Dave in forum VB Classic
    Replies: 3
    Last Post: 05-25-2001, 02:32 PM
  5. IIF in SQL Query Analyzer
    By Jeff Johnson in forum Database
    Replies: 3
    Last Post: 04-30-2001, 02:42 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