Some Noob Questions about the Foundations

[Antwoord]

Hello,

Firstly apologies for the long question and all the details, hopefully you can help!

I am building a Access database to record currency trading deals and report on key financial indicators such as:

• The cost of investment in base currency (USD) [The nature of the activity is that trading is performed in "Pairs", mostly we use a USD pair since Trading a YENEURO pair for example gets complicated for tracking cost and profitability.}
• The amount of each currency held eg. €1800 with it's historic cost in the currency used to purchase it and the base currency
• Profit/(loss) on sale of a currency (using a average (aka "pooled") cost , each sale must be attributed with a cost to calculate profit/loss - the Sales proceeds are just the sales value, the cost should be calculated by using the Average cost per unit at the time for sale ( total cost of the currency held in all accounts/the grand total of all the currency held) eg Grand total cost of GBP held= $13,800/£9750.99 = Average cost for profit calculation= $1.41524091399
• Reconciliation schedule simply to compare the totals for each currency held per the transactions listing versus the statement balances ( for each account)

Following the process of Data Normalization I have created these tables:

1. Accounts (for the different exchange and bank type accounts, we use about 9 different accounts) Primary Key=Autonumber
2. Currencies ( this is simply a list of all the various currencies traded) Primary Key=Autonumber
3. FilledOrders ( this records the final results of an order, fields used are Extended date, order reference (being the account's internal reference) Primary Key=Autonumber
4. FilledOrderItems
   1. (this table records the details of an order each record will use these fields: Account (lookup combobox from tbl1)/Currency (lookup combobox from tbl2) / Amount (decimal number with 12 decimal places)/ Type of transaction(lookup from tbl5) Primary Key=Autonumber / foreign key=Lookup from FilledOrders Primary Key.
   2. There will be at least two records for each filled order because every transaction results in at least two filled order items eg Item 1 bought Euros and Item 2 Sold USD. In addition there may be an extra Filled order item for charges too - to make things complicated the charges vary a lot in these ways, sometimes they charge by a deduction of the currency purchased, sometimes they charge a specific currency, the rates vary at different times and on different accounts too! The only way to deal with them accurately would be to input them which would be time consuming but luckily the charges tend to be immaterial so I can ignore them mostly ( also we have changed account settings so that from June the deal values are left alone and charges are instead made against a currency balance making the audit trail easier. In summary charges are not too material and can be ignored if they create too much work)
5. FilledOrderItemType ( this table categorises the type of item for example "Trade" "Charges", "45 day deposit" "90 day deposit" "Inter Account Transfer" , "reconciliation Adjustment" "Suspense" , "Lost" , "Interest". The Primary Key=Autonumber
6. CurrenciesMarketvalue ( I haven't worked this table out yet but it will be used to input the latest price of each currency in USD, say at month-end and therefore help us calculate the Unrealised Gain or Loss on currency held The Primary Key? Perhaps instead of a separate table this should just be an additional field in tbl 2? However in time the data points needed for "the latest market view" will expand to include many extra values like "all time high" , "30 day high" "standard deviation" etc so I thought it might be best to have a separate table for Market data ready for this growth.

The main reason for the way I have set this up is due to my limited understanding of data normalization plus for easy calculations I wanted a single value field in table 4 and not the traditional "Debit and Credit" fields used in accounting.

Questions:

• Do you think I am on the right path here?
• So far limited understanding of data input for Access means I am troubled by having the all important transaction entry spread across multiple tables - I can no longer simply go into table view and enter a single record, what are my options for data entry ?( so far about 400 transactions which I can do manually no problems and check against the actual account balances, but I am concerned about inputting errors going forward) However can I create a form that will populate the Filled order table and enter the multiple filled order items at the same time? Or perhaps cleaning the data up in Excel would be better and importing it ( but again each filled order has at least 2 filledorderitem records so am not sure if that would work?
• I understand that as a relational database I need to connect tables together. However about the manner to do so I am not sure I have this right, I am doing this using a Lookup field in the second table "foreign key" to use a key from the first table, for example, the "all important" FilledOrderitems table (tbl4) uses a lookup field of the Primary key field from the FilledOrders table - that way every order should be linked to the order items it is related too? However I am not clear how does this "foreign key" populate into the FilledOrderitems table (tbl4) ? It's not like Excel where I would use a formula and a manual input via cut/paste or combobox seems a duplication of input effort and risk of error? Perhaps to enter a transaction I should use a form with subform?
  • form to enter the Filledorder table data
  • and a Subform to enter the (multiple) FilledOrderItems so in a single input event the necessary data is entered - is this the best way to do this?
  • Or perhaps all the data for the two tables can be entered in a single form?

I feel ( but could be wrong!) that the reporting I need would be possible with this set up; the types of questions I need to answer are

1. How much of each currency do we hold on each account and does it agree to what the account statement says we hold? (a reconciliation report Orders data versus Account balances)
2. How much did the currency we hold cost us? and How much is it worth according to the latest exchange rates (tbl 6) (portfolio valuation report - " a moment in time")
3. What is our trading profit for All time for all currencies? (All time All curencies trading report)
4. What is our trading profit for a specified time period for all currencies? (Periodic all currencies trading report)
5. What is our trading profit for All time for a selected currency? (All time single currency trading report)
6. What is our trading profit for a specified time period for a selected currency? (Periodic trading single currency trading report)

Once again apologies for all the detail, please do feel free to make any comments or suggestions at all, it might be blindingly obvious to you but I am a beginner I appreciate any and all your help and insight!

Thank you!

 

[The_Doc_Man]

First, you are correct to lay out designs and identify design questions ahead of time including reporting goals as well as table details. No fault at all to have details laid out. In fact, given the likely involvement of various international regulations regarding monetary exchange, you probably need even more detail than this. So are you on the right track? I don't know, I'm a chemist and have no experience with exchange rates. But you are on the right track to really investigate your requirements and approach.

Second, your concern regarding data entry PERHAPS ignores or glosses over the power of Access forms. If you have enough data for your forms to SHOW you your accounts, you have enough power already to enter data from minimal input - let Access put it in, since you are assuming it can already get it out. That is, the problem is more or less symmetric for input vs. output abilities. At least, in broad-brush terms.

Specific question: "can I create a form that will populate the Filled order table and enter the multiple filled order items at the same time?"

Well, in nit-picking technical terms, no if the order item table is a properly constructed child of the filled order table. Not exactly at the same time. But ... you can populate the parent table first and immediately thereafter populate the child table elements. Access doesn't like it if you create orphans, so to create new entries, you do parents then children. (And to delete the entries, don't leave orphans, so delete children then parents.) That can all be done "behind the scenes" under the form because there is no reason you have to make multiple controls to add things piecemeal. One "save" button can do sequences - i.e. more than one thing behind the scenes, and in fact that is the basis for one of my favorite bits of advice. Be like Julius Caesar. Divide and conquer. Break up your actions into simpler parts and just do them in sequence. When you have broken down the parts enough that you feel comfortable with each one, you are good to proceed.

Your concerns about table 4 (the order item details) simply suggests you aren't comfortable yet with your own idea. That might be because of incomplete normalization or simply lack of confidence in your chosen solution. In either case, I sense this phrase: "I don't see it yet." There, I have no way to answer a question for you, just advise you to think.

I have a couple of rules for you to consider:

1. If you can't do it on paper, you can't do it in Access. Meaning... if you do your data layout and try to hand-compute whatever it is you want, if you cannot do that using paper and pencil, you might not understand the problem well enough (including "haven't analyzed it enough") to try to do any coding. It will be a morass of retrofitting and reworking and revisiting. If you lay out a design on paper, it becomes your road map. If you are on a journey without a road map, how will you EVER know you have arrived?

2. Access can't tell you anything you didn't tell it first. Meaning... if your analysis tells you that you want to see X, Y, and Z (or XYZ) in the output, you need to verify that you had sources of X, Y, and Z (and maybe the formula to convert them to XYZ). If this sometimes means going backwards through your design to verify data availability, so be it. Remember that Access ONLY knows how to make database infrastructure. It has no ability for subject-matter issues. Access is dumber than a box of rocks when it comes to inventory, class attendance records, pigeon breeding records, or tracking library books. You know how to do that. You have to "teach" Access what you want it to do by giving it code, formulas, macro sequences, queries, etc. to accomplish your goal. If you don't tell Access how to do X, it won't do X. That simple. Use the road map (see #1 above) to help you work your way backwards to validate your data sources.

 

[CJ_London]

probably easier for us to see your actual db rather than working from descriptions, but a few comments

FilledOrderitems table (tbl4) uses a lookup field of the Primary key field from the FilledOrders table

it is poor practise to use lookup fields in tables. Similarly don't do any formatting of table fields. The principle reason it hides the actual data. Just because you can, does not make it a good idea. For example currency fields have 4 decimal places, format it to show only 2 dp and you will not see the final 2 digits which will be included in any calculations you may do.

not the traditional "Debit and Credit" fields used in accounting.

that's fine. I'm an accountant and although I may display debit/credit in separate columns in a report, I store the values in a single field. I might use positive/negative to indicate whether a debit or credit, but more usually a transaction type (looks like your table FilledOrderItemType) - that way I can easily reverse a transaction if required.

CurrenciesMarketvalue ( I haven't worked this table out yet but it will be used to input the latest price of each currency in USD, say at month-end and therefore help us calculate the Unrealised Gain or Loss on currency held The Primary Key? Perhaps instead of a separate table this should just be an additional field in tbl 2? However in time the data points needed for "the latest market view" will expand to include many extra values like "all time high" , "30 day high" "standard deviation" etc so I thought it might be best to have a separate table for Market data ready for this growth.

keep it in a separate table

tblCurrencyValues
CurrencyValuePK
CurrencyFK
OnDateT - might include a time element?
CurValue

not sure about extra values - if these need to be calculated to populate the table then just calculate on the fly when required. If they are coming already calculated from another source then include an 'extra value' table and include the FK in this table. Note, if you are doing trades, you might want to populate this table with the date and value of the trade as well (in which case perhaps include a tradeFK field as well, can be left blank when not related to a trade so you won't be able to maintain referential integrity)

 

[Antwoord]

@The_Doc_Man
Thanks for your helpful comments, it's good to know I am on the right path -
I can confirm that I believe that I have access to all the data that should be needed for this exercise, some of the reporting on these exchanges can lack a helpful audit trail but with time I will be able to put it together. Of course I have been working with numbers long enough to know that they will normally be wrong on the first few cuts ( at least) hence the need for reconciliation procedures to the statement balances and other checks.

To clarify this statement "you can populate the parent table first and immediately thereafter populate the child table elements"
If I understand correctly I should create a single Access form for entry of data into filledorders and filledorderitems with the various fields needed (from the different tables) to enter an order and the order items but have a single "Save" button on the form that saves the Parent table fields (FilledOrders) first and then immediately saves the child table fields (FilledOrderItems) to the naked eye it appears to happen simultaneously but in reality it happens sequentially, automatically from one click very fast. Have I interpreted that correctly?

You are quite right about the financial accounting treatments and international financial reporting standards, not straightforward and quite variable, as such they are beyond the scope of the programmed features of this project for me at this time, we use that knowledge to help us know what to do when and when the need arises make any necessary adjustments manually.

Appreciate the general rules that you laid out, a long engrained habit from Excel is building in checks, audit trail and recs, to ensure data integrity and completeness I will be applying all that here too.

 

[Antwoord]

@CJ_London
Thanks for your help, if anyone was willing to look at the file that would be amazing, I will progress it as best as I can before doing that so as to not waste anyone's time (or patience!)

Some questions on a few points you made:

1. "it is poor practise to use lookup fields in tables" what you are saying makes good sense to me. Does this mean in the table just to set the foreign key field to the correct type to hold the Parent table's primary key and instead force the user to select a primary key at the form level only using a combobox control on the form object only? ( if so the admin/user must be careful to not use the database table for data entry due to the risk of creating an orphan entry) - am I understanding this correctly?
2. I like your suggestion of using a separate transaction type table to cover debit/credit while also maintaining a single value field and separate column formats for reporting - sounds like the best of both worlds, is there any downside to consider other than an extra click for entering the order item?
3. tblCurrencyValues - initially these will be sourced externally and hopefully be imported, however in time I would expect to add our own metrics, as you suggested I think these would be best calculated on the fly in the reports or forms they would be used in. Absolutely agree the data for this table should be time stamped. In terms of integrating this data with other data from a decision support at a later date I think we would definitely want to do that, lots of useful information could be processed

Thanks again

 

[CJ_London]

Does this mean in the table just to set the foreign key field to the correct type to hold the Parent table's primary key and instead force the user to select a primary key at the form level only using a combobox control on the form object only?

Correct!

if so the admin/user must be careful to not use the database table for data entry due to the risk of creating an orphan entry

no one should be using the tables or queries directly, only through forms. You can prevent users from creating orphan entries by setting the rule in relationships - having created a relationship, double click on it and select 'enforce referential integrity'. Note that the relationships view looks a bit like the query view - but the joining lines represent different things.

I should create a single Access form for entry of data into filledorders and filledorderitems with the various fields needed (from the different tables)

you would have two forms, one for each table - a main form and a subform. The recordsource to your main form (single view) would be filledorders and the subform (continous view) filledorderitems. If you have created your relationships then the 'link' between the two is created automatically in the subform linkmaster/child properties. Otherwise you will need to complete these themselves. General rule is one form, one table. Combining tables in a form recordsource will frequently make the form unupdateable.

is there any downside to consider other than an extra click for entering the order item?

not from my perspective - all handled in the form. My thinking is if data is obtained using manual input, keep it all positive (users can forget to include a -). If it is primarily imported/copy pasted, keep in the format of the source data. Note: in your transaction type table have a 'multiplier' field populated with either 1 or -1 so in a query you would have Amount:Qty*Multiplier. I sometimes include a 0 as well which would be for headings in a list

Absolutely agree the data for this table should be time stamped.

easy to find a current value for any given day - in a basic query something like

SELECT Currency, max(price) as curPrice
FROM tblCurrencyValues
WHERE OnDateT<=[Enter Date]

 

[Antwoord]

@CJ_London
awesome thank you for the assistance, like your thinking about polarity of data entry it's a common area for errors. Working on v3!

 

[The_Doc_Man]

@The_Doc_Man
Thanks for your helpful comments, it's good to know I am on the right path -
I can confirm that I believe that I have access to all the data that should be needed for this exercise, some of the reporting on these exchanges can lack a helpful audit trail but with time I will be able to put it together. Of course I have been working with numbers long enough to know that they will normally be wrong on the first few cuts ( at least) hence the need for reconciliation procedures to the statement balances and other checks.

To clarify this statement "you can populate the parent table first and immediately thereafter populate the child table elements"
If I understand correctly I should create a single Access form for entry of data into filledorders and filledorderitems with the various fields needed (from the different tables) to enter an order and the order items but have a single "Save" button on the form that saves the Parent table fields (FilledOrders) first and then immediately saves the child table fields (FilledOrderItems) to the naked eye it appears to happen simultaneously but in reality it happens sequentially, automatically from one click very fast. Have I interpreted that correctly?

You are quite right about the financial accounting treatments and international financial reporting standards, not straightforward and quite variable, as such they are beyond the scope of the programmed features of this project for me at this time, we use that knowledge to help us know what to do when and when the need arises make any necessary adjustments manually.

Appreciate the general rules that you laid out, a long engrained habit from Excel is building in checks, audit trail and recs, to ensure data integrity and completeness I will be applying all that here too.

You have interpreted the comments about populating the tables in the way I intended. However, there is no guarantee that my guess exactly conforms to your data layout. The more important fact to remember is that forms with a little bit of VBA code in hand can do a sequence of many events, many steps, in any order you need. Limited only by your imagination and understanding.

Note that if you develop tables that are related to each other (immediate example is your order and order items), you can define a one-to-many relationship and advise Access to enforce Relational Integrity. That would then build in an Access-level safeguard to avoid creating orphans. The parent-child relationship is also sometimes seen as an independent/dependent relationship, in that an order item depends on having an order with which to be associated. You can also use field properties in the field definition grid on a table so that you can build in constraints that you don't have to enforce, because Access will mind that for you, too.

You mentioned Audit Trails. There is a pot-load to be had on that subject using the forum's Search function (upper right, has a magnifying glass icon). Once you have read a few of those articles, I will leave it to you to decide for yourself what KIND of pot is carrying what kind of load.

 

[Pat Hartman]

The others have given you lots of good suggestions so I don't want to interfere. I just wanted to comment on this to ease your mind a little

I am troubled by having the all important transaction entry spread across multiple tables

There are different classes of tables in every application. Some tables are "masters". I.e. they define the possible values of something such as currency types, accounts, currency values, etc. Mostly you add new records and only occasionally change existing ones. Then you have "transaction" tables. These tables describe actions like Orders and Order items. The update forms for transaction tables always contain lots of combos to pick values from the master tables but they never update the master tables. They do however require that the data they depend on already exist in the "master" tables. Tables like the currency values tend to require history because they essentially change every value every day and you frequently need to analyze them over time.

 

[CJ_London]

I would add as an example a sales invoice - how many tables do you think are required?

An invoice header table and an invoice line table certainly.

But unless you are only selling one product or service, you are going to need a product or service table referenced by each invoice line. And products and services may have multiple prices based on the type of customer and/or quantity or price changes over time, so that is another 2 or 3 tables.

Looking the other way at the customer - unless you only sell to a customer once and never again, you are going to need a table for customers. And perhaps they have multiple addresses for invoicing, multiple delivery addresses etc. So that is another table. Maybe your customer has multiple contacts, so you need a table for customer contacts. And perhaps those contacts have multiple methods to contact them (phone/cell/web/fax/whatsapp etc) so potentially another table there.

So even a 'simple' sales invoice might need 9 tables to meet the real world processes of the business.

The more you can break down your data logically into smaller tables your app will be more efficient and more flexible to meet future needs. Don't forget tables are about data storage and manipulation, not about presentation.

 

[Pat Hartman]

And finally, we talk a lot about normalization where we put the data in separate tables so that it occurs only once but we don't talk about how we use that data once it is all "normalized" Tables represent raw data but queries - where we join tables back together represent information.

So, once you normalize your spreadsheet correctly, you should be able to create a query that recreates the original spreadsheet by joining the tables back together again. You don't ever lose information. You just might have to use a query with a join to see it in context.

 

[Antwoord]

@Pat Hartman Thanks Pat that does make it clearer and it fits with my understanding of tables being generally either of an "object" "event" or "join" type, I think I was guilty of some "Excel think" with regards foreign keys being "automatically" entered into child tables in the way an Excel formula might, but now as I understand it that's not the case and the foreign key must be manually entered into the child table, or better practice, using a form and a sub form it can "appear" to be done simultaneously when creating the parent record with a single save click . ( or I guess some clever vba might be able to copy/paste the parent table primary key into the (continuous) subform field for the foreign key to save input error and effort, before running the save form data/save sub form data) Is my understanding correct?

 

[Pat Hartman]

As long as you set the master/child links property of the subform control, Access will automagically populate the FK of the subform to sync with the PK of the main form. People get confused when they use popup forms because in their minds they ae still a "subform" but not so. Therefore, you need to set the FK yourself. In this case, I recommend the BeforeInsert event of the popup form to avoid the problems caused by your code dirtying the record and to ensure that if multiple records are added, ALL of them get the correct FK.
Me.ParentID = Forms!frmParent!ParentID

 

[Antwoord]

The more you can break down your data logically into smaller tables your app will be more efficient and more flexible to meet future needs. Don't forget tables are about data storage and manipulation, not about presentation.

@CJ_London thanks yes this makes perfect sense, I am too weak right now on how to robustly join related data back up. I believe I am in far better place with normalization now I will try to focus on queries, forms and reports before testing with some dummy data, exciting times!!

 

[mike60smart]

If you relate the table correctly and Join the Parent Key from your Main (Parent Table) to the Foreign Key in your Child table and Enforce Referential Integrity, the Foreign Key will populate automatically.

 

[gemma-the-husky]

I would have to say that financial applications need to be robust, resilient, and correct. This sounds like it is likely to be a very challenging project to get right. Sorry if this response appears unduly negative.

I assume you are a financial services firm managing investments, but if I have the wrong end of the stick, please correct me.

Assuming you are a financial organisation, then I really think you ought to be be prepared to pay for assistance where needed, and not rely on assistance from unpaid volunteers, such as this site, and other similar sites. I assume you are developing this yourself. If so, if you are unsure how to analyse the data, and structure your tables at the very outset, then I really do think you either need professional help to assist you in the project, or professional developers to build the whole thing. Hence the caveats being expressed in many of these responses.

In many cases it will be cheaper and easier to buy a ready made package that gives you the facilities you need. If you have to go your own way, and develop something yourself, it may cost more, you will have to support it yourself, you will have to build in any financial regulation controls you need, and it will probably not be as polished as a commercial package would be. On the other hand, you would be able to include specific functionality that you need, and maybe isn't built in to existing commercial packages. That's the trade-off for bespoke development.

In general terms, you started with the outputs, and that's the wrong place to start in my opinion. You should start with the data. If the data is right, the outputs will flow naturally. The outputs may just guide your thought process.

eg. If each transaction records the date, the amount, the people/agencies involved, the client, the currencies involved, the currency translation rate used, maybe commission rates, and anything else, you will automatically be able to generate the outputs you need. If you can't generate the output, then it will be because the data analysis is imperfect. You mentioned specifics like "paired" transactions. I don't understand that, but your data analysis/table design needs to respect it, and then you will get the outputs you want.

I hope you find this helpful.

 

[Antwoord]

@gemma-the-husky

Thanks for your insight, this a tool for my own use only and not for a financial services firm managing investments, I don't think Access would be the tool for that.
Since its just for my own use, I have only a limited budget which is why I'm interested in learning more about access to do as much as I can and also build other personal tools only.

I understand building a commercial app is much larger proposition and that is not what I am interested in, you are correct there are free and paid commercial products that are far slicker, which are more robust and with proper controls to do some of what I want (and more) they deliver on the essential reporting and can do so efficiently with automatic data imports from many sources to support the larger volumes of transactions used by businesses, however they just don't do everything I want so this tool is for my personal use only to provide the extra information that I want in the way I want if that makes sense.

Completely understand your comments on the base data, I already did my checking on that issue and decided on any necessary treatments to make the task a reasonable amount of work yet still materially correct. An example of this would be "order date" is not transaction date, in fact sometimes an order is often filled in a few minutes but sometimes it might get filled in dozens of smaller transactions over more than one day, week or months so the available data needs to used at the transaction level if the highest level of accuracy is needed. I am happy that everything that is needed is there and I can handle the base data in an appropriate manner for the "materially accurate" level that I need.

My reason for focussing on outputs is I just read elsewhere that defining the outputs in reports was a good approach to design of an Access DB but I am just learning so I have already found I needed to check and double check the base data in the course of defining outputs and checking test data, hopefully I will get better at that!

So my situation is I'm confident of the base data and the required outputs, the part in the middle is what Access provides and the mechanics of how to do that is what I am working on, obviously as noob it's not quite a black box but there are a lot of blanks to fill in, I can see that other forum users, even more advanced users who use Access professionally at work are also in need of insight from helpful experts too, so I have no doubt that I have a lot to learn but I am sure that I will be able to achieve what I want without unduly burdening forum members.

The concept of "pairs" is "the price" a "trading pair" is simply a term to describe the value in foreign exchange , securities or trading, eg. USDGBP, USDYEN, AAPLUSD are trading pairs.

thanks again for sharing your thoughts

 

[gemma-the-husky]

Thanks for your insight, this a tool for my own use only and not for a financial services firm managing investments, I don't think Access would be the tool for that.

Personally, I think the only thing that Access can't really do is give you total security over data. For data handling, it's not much different to other solutions. Maybe even quicker. Anyway your additional notes make it a lot clearer.