How to do this?

[Gasman]

Hi all,

Making good progress on this database, but have hit a big bump (to me anyway) now.

The VBA code below exports the relevant records for trades with payments attached.
However I have to issue an update each week that includes all the trades, not just the ones that have payments for them.?
With the relationship diagram I have at present how can I include both types.

I am thinking a UNION clause here, but unable to get my head around what I would be looking to omit.

At present I have this query which produces all the records for one submitter

SELECT tblSVSTrades.TradeDate, tblSVSTrades.Forename, tblSVSTrades.Surname, tblSVSTrades.TradeType, tblSVSTrades.NetCost, tblSVSTrades.BuySell, tblSubmitter.SubmitterName, 0 AS IntroCommission, tblSVSTrades.SVSTradesID, tblSubmitterClient.SubmitterID
FROM tblSubmitter INNER JOIN ((tblClient INNER JOIN tblSubmitterClient ON tblClient.ClientID = tblSubmitterClient.ClientID) LEFT JOIN (tblCommission RIGHT JOIN tblSVSTrades ON tblCommission.TradeID = tblSVSTrades.SVSTradesID) ON tblClient.SVS_Account = tblSVSTrades.SVSAccount) ON tblSubmitter.SubmitterID = tblSubmitterClient.SubmitterID
WHERE (((tblSubmitterClient.SubmitterID)=5))
ORDER BY tblSVSTrades.SVSTradesID;

and this

SELECT tblSVSTrades.TradeDate, tblSVSTrades.Forename, tblSVSTrades.Surname, tblSVSTrades.TradeType, tblSVSTrades.NetCost, tblSVSTrades.BuySell, tblSubmitter.SubmitterName, tblIntroCommission.IntroCommission, tblIntroCommission.InvoicedDate, tblIntroCommission.PaidDate, tblSubmitter.SubmitterID, tblSVSTrades.SVSTradesID
FROM tblSubmitter INNER JOIN (tblSubmitterClient INNER JOIN ((tblCommission INNER JOIN tblIntroCommission ON tblCommission.CommissionID = tblIntroCommission.CommissionID) INNER JOIN tblSVSTrades ON tblCommission.TradeID = tblSVSTrades.SVSTradesID) ON tblSubmitterClient.SubmitterClientID = tblIntroCommission.SubmitterClientID) ON tblSubmitter.SubmitterID = tblSubmitterClient.SubmitterID
WHERE (((tblSubmitter.SubmitterID)=5));

which produces all the records that would have payments.

I have tblSVSTrades.SVSTradesID in common between them and need to execute this in VBA.

Could someone please explain how I might go about it?

TIA

Sub Export2XL(pstrType, plngSubmitterID)
On Error GoTo Err_Handler

Dim db As Database
Dim xlApp As Excel.Application
Dim xlWrkBk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim rstTrades As Recordset, rstIntro As Recordset
Dim strSQL As String, strSQLdate As String, strDBpath As String, strFolder As String, strPaymentsPath As String
Dim strSubmitterName As String, strEmail As String, strInvoiceFile As String, strSubject As String, strMessage As String, strSubjectEmail As String
Dim lSubmitterID As Long, lxlRow As Long
Dim strTestPrefix As String
Dim blnEmail As Boolean
Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

' Set for testing, remove when live
strTestPrefix = "Test DB "
blnEmail = TempVars("gbEmail")
' Set messages and subject depending on what has been passed
Select Case pstrType
    Case "Invoice"
        strSubject = strTestPrefix & "Invoice Request"
        strMessage = "Would you please supply an invoice for the attached transactions?"
    Case "Update"
        strSubject = strTestPrefix & "Trade Update"
        strMessage = "Please find attached your latest client trades update."
End Select

'strMessage = "Would you please supply an invoice for the attached transactions?"

Set db = CurrentDb()
strDBpath = GetDBPath

'Create new folder if it does not exist
strFolder = Format(Now(), "yyyy-mm-dd")
strPaymentsPath = strDBpath & pstrType & "\" & strFolder & "\"

' Test for path to save files, created each week.
If Dir(strPaymentsPath, vbDirectory) = "" Then
    MkDir strPaymentsPath
End If


'Open and reference an instance of the Excel app
Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False

' First get all the Submitters that can have an Invoice Run
' if plngSubmitterID is 0 then we want ALL
If plngSubmitterID = 0 Then
    strSQL = "SELECT tblSubmitter.InvoiceRun, tblSubmitter.SubmitterName, tblSubmitter.SubmitterID, tblSubmitter.Email FROM tblSubmitter"
    strSQL = strSQL & " WHERE (((tblSubmitter.InvoiceRun)=True))"
    strSQL = strSQL & " ORDER BY tblSubmitter.SubmitterName;"
