suppress record without equivalent record to other table using SHAPE command

[nyah_RMC]

hi..i have this code for creating a report ...is it possible if i limit my query to those Employee that has a transaction only?? this code returns all the employee records even the ones without transactions....

Private Sub Command1_Click()

If cmbselect.Text = "EMPLOYEES" Then
'this shows the preview window for the report

  DataReport1.Show vbModal
  'DataReport1.Refresh

ElseIf cmbselect.Text = "CUSTOMERS" Then
  DataReport2.Show vbModal
  'DataReport2.Refresh
  
ElseIf cmbselect.Text = "UTILITIES" Then

  DataReport3.Show vbModal
 ' DataReport3.Refresh
  
ElseIf cmbselect.Text = "SUPPLIERS" Then
  DataReport4.Show vbModal
 ' DataReport4.Refresh
  
End If

End Sub


' ============= DATAREPORT1 ==============
Private Sub DataReport_Initialize()
 Dim strSQL As String
 Dim strTo As String
 Dim strFrom As String
 Dim oConn As New ADODB.Connection
 Dim oRS As New ADODB.Recordset
   
  oConn.CursorLocation = adUseClient
  oConn.ConnectionString = DataEnvironment1.Connection1 ' were going use this because it's already done, but you could make your own.
  
  oConn.Open
   

   
  With frmEmpRep
    strTo = .DTPicker1.Value
    strFrom = .DTPicker2.Value
   
  End With
   DataReport1.Sections("Section2").Controls("lbldate").Caption = strTo

  strSQL = "SHAPE { "
  strSQL = strSQL & "SELECT tblEmployee.* FROM tblEmployee WHERE Type LIKE 'Employee' "
  strSQL = strSQL & "} AS Command1 "
  strSQL = strSQL & "APPEND ({ "
  strSQL = strSQL & "SELECT tblTransaction.* FROM tblTransaction "
  strSQL = strSQL & "WHERE DateTrans "
  strSQL = strSQL & "BETWEEN #" & strFrom & "# "
  strSQL = strSQL & "AND #" & strTo & "# ORDER BY BALANCE DESC "
  strSQL = strSQL & "} AS Command2 "
  strSQL = strSQL & "RELATE 'ID' TO 'ID') "
  strSQL = strSQL & "AS Command2 "
    oRS.Open strSQL, oConn, adOpenForwardOnly
  Set DataReport1.DataSource = oRS
   
  
End Sub

[plenderj]

Can you provide your table structure?

[nyah_RMC]

oh here's my table structures

tblEmployee
ID Name Type
1 John Doe Employee
2 Smith Joe Employee


tblTransaction
ID TransID Quantity Price Amount Term
1 1 10 20.30 250 COD
1 2 1 30 30 COD


now on my report, it returns all the Employee records with their transaction...but it also returns let say employee # 2 even without the transaction...i want my report to display all employees that have transactions only..how can i attain that...?

[nyah_RMC]

can we join tables in shape command...?

how can i make a query where Employees with transactions only can be displayed in my data report?

[leinad31]

It's a heirarchical recordset, so simply modify the 'Command1' SQL query so as to include only Emloyee IDs that are IN tblTransaction.ID set.

WHERE Type LIKE 'Employee' AND tblEmployee.ID IN (SELECT DISTINCT tblTransaction.ID FROM tblTransaction)

You might need to use aliases since the table names are recurring

Additional reference.

http://support.microsoft.com/default...NoWebContent=1

[nyah_RMC]

that's pretty interesting..thanks for the link lei31 ..however i have trouble sorting the records according to the balance in my select distinct...i want to display my employee who has the most balance...it says it conflict with the distinct clause when i try to put order by...

[leinad31]

Then tblTransactions will have to be the basis of the employee IDs list cause the balances are there.

SELECT SUM(t.Amount), t.ID FROM tblTransactions t GROUP BY t.ID
ORDER BY SUM(t.Amount) DESC

gives you the IDs sorted descending... You'll have to correlate that list with the other tables. Up to you if you'll add a higher command level to the heirarchy (Command0) or will play around with the other other queries instead.