select distinct not working [moved]
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...i mean i want to suppress my records displaying only those records with transactions that corresponds to a certain date... ??
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) "
Re: select distinct not working [moved]
Quote:
Originally posted by nyah_RMC
i tried to run this statement but i got some errors...
What errors?
Quote:
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...
i mean i want to suppress my records displaying only those records with transactions that corresponds to a certain date... ??
I cannot see any filters in the where clause for dates?
Hint: Use hashes around the date.
Quote:
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, [b][Name][/b] FROM tblContact WHERE ID IN (SELECT DISTINCT tblTransaction.ID FROM tblTransaction)"
strDetailsSQL = "SELECT tblContact.ID, tblContact.[Name], tblTransaction.[Total], tblTransaction.TransID, tblTransaction.Paid, [i]tblTransaction.[Total][/i], "
strDetailsSQL = strDetailsSQL & "(tblTransaction.[Total] - tblTransaction.Paid) AS Balance ,tblTransaction.DueDate, tblTransaction.Settled "
strDetailsSQL = strDetailsSQL & "FROM tblContact INNER JOIN tblTransaction ON [i]tblContact.ID = tblTransaction.ID[/i] WHERE tblTransaction.Settled LIKE 0 "
strShapeSQL = "SHAPE {" & strGroupSQL & "} "
strShapeSQL = strShapeSQL & "APPEND ({" & strDetailsSQL & "} AS GrpDetails "
strShapeSQL = strShapeSQL & "RELATE ID to ID) "
The bit in bold may need square brackets (inserted) as Name is a function.
The bit in italics (middle) looks like its a duplicate field?
The bit in italics (bottom) is joining on the wrong id? Possibly?
Vince