Else
    strSQL = "SELECT tblSubmitter.InvoiceRun, tblSubmitter.SubmitterName, tblSubmitter.SubmitterID, tblSubmitter.Email FROM tblSubmitter"
    strSQL = strSQL & " WHERE (((tblSubmitter.InvoiceRun)=True) AND (tblSubmitter.SubmitterID = " & plngSubmitterID & "))"
End If


Set rstIntro = db.OpenRecordset(strSQL, dbOpenDynaset)

' Any submitter to process?
If rstIntro.EOF Then
    MsgBox "No Submitters found for " & pstrType & " Run"
    GoTo ExitSub
End If

' Set date in correct format for query
strSQLdate = Format(TempVars("Invoicedate"), strcJetDate)

rstIntro.MoveFirst

Do While Not rstIntro.EOF
    'Debug.Print rstIntro.Fields("SubmitterName")
    strSubmitterName = rstIntro.Fields("SubmitterName")
    ' need to add submitter name to subject so we can see it in Outlook list
    strSubjectEmail = strSubject & " - " & strSubmitterName
    strEmail = rstIntro.Fields("Email")
    strInvoiceFile = strPaymentsPath & strTestPrefix & strSubmitterName & " " & pstrType & ".xlsx"
    ' Set submitterID for query
    lSubmitterID = rstIntro.Fields("SubmitterID")
    ' SQL is different depending on Update or Invoice run
    If pstrType = "Invoice" Then
        strSQL = "SELECT tblSVSTrades.TradeDate, tblSVSTrades.Forename, tblSVSTrades.Surname, tblSVSTrades.TradeType, tblSVSTrades.NetCost, tblSVSTrades.BuySell, tblSubmitter.SubmitterName, tblIntroCommission.IntroCommission,tblIntroCommission.Invoiceddate,tblIntroCommission.PaidDate  FROM tblSubmitter"
        strSQL = strSQL & " INNER JOIN (tblSubmitterClient INNER JOIN ((tblCommission INNER JOIN tblIntroCommission ON tblCommission.CommissionID = tblIntroCommission.CommissionID) INNER JOIN tblSVSTrades ON tblCommission.TradeID = tblSVSTrades.SVSTradesID) ON tblSubmitterClient.SubmitterClientID = tblIntroCommission.SubmitterClientID) ON tblSubmitter.SubmitterID = tblSubmitterClient.SubmitterID"
        strSQL = strSQL & " WHERE (((tblIntroCommission.InvoicedDate) = " & strSQLdate & ")  AND ((tblSubmitterClient.SubmitterID)= " & lSubmitterID & "))"
        strSQL = strSQL & " ORDER BY tblSVSTrades.SVSTradesID;"
    Else
        strSQL = "SELECT tblSVSTrades.TradeDate, tblSVSTrades.Forename, tblSVSTrades.Surname, tblSVSTrades.TradeType, tblSVSTrades.NetCost, tblSVSTrades.BuySell, tblSubmitter.SubmitterName, tblIntroCommission.IntroCommission,tblIntroCommission.Invoiceddate,tblIntroCommission.PaidDate  FROM tblSubmitter"
        strSQL = strSQL & " INNER JOIN (tblSubmitterClient INNER JOIN ((tblCommission INNER JOIN tblIntroCommission ON tblCommission.CommissionID = tblIntroCommission.CommissionID) INNER JOIN tblSVSTrades ON tblCommission.TradeID = tblSVSTrades.SVSTradesID) ON tblSubmitterClient.SubmitterClientID = tblIntroCommission.SubmitterClientID) ON tblSubmitter.SubmitterID = tblSubmitterClient.SubmitterID"
        strSQL = strSQL & " WHERE ((tblSubmitterClient.SubmitterID)= " & lSubmitterID & ")"
        strSQL = strSQL & " ORDER BY tblSVSTrades.SVSTradesID;"
    End If


    Set rstTrades = db.OpenRecordset(strSQL, dbOpenDynaset)

    If Not (rstTrades.BOF And rstTrades.EOF) Then
        ' Open the Excel Template file
        Set xlWrkBk = xlApp.Workbooks.Open(strDBpath & "Introducer Export.xltx")
        'reference the first sheet in the file
        Set xlSht = xlWrkBk.Sheets(1)
        rstTrades.MoveFirst
        strSubmitterName = rstTrades.Fields("SubmitterName")
        'Update status bar with progress
        SetStatusBar ("Retrieving Invoice data for " & strSubmitterName)
    
        lxlRow = 3
        Do While Not rstTrades.EOF
            ' Now enter values in sheet
            xlSht.Cells(lxlRow, 1) = rstTrades.Fields("TradeDate")
            xlSht.Cells(lxlRow, 2) = rstTrades.Fields("Forename")
            xlSht.Cells(lxlRow, 3) = rstTrades.Fields("Surname")
            xlSht.Cells(lxlRow, 4) = rstTrades.Fields("TradeType")
            xlSht.Cells(lxlRow, 5) = rstTrades.Fields("NetCost")
            xlSht.Cells(lxlRow, 6) = rstTrades.Fields("BuySell")
            xlSht.Cells(lxlRow, 7) = rstTrades.Fields("SubmitterName")
            xlSht.Cells(lxlRow, 8) = rstTrades.Fields("IntroCommission")
            ' If in Update mode add relevant dates
            If pstrType = "Update" Then
                xlSht.Cells(lxlRow, 9) = rstTrades.Fields("InvoicedDate")
                xlSht.Cells(lxlRow, 10) = rstTrades.Fields("PaidDate")
            End If
            lxlRow = lxlRow + 1
            rstTrades.MoveNext
        Loop
        lxlRow = lxlRow + 2
        xlSht.Cells(lxlRow, 3) = "Date"
        xlSht.Cells(lxlRow, 4) = Date
        xlSht.Cells(lxlRow, 7) = "Total"
        xlSht.Cells(lxlRow, 8) = "=SUM(H3:H" & lxlRow - 2 & ")"
        SetStatusBar ("Saving Excel workbook " & strInvoiceFile)
        ' Now save the workbook
        xlWrkBk.SaveAs FileName:=strInvoiceFile
        xlWrkBk.Close
        'Now email the workbook to the Submitter if tempvars gbEmail is true
        If blnEmail Then
            Call Mail_Attachment(strEmail, strInvoiceFile, strSubjectEmail, strMessage)
        End If
    Else
        SetStatusBar ("No trades for " & strSubmitterName)
    End If
    ' Now close the recordset ready for the next
    rstTrades.Close
    ' Now get next record
    rstIntro.MoveNext
