A
Ann
I am new to Access and am creating a database that tracks contract trades. I
have the following tables that I need info from
Trade Details (trade # (primary), trade date, buy price, sell price)
Client Info (account number (primary), name, address, etc)
In addition to these I also need a field for # of Contracts Traded in order
to perform the calculations I need but I am unsure how and where to integrate
this field. I need to create weekly account statements that I am able to
email to each client for each account with the following information:
All client table info
All trade table info
And I need to calculate the values for Pts P/L, Gross P/L, Bnk Comm, and Net
P/L which are based on the # of contracts traded (the field I am having
difficulty placing). I have the expressions to put in a query to correctly
return the results from the calculations. They are:
PtsP/L: IIf([Buy/Sell]="Sell",[Fill Price]-[Sell
Price],IIf([Buy/Sell]="Buy",[Sell Price]-[Fill Price]))
GrossP/L: CCur([Conts]*50*[PtsP/L])
BnkComm: CCur([Conts]*6.1)
NetP/L: CCur([GrossP/L]-[BnkComm])
My problem is this; some accounts trade a different number of contracts than
others. For example, some trade 50 and some trade 100. How do I create a
statement with the correct calculations for each individual account? The
trades are conducted daily, sometimes more than one trade in a day.
My database is brand new and does not have many tables or queries yet. I
want to know how I am going to approach this first before I implement
anything that I will have to correct later. Since I am new to this I am
looking for a detailed response including whether I need another table & with
what relationship, or if I should do this another way.
Thank you in advance for your help,
Ann
have the following tables that I need info from
Trade Details (trade # (primary), trade date, buy price, sell price)
Client Info (account number (primary), name, address, etc)
In addition to these I also need a field for # of Contracts Traded in order
to perform the calculations I need but I am unsure how and where to integrate
this field. I need to create weekly account statements that I am able to
email to each client for each account with the following information:
All client table info
All trade table info
And I need to calculate the values for Pts P/L, Gross P/L, Bnk Comm, and Net
P/L which are based on the # of contracts traded (the field I am having
difficulty placing). I have the expressions to put in a query to correctly
return the results from the calculations. They are:
PtsP/L: IIf([Buy/Sell]="Sell",[Fill Price]-[Sell
Price],IIf([Buy/Sell]="Buy",[Sell Price]-[Fill Price]))
GrossP/L: CCur([Conts]*50*[PtsP/L])
BnkComm: CCur([Conts]*6.1)
NetP/L: CCur([GrossP/L]-[BnkComm])
My problem is this; some accounts trade a different number of contracts than
others. For example, some trade 50 and some trade 100. How do I create a
statement with the correct calculations for each individual account? The
trades are conducted daily, sometimes more than one trade in a day.
My database is brand new and does not have many tables or queries yet. I
want to know how I am going to approach this first before I implement
anything that I will have to correct later. Since I am new to this I am
looking for a detailed response including whether I need another table & with
what relationship, or if I should do this another way.
Thank you in advance for your help,
Ann