Can't get my head around this

[Gasman]

Hi all,

I have created a small Access system to generate a few excel sheets.
I was quite happy with the way it was progressing and it even showed me a few errors in my current Excel processing of the data.

The relationship diagram is attached. I know it is not perfect, but I hope I am getting there.

Now currently it only needs to produce two sheets. One for which I call Invoice and another for Updates.

Updates are details of all trades for the respective company and include those that earn commission and those that do not.
As not every trade earns commission, I only create commission records for those that do.
I also had the requirement to have to split the commission between individuals in the company and this was becoming unwieldy in Excel and that is why I decide to try in Access.

My SQL for Updates is

        strSQL = "SELECT tblSVSTrades.TradeDate, tblClient.Forename, tblClient.Surname, tblSVSTrades.TradeType, tblSVSTrades.NetCost,  tblSVSTrades.BuySell, tblSubmitter.SubmitterName,tblIntroCommission.IntroCommission, tblIntroCommission.InvoicedDate, tblIntroCommission.PaidDate FROM (tblCommission"
        strSQL = strSQL & " RIGHT JOIN ((tblClient INNER JOIN (tblSubmitter INNER JOIN tblSubmitterClient ON tblSubmitter.SubmitterID = tblSubmitterClient.SubmitterID) ON tblClient.ClientID = tblSubmitterClient.ClientID)"
        strSQL = strSQL & " INNER JOIN tblSVSTrades ON tblClient.SVS_Account = tblSVSTrades.SVSAccount) ON tblCommission.TradeID = tblSVSTrades.SVSTradesID) LEFT JOIN tblIntroCommission ON tblCommission.CommissionID = tblIntroCommission.CommissionID"
        strSQL = strSQL & " WHERE (((tblSubmitter.SubmitterID)=" & lngSubmitterID & " ))"
        strSQL = strSQL & " ORDER BY tblSVSTrades.SVSTradesID"

This works fine for when there is only one submitter for a company. It produces both those records that have commissions attached and those that do not.

However when I apply it to a company that has more than 1, in this case 2 submitters, it doubles up the records that have commissions. I dare say it the company had three, it would triple the number. If I put a further check in for SubmitterClientId, I get only the correct records, but miss out on those records that do not have commission attached.

I *think I need a union somewhere, but not sure on how to code it.?

I'd appreciate some pointers on how to overcome this.

I'm fine (I think) with the Invoicing part as that looks for specific dates and of course only includes records that have commission, but notice I do have the submitterclientid in the string as well.

        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)= " & lngSubmitterID & "))"
        strSQL = strSQL & " ORDER BY tblSVSTrades.SVSTradesID;"

TIA

 

[plog]

I really think you need to nail down the structure before writing any SQL. The immediate big issue I see is a circular path. There should only be one way to trace a path between tables, you have created a loop. I can get from tblClient to tblIntroCommission by going clockwise through tblSubmitterClient or counter-clockwise via tblSysTrades\tblCommission. This is not correct.

Unfortunately, I don't know what is, that's up to you--you know your business rules better than us. My advice for this is to start from scratch with your relationships. First, identify your main table. Usually it is the one that will see the most new records added. My gues is that would be tblSysTrades. Add that to your relationships, then one by one add your other tables. Upon each addition, make sure the table you are adding is in fact related to the table you are relating it to. If you a table creates a loop, it is not related correctly.

 

[plog]

As I look closer at your fields I'm seeing other issues (some of these are educated guesses) :

1. Unformalized multiple paths. You have the big one I identified in my first response, but I see what are effectively others that you haven't created. For example, tblTransfers has a PortfolioId field, which in effect makes a circular relationship between tblClient, tblPortfolio and tblTransfers. tblTransfers has a SYSAccount field which would bring in tblSysTrades into that spiderweb.

2. Calculated fields. I see a TotalCost field in tblSysTrades, that sounds like a calculated field, which shouldn't be in a table, but instead calculated in a query.

3. Redundant data. tblSysTrades is indirectly related to tblIntroCommission and they both have InvoicedDate and PaidDate fields. Will those be the same values? Actually, I'm seeing PaidDate and InvoicedDate fields in a lot of tables.

3.1. BuySell field seems redundant. You have a tblTradeType to define what types of trades you have, why do you need a BuySell field? Wouldn't/Couldn't the TradeType tell you that?

3.2. Forename/Surname stored in both tblSysTrades and tblClient. Why is that?

 

[Gasman]

Good morning Plog,
1. The SVSAccount is what the investment firm (IF) use, and we use in our Google Docs workbook.
2. TotalCost is in the trades sheet and is supplied by IF.
3. I hope my explanation below will explain the use of these. Basically I am trying to emulate the structure where a price of an item may change in an order line, so the price is fixed in the order line, not linked to the price as it might be later. I dare say I could use links to date, but would need to be shown how.?
3.1 The buysell again is supplied by the IF, but I need that as only buys earn commission and then only on certain trade types. I suppose the situation could occur where a stock is sold for a client on the same day it is bought for another client, depending on their portfolio?
3.2 Again import data from the IF. I would like to keep that as is, as it is my 'safety blanket' if I ever get the links wrong. With those I can reconstruct my values.

Now the background.

I'll try and explain the process as I created it with an Excel workbook. That is pretty much what I am trying to achieve, but with the benefits of Access and links. I know it is always said to forget your Excel head, but in my defence I have tried to create the workbook using what little db concepts I know.
Every week I get a sheet of trades. I copy in the last weeks worth into a similar structured sheet in my workbook, I say similar as my sheet has extra columns for commission amount, introducer commission amount, date invoiced, date paid, a key to identify trade and stock and a few formulae to allow for filtering.
Each trade is identified by the account number of the client, but also their name.
In another sheet called Lookup I have a table that holds the account number, forename, surname and the introducer for that client. To get the introducer I have to look in our computer system and update manually. That will remain the same in the new system.
Also in this sheet I have a table for the Introducers and their commission rates.
Another table holds the stocks traded, the date of trade, type of stock, and commission rate, date invoiced, date payment received. This also has a compound key of date & stock name & "buy" that is used to identify the rows in the trades sheet.
Another sheet holds details of the transfer amount and the type of portfolio the client has chosen. This also can then monitor % traded via formula. Most of the data on this sheet is formula (a type of dashboard sheet)
Some time after the trades we invoice for the commission and receive payment. Formula in the sheet identifies which trades these are and using these dates in the Received column I can filter out and have vba to create and email a sheet to each individual Introducer.
Each week regardless of payments, I send each introducer an 'Update' which is basically all the trades for their clients. However it identifies when they have been invoiced and paid and also advises them of what will be coming in the future.
That is pretty much it in a nutshell.

Now in my DB I was trying to emulate most of that processing.
So I have the table SVSTrades. i'll apologise now as Commission, Invoiced date and Paid Date are a leftover from importing the table from my workbook. Those fields are no longer populated, but were left there so I could compare my DB generated values with those already given and discover any errors. Effectively the table stops at buysell. This table mirrors the structure of the weekly sheet.
 
What I have achieved so far is
I import last weeks trades from the workbook sent into SVSTrades.
I then update the StockTraded table with new stocks and dates.
I then manually update (for now) the stock with the correct rate.
Then VBA runs which applies the rate to the amounts correctly creating Commission and IntroCommission records.
Now for the most part there is only one submitter per Introducer. if that is the case, what I have now works fine. However another reason I went to Access is that having more than one was making the Excel process unwieldy. Currently I copy those records to another sheet (the same structure as Trades sheet) and run my macroes on that sheet after duplicating and reassigning the rows to correct submitter.
For that reason I created the Client - ClientSubmitter - Submitter - Introducer structure. That works in as commission is split correctly as per rate for each submitter and trade in respect to the client.
Transfers is to come. For now I am just updating this table in parallel with my Excel sheet.
For new clients I add them to the client table, assign them a submitter(s) so as to get correct rates.

Castle is a special situation where I have to keep track of advance payments and apply to invoices. That again is working fine.

I have really tried to get the structure right for the process I have explained and have only now started creating forms for maintenence. A you have stated it is not perfect, so any advice you can give, I'd appreciate. One of the hardest things I had to do was getting the data from the excel workbook into the structure I have now.
If I have to recreate the DB, I believe I have the basics now for moving tables to the new DB almost as is?
I have had a lot of problems when trying to create queries with ambiguous joins.

Sorry for the lengthy reply, but I wanted to try and give you the whole picture in one go, rather than you suggest something or have to ask and we go back and forth like that. or at least minimise that.
TIA

 

[plog]

Thanks for the explanation. My general advice still stands--get your structure correct then start building on top of it (reports, queries, forms). I too believe you have 90% of the table structure already there, now its a matter of relating everything properly.

You need to start from scratch in your Relationship Tool and rebuild it table by table. When you come to something that either creates a loop or doesn't seem right, you need to thoroughly think about how that table relates and if it in fact holds the correct fields. Perhaps moving certain fields of a table will help it relate better.