New Db building stuff
-
- BronzeLounger
- Posts: 1407
- Joined: 08 Jul 2016, 18:53
Re: New Db building stuff
If I delete the field I won't be able to see the field! Obviously from my posts you must realize that I want to see the field, filled out. Maybe that is the best way to describe my issues I want to see what you refer as "redundant" data. I'm not concerned with data bloat.
I do realize your training says it isn't necessary, it is a desire.
Let put it another way, you know nothing of the fields except you can see what they contain wouldn't it be easier to read each record? Remember you don't know that 46 = Buy to Open, but could deduce it if the data were sturdied.
I do realize your training says it isn't necessary, it is a desire.
Let put it another way, you know nothing of the fields except you can see what they contain wouldn't it be easier to read each record? Remember you don't know that 46 = Buy to Open, but could deduce it if the data were sturdied.
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: New Db building stuff
Users can see the description in the form. They should never view the table directly.
And you can create a query based on the Trades and TradeNum table:
You can use this query as Record Source for a report, for example.
And you can create a query based on the Trades and TradeNum table:
You can use this query as Record Source for a report, for example.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1407
- Joined: 08 Jul 2016, 18:53
Re: New Db building stuff
I could make a query on the table and you would not know what the actions are, you would have to look at another table, the image is a query not the table. But I digress and further discussions are pointless I want the field to be in the table, you don't feel it is necessary and I am unable to get a positive result so I'm dropping the issue and non-resolved in my mind.
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: New Db building stuff
You do NOT have to look at another table separately.
You can use the query as if it is the Trades table, and see both the ActionID and its description without having to store it twice in the Trades table.
The form I proposed also displays both the ActionID and its description without needing two fields in the Trades table.
So there are only advantages, without any downside.
You can use the query as if it is the Trades table, and see both the ActionID and its description without having to store it twice in the Trades table.
The form I proposed also displays both the ActionID and its description without needing two fields in the Trades table.
So there are only advantages, without any downside.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1407
- Joined: 08 Jul 2016, 18:53
Re: New Db building stuff
Attempting to build an event
results in an error. What is the correct syntax?
Code: Select all
Private Sub Form_DataChange(ByVal Reason As Long)
Me.[Fees].OnChange
if me.(left,3[Symbol])="MES" then
End Sub
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: New Db building stuff
This is probably not an event you want to use - it applies only to a form in PivotTable view.
What exactly do you want to do?
What exactly do you want to do?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1407
- Joined: 08 Jul 2016, 18:53
Re: New Db building stuff
Attempting to calculate a number but there are several tree possibilities thus the if statement triggered by entering/changing the fees field.
Long/short
Open/close
symbol 3 choices now but that may change with time.
I thought that event with a change would be appropriate, but if not then what?
Long/short
Open/close
symbol 3 choices now but that may change with time.
I thought that event with a change would be appropriate, but if not then what?
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: New Db building stuff
Which field do you want to change to which values on which conditions?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1407
- Joined: 08 Jul 2016, 18:53
Re: New Db building stuff
It will be in the form of [price] * constant(depende on symbol) * [quantity] + [commissions] = [fees] = [amount]
If it is a closing trade then the [amount] -previous [amount]=[profit]
If it is a closing trade then the [amount] -previous [amount]=[profit]
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: New Db building stuff
I don't think you need VBA for that - a formula (calculated column) would probably be sufficient
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1407
- Joined: 08 Jul 2016, 18:53
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: New Db building stuff
Option 1: in a query based on the table; use the query as Record Source for forms and reports.
Fees: [price]*DLookup("constant", "lookuptable", "symbol=' & [symbol] & "'")*[quantity]+[commissions]
Option 2: in the Control Source of a text box named Fees on a form or report.
=[price]*DLookup("constant", "lookuptable", "symbol=' & [symbol] & "'")*[quantity]+[commissions]
Fees: [price]*DLookup("constant", "lookuptable", "symbol=' & [symbol] & "'")*[quantity]+[commissions]
Option 2: in the Control Source of a text box named Fees on a form or report.
=[price]*DLookup("constant", "lookuptable", "symbol=' & [symbol] & "'")*[quantity]+[commissions]
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1407
- Joined: 08 Jul 2016, 18:53
Re: New Db building stuff
It seems like the second option would be better. So, a new table with symbols and constant. In that table filed one symbol and field two constant, I assume.
Suppose I call the table tblTickValue, then the formula part DLookup([Multiplier],[tblTickValue],"MES")?
Suppose I call the table tblTickValue, then the formula part DLookup([Multiplier],[tblTickValue],"MES")?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: New Db building stuff
You're almost there. It would be
DLookup("Multiplier","tblTickValue","Symbol='MES'")
but since you want it to be variable
DLookup("Multiplier","tblTickValue","Symbol='" & [Symbol] & "'")
DLookup("Multiplier","tblTickValue","Symbol='MES'")
but since you want it to be variable
DLookup("Multiplier","tblTickValue","Symbol='" & [Symbol] & "'")
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1407
- Joined: 08 Jul 2016, 18:53
Re: New Db building stuff
I don't think that "Symbol='" & [Symbol] & "'") will work, but I will try that, shouldn't there be an"'" on the inner parentheses?
Never mind when I posted the quote, I see you used "'".
Does there need to be an "=" at the beginning of all of the formula, in the control source?
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: New Db building stuff
> Does there need to be an "=" at the beginning of all of the formula, in the control source?
Yes.
Yes.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1407
- Joined: 08 Jul 2016, 18:53
Re: New Db building stuff
Acid test tomorrow, thanks
-
- BronzeLounger
- Posts: 1407
- Joined: 08 Jul 2016, 18:53
Re: New Db building stuff
not correct
All buys should be negative, all sells should be positive. Form the image all values are negative.
I changed the formula by eliminating the minus sign and all values are positive. This is a logic issue and I don't see where I'm wrong, can anyone?
Code: Select all
=-[Quantity]*[Price]*DLookUp("Multiplier","tblTickValue","Symbol='" & [Symbol] & "'")*[Quantity]+[Commission]+[Fees]
All buys should be negative, all sells should be positive. Form the image all values are negative.
I changed the formula by eliminating the minus sign and all values are positive. This is a logic issue and I don't see where I'm wrong, can anyone?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1407
- Joined: 08 Jul 2016, 18:53
Re: New Db building stuff
Thanks the formula worked in Excell. And I thought I entered it correctly, but alas I put quantity in twice.