select distinct not working

i tried to run this statement but i got some errors...can someone show me how to do this correctly? i want to filter my employee records showing only the records with equivalent transactions on a given date...

VB Code:

d = Date
   strGroupSQL = "SELECT * FROM tblContact "
   
   strDetailsSQL = "SELECT tblContact.ID, tblContact.Name, tblTransaction.Total, tblTransaction.TransID, tblTransaction.Paid, tblTransaction.Total, "
   strDetailsSQL = strDetailsSQL & "tblTransaction.Total - tblTransaction.Paid AS Balance ,tblTransaction.DueDate, tblTransaction.Settled "
   strDetailsSQL = strDetailsSQL & "FROM tblContact Where tblContact.ID IN (Select Distinct tblTransaction.ID from tblTransaction WHERE tblTransaction.Settled LIKE 0 AND tblTransaction.DueDate like " & d & ")"
      
   strShapeSQL = "SHAPE {" & strGroupSQL & "} "
   strShapeSQL = strShapeSQL & "APPEND ({" & strDetailsSQL & "} AS GrpDetails "
   strShapeSQL = strShapeSQL & "RELATE ID to ID) "

anybody???:confused:

search the forums for Distinct. There are a few examples.
You may want to post this in the Database forum.

I would break the SQL into parts. Create a recordset using Distinct, and then run another query on it. Or else, join the tables. Not sure which is the best way.

yeah i tried to break it up and ended up coding like this :

VB Code:

Dim strGroupSQL As String
Dim strDetailsSQL As String
Dim strShapeSQL As String
Dim cus As String
Dim d As Date
 
   d = Date
   strGroupSQL = "SELECT ID, Name FROM tblContact WHERE ID IN (SELECT DISTINCT tblTransaction.ID FROM tblTransaction)"
   
   strDetailsSQL = "SELECT tblContact.ID, tblContact.Name, tblTransaction.Total, tblTransaction.TransID, tblTransaction.Paid, tblTransaction.Total, "
   strDetailsSQL = strDetailsSQL & "tblTransaction.Total - tblTransaction.Paid AS Balance ,tblTransaction.DueDate, tblTransaction.Settled "
   strDetailsSQL = strDetailsSQL & "FROM tblContact INNER JOIN tblTransaction ON tblContact.ID = tblTransaction.ID WHERE tblTransaction.Settled LIKE 0 "
   
   strShapeSQL = "SHAPE {" & strGroupSQL & "} "
   strShapeSQL = strShapeSQL & "APPEND ({" & strDetailsSQL & "} AS GrpDetails "
   strShapeSQL = strShapeSQL & "RELATE ID to ID) "

it returns all the data even the ones without the transactions..and also, when I try to add :

INNER JOIN tblTransaction ON tblContact.ID = tblTransaction.ID WHERE tblTransaction.Settled LIKE 0 AND tblTransaction.DueDate LIKE " & d

there are no data returned....i checked my database date and all was in a short format date mm/dd/yyyy...ive been checking this forum for the entire day already...i havent found what im looking for :confused:

have u tried use '#' bracket for date datatype?

it sure does dude! thanks!!! :D

VB Code:

strGroupSQL = "SELECT ID, Name FROM tblContact WHERE ID IN (SELECT DISTINCT tblTransaction.ID FROM tblTransaction)" 
    
   strDetailsSQL = "SELECT tblContact.ID, tblContact.Name, tblTransaction.Total, tblTransaction.TransID, tblTransaction.Paid, tblTransaction.Total, " 
   strDetailsSQL = strDetailsSQL & "tblTransaction.Total - tblTransaction.Paid AS Balance ,tblTransaction.DueDate, tblTransaction.Settled " 
   strDetailsSQL = strDetailsSQL & "FROM tblContact INNER JOIN tblTransaction ON tblContact.ID = tblTransaction.ID WHERE tblTransaction.Settled = 0 AND tblTransaction.DueDate = #" & d & "#"

BUT, the records are not yet suppressed! it still return the contact records without transactions...:confused:

up

in that case..try use LEFT JOIN instead of INNER JOIN

i already tried that...but it still didnt work...though it returns the correct records , it still return the other unwanted records.,..:confused:

well..can u provide example the value for the table and the result that u want?

ok

my main table is tblContact which contains the basic info of the customer

tblContact

Name
Address

while in tblTransaction , it holds the transaction records made by the customer

tblTransaction
TransID
ID
Item
Balance
DueDate

now i want to return all the records that have a transaction in this day and sort it according to the most number of transactions made...example:

Joe, Smith

TransID ID Item Balance DueDate
1 1 pail 120.00 11/10/2004
2 1 milk 12.30 11/10/2004


Landon, Doe
TransID ID Item Balance DueDate
3 2 nails 120.00 11/10/2004


but whenever i execute my query, it also returns the contact names without the transactions :

april , May
TransID ID Item Balance DueDate


Joe, Smith

TransID ID Item Balance DueDate
1 1 pail 120.00 11/10/2004
2 1 milk 12.30 11/10/2004

Pocahontas, Blah
TransID ID Item Balance DueDate


Landon, Doe
TransID ID Item Balance DueDate
3 2 nails 120.00 11/10/2004



i hope i am understood...
:confused:

Quote:

---

i want to filter my employee records showing only the records with equivalent transactions on a given date...

---

Code:

---

"SELECT tblTransaction.TransID, tblTransaction.ID, " & _
"tblTransaction.Item, tblTransaction.Balance, " & _
"tblTransaction.DueDate, tblContact.Name, tblContact.Address " & _
"FROM tblContact INNER JOIN tblTransaction ON tblContact.ID = tblTransaction.ID " & _
"WHERE tblTransaction.DueDate = #" & format(d,"dd/mm/yyyy") & "#"

---

i tried this and it return only for the Customer that have Transaction at the given date (d)

i'm sorry david..i wish i could say that but it's not my thread..:D

looks like it works. I will remove the post if it isn't! :bigyello:

made it so...

the query is correct but when i do it in my SHAPE command..it still returns the same result.....:confused: ..i need to figure it out soon!!!!

o shape command..i see..
i believe u want the shape should return the same?
i see what i can do..(not sure if it can be done)

thank you and yes sir i need to work it out on the shape command, or if you want ill email you my project...

Code:

---

"SHAPE {SELECT * FROM tblTransaction} " & _
    "APPEND ({SELECT * FROM tblContact} " & _
    "RELATE [ID] TO [ID])"

---

just use that command..

hope it works now :afrog: