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?