Loop
    

ExitSub:
    xlApp.DisplayAlerts = True
    Set db = Nothing
    Set rstIntro = Nothing
    Set rstTrades = Nothing
    Set xlSht = Nothing
    Set xlWrkBk = Nothing
    Set xlApp = Nothing
    SetStatusBar (" ")

    
Err_Exit:
    Exit Sub
    
Err_Handler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume ExitSub
End Sub

 

[Ranman256]

You can do a union, with and w/o.
Or
Use an outer join to gather both.

 

[plog]

I opened your relationship image and basically stopped there. You have some issues with your tables that you should address before you move on.

1. Circular Path. There should only be one way to traverse from 1 table to another in your relationships, you've created a loop with yours. Starting at tblClient you can move directly counterclockwise to tblSVSTrades or you can move through 3 other tables clockwise and arrive at tblSVSTrades. I don't know which path is the correct one, but you shouldn't have both.

2. Island Tables. tblPortfolio isn't technically included in your relationships, but I see both tblClient and tblTransfers have a PortfolioID--why is that? My guess is when you included tblPortfolio in your relationships it caused your data entry methods to fail. You have 2 other island tables--are they really unrelated to the rest?

3. Tables with only 1 real field of data. tblTradeType probably shouldn't exist. If you have only 1 real field of data (autonumbers don't count) then you should just store the real value (TradeType) in the external table (tblSVSTrades).

I really think that circular path is the cause of your query issue that you have posted about, but I haven't really dug into it to figure it out, because you should really fix that circular path. Most likely that changes the query you are working with.

 

[Gasman]

Hi Plog,

Thank you for looking at the relationships.
I'll try and address what I was attempting to achieve.

1. The data comes in via and Excel sheet and a client has various trades. Those trades are linked solely by the SVSAccount field. Not all the trades warrant commission, so I created the Commission table for those trades that do, and the subset of that is the IntroCommission table. This was created as there is now a requirement to split the payment between various parties, where previously it was one trade one person. This is one of the reasons I decide to move to Access as trying to implement this in Excel meant a lot of duplication of data (or so I believe).

As I have discovered with this post, going through those tables only gets me the trades on which there are payments on. That is great for Invoicing and works as I intended, as each party can have a different rate. Yet to test this as the situation is due to arise soon.

I have managed to get the data I need for an update (basically a list of all trades showing those paid and not paid) by using this code

        strSQL = "SELECT tblSVSTrades.TradeDate, tblSVSTrades.Forename, tblSVSTrades.Surname, tblSVSTrades.TradeType, tblSVSTrades.NetCost, tblSVSTrades.BuySell, tblSubmitter.SubmitterName, tblIntroCommission.IntroCommission, tblIntroCommission.InvoicedDate, tblIntroCommission.PaidDate, tblSVSTrades.SVSTradesID, tblSubmitter.SubmitterID"
        strSQL = strSQL & " FROM tblSubmitter INNER JOIN (tblSubmitterClient INNER JOIN ((tblCommission INNER JOIN tblIntroCommission ON tblCommission.CommissionID = tblIntroCommission.CommissionID) INNER JOIN tblSVSTrades ON tblCommission.TradeID = tblSVSTrades.SVSTradesID) ON tblSubmitterClient.SubmitterClientID = tblIntroCommission.SubmitterClientID) ON tblSubmitter.SubmitterID = tblSubmitterClient.SubmitterID"
        strSQL = strSQL & " WHERE ((tblSubmitterClient.SubmitterID)= " & lSubmitterID & ")"
        strSQL = strSQL & " UNION"
        strSQL = strSQL & " SELECT tblSVSTrades.TradeDate, tblSVSTrades.Forename, tblSVSTrades.Surname, tblSVSTrades.TradeType, tblSVSTrades.NetCost, tblSVSTrades.BuySell, tblSubmitter.SubmitterName, 0, '', '',tblSVSTrades.SVSTradesID, tblSubmitterClient.SubmitterID"
        strSQL = strSQL & " FROM tblSubmitter INNER JOIN ((tblClient INNER JOIN tblSubmitterClient ON tblClient.ClientID = tblSubmitterClient.ClientID) LEFT JOIN (tblCommission RIGHT JOIN tblSVSTrades ON tblCommission.TradeID = tblSVSTrades.SVSTradesID) ON tblClient.SVS_Account = tblSVSTrades.SVSAccount) ON tblSubmitter.SubmitterID = tblSubmitterClient.SubmitterID"
        strSQL = strSQL & " WHERE (((tblSubmitterClient.SubmitterID)= " & lSubmitterID & ") AND ((tblSVSTrades.SVSTradesID) NOT IN (SELECT SVSTradesID from qryUpdatePaid)))"
        strSQL = strSQL & " ORDER BY tblSVSTrades.SVSTradesID"

longwinded I know, but I could not see any other way at present.
So what I was trying to achieve with the Client to SVSTrades was show all trades, not just paid for trades. qryUpdatePaid is effectively the query before the union.
I hope I am making some sense here.

2. A client can have multiple transfers, but only one Portfolio type, so I think the ID should remain in the client table. The transfers are from a different sheet in the Excel workbook (a sort of summary sheet).
I have not created any forms for data entry. This system is really just to automate what was being done manually, then by Excel and now Access. Whilst there will be basic forms to view the data, other than forms for amending the control items (like Trade Type), there will not be many. I also took on board (I think it was you) that forms are the last thing to work on (though they may be sexy?)

3.The TradeType table is there so I do not have to hard code the different types, as whilst there are only two at present, that will change in the future, another reason for moving to Access? The Tradetype is in tblSVSTrades and is used to select the correct record as per tblTradeType

INSERT INTO tblCommission ( TradeID, Commission, DateAdded )
SELECT tblSVSTrades.SVSTradesID, [NetCost]*0.02 AS Commission, Date() AS Added
FROM tblTradeType INNER JOIN tblSVSTrades ON tblTradeType.TradeType = tblSVSTrades.TradeType
WHERE (((tblSVSTrades.BuySell)="buy") AND ((tblSVSTrades.TradeDate)>=[Trade Date]))
ORDER BY tblSVSTrades.SVSTradesID;

I believe now that I did not want Outer Joins, but inner joins?, as the tables are meant to link to each other (for the most part). This problem I have had today is the exception.

So for a Invoice I want the Trades info (tblTrades), the Client info (tblClients), Although some client info is on the SVSTrades table, this has bee incorrect in the past, but the SVS account remains correct and I want the IntroCommission info (tblIntroCommission via tblCommision)

If you could advise me to a better way to achieve this, I'm keen to learn, as at present I am not sure about Q1 and which path to remove?

Thank you for your input.

 

[Gasman]

You can do a union, with and w/o.
Or
Use an outer join to gather both.

Thank you Ranman256

I went with a union. I cannot seem to get my head around these outer joins. Will need to read up some more.