Simple sql query for recordset

[awyeah]

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:

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:

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:

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:

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.

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?

[Slope]

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.