Create Query for calculated column (1 Viewer)

yasmeen

Registered User.
Local time
Today, 07:19
Joined
Dec 19, 2010
Messages
30
Will you please help me to write a query for same database.

Query is: I have to add a calculated coulmun in my table called 'pips' and How it will calutate is as follows:
- I have pairs of trades in the table and subtraction of 'price' of pair is called pip of that row.

-We can use 'key' coulum to find a pair. 'Key' has this type of value: 244/1/-9934863, So each pair would have the first and last part of the value same. (for example if we have a pair, its values would be 244/1/-9934863 and 244/0/-9934863). These 0's and 1's denote for 'sell' and 'buy'.

- then Subtract the pair (large value - small value)
- then i have to figure out that what is the pair name from 'description' column. If its part of value contains 'JPY' then i have to multiply the subtracted value with 100 otherwise it will multiplied by 10,000.

It is very difficult for me, i don't know how to start it. I need these calculated pips in my table field, so that when i run my '1st dec' form, i will display the calculated pips in the subform with other coulmns.

Thanks


 

boblarson

Smeghead
Local time
Today, 07:19
Joined
Jan 12, 2001
Messages
32,059
Calculated fields like that should just be displayed using a query, not stored (as far as I can see). You don't need to use a table for your form, you can use a query. Also, I hope you aren't planning on using the same table for your main form and subform. That will only lead to pain and suffering.
 

yasmeen

Registered User.
Local time
Today, 07:19
Joined
Dec 19, 2010
Messages
30
ok, so i will combine this query with the query for my subform. For subform, i just need to select some columns from my table plus i need this calculated feild. But i don't know how to calculate as it is very complicated for me. Please help me.

In my subform, i need the following columns:
Action, Quantity, dates, submitter, symbol, pips(calculated column)

Thanks

Calculated fields like that should just be displayed using a query, not stored (as far as I can see). You don't need to use a table for your form, you can use a query. Also, I hope you aren't planning on using the same table for your main form and subform. That will only lead to pain and suffering.
 

yasmeen

Registered User.
Local time
Today, 07:19
Joined
Dec 19, 2010
Messages
30
I have only one table in my database. So i have to use the same table in my form and subform :mad:
 

boblarson

Smeghead
Local time
Today, 07:19
Joined
Jan 12, 2001
Messages
32,059
I have only one table in my database. So i have to use the same table in my form and subform :mad:

Then you have a design flaw. You either need a single table and a main form or you need to have more than one table and use a subform. If the fields are from the main table you don't need a subform on the main form and, in fact, it will cause you pain because you will get errors about you and another user trying to modify the same data even though it is you.

So, I don't understand why you would not just have the fields on the main form.
 

yasmeen

Registered User.
Local time
Today, 07:19
Joined
Dec 19, 2010
Messages
30
My form structure is as follows:

I have 3 combobox on main form which filters the results and displays in subform.
1st combo box: submitter
2nd combo box: starting date
3rd combo box: End date

I have a button to filter the subform. It works fine. In that subform, i need to calculate 'pips' now.

What should i need to change in my design now?

Then you have a design flaw. You either need a single table and a main form or you need to have more than one table and use a subform. If the fields are from the main table you don't need a subform on the main form and, in fact, it will cause you pain because you will get errors about you and another user trying to modify the same data even though it is you.

So, I don't understand why you would not just have the fields on the main form.
 

boblarson

Smeghead
Local time
Today, 07:19
Joined
Jan 12, 2001
Messages
32,059
I took a look at your other thread and see what you are doing. The main form doesn't actually have a recordsource so that is fine.

As for the calculation, I believe since it might be fairly long IIf statments, I would create a function to return the value. I'll help with that in a second but I need an answer to a question first. Which part do you consider the larger value and which the smaller. I would normally consider the 244 larger than the -9934863.
 

yasmeen

Registered User.
Local time
Today, 07:19
Joined
Dec 19, 2010
Messages
30
244/1/-9934863 is a value of 'key', we need to look at price column correspondence to the key column. Key is just to recoganise the pairs. (the two rows which will have 244/1/-9934863 and 244/0/-9934863 will make a pair.) In this way we will get 2 prices for each pair and we need to subtract those price values. (large price-small price)

Hope it will help you to understand my query.

Thanks
 

vbaInet

AWF VIP
Local time
Today, 14:19
Joined
Jan 22, 2010
Messages
26,374
This shouldn't be too difficult so I will explain the steps. I haven't had the time to test it but it should work:

You need a query for this.

1. Include only three fields - Key, Price and Description
2. Go to the SQL View of your and just after the word SELECT put DISTINCT. So it should read SELECT DISTINCT [Key], [Price], ... etc. Or if you look at the Property Sheet of the query, set the Unique Values property to Yes.
3. Filter out the the Buys, i.e. we only want to see the Sells. So under the criteria for the Key field you will put:
Code:
Mid([[COLOR=Red]Key[/COLOR]], nz(instr(1, [[COLOR=Red]Key[/COLOR]], "/"), 0) + 1, 1) = "0"
4. We need an alias field that will calculate the price difference, so put the following in a new column in the query:
Code:
PriceDiff: Abs([[COLOR=Red]TableName[/COLOR]].[[COLOR=Red]Price[/COLOR]] - (SELECT TOP 1 Q.[[COLOR=Red]Price[/COLOR]] FROM [[COLOR=Red]TableName[/COLOR]] AS Q WHERE Q.[[COLOR=Red]Key[/COLOR]] = mid([[COLOR=Red]TableName[/COLOR]].[[COLOR=Red]Key[/COLOR]], 1, nz(instr(1, [[COLOR=Red]TableName[/COLOR]].[[COLOR=Red]Key[/COLOR]], "/"), 0) - 1) & "/1/" & mid([[COLOR=Red]TableName[/COLOR]].[[COLOR=Red]Key[/COLOR]], nz(instr(1, [[COLOR=Red]TableName[/COLOR]].[[COLOR=Red]Key[/COLOR]], "/"), 0) + 3)))
5. Finally, to calculate the Pips, we also need another alias field. The following goes into a new column too:
Code:
CalcPips: IIF(Instr(1, [[COLOR=Red]Description[/COLOR]], "JPY") > 0, PriceDiff * 100, PriceDiff * 10000)
Obviously you need to change TableName to the name of the table and amend the field names too. I've highlighted exactly what you need to amend but don't remove the square brackets [ ].
 
Last edited:

yasmeen

Registered User.
Local time
Today, 07:19
Joined
Dec 19, 2010
Messages
30
Few Questions please:

  1. when i am adding code under key field creteria, it is showing that 'data type mismatch in createria expression'. Key feild's dayatype is 'text'.
  2. Same thing is happening when i write for 'Pricediff' creteria.
  3. 'Pricediff' is not in my table columns, so it is not allowing me to add this column in design of sql query, For that, i add 2 more columns into my table 'Pricediff' and 'CalcPips'. Is it a right way?
  4. If it will work, how i will attach this query with my subform filters?
Many Thanks

This shouldn't be too difficult so I will explain the steps. I haven't had the time to test it but it should work:

You need a query for this.

1. Include only three fields - Key, Price and Description
2. Go to the SQL View of your and just after the word SELECT put DISTINCT. So it should read SELECT DISTINCT [Key], [Price], ... etc. Or if you look at the Property Sheet of the query, set the Unique Values property to Yes.
3. Filter out the the Buys, i.e. we only want to see the Sells. So under the criteria for the Key field you will put:
Code:
Mid([[COLOR=Red]Key[/COLOR]], instr(1, [[COLOR=Red]Key[/COLOR]], "/") + 1, 1) = "0"
4. We need an alias field that will calculate the price difference, so put the following in a new column in the query:
Code:
PriceDiff: Abs([[COLOR=Red]TableName[/COLOR]].[[COLOR=Red]Price[/COLOR]] - (SELECT TOP 1 Q.[[COLOR=Red]Price[/COLOR]] FROM  [[COLOR=Red]TableName[/COLOR]] AS Q WHERE Q.[[COLOR=Red]Key[/COLOR]] = mid([[COLOR=Red]TableName[/COLOR]].[[COLOR=Red]Key[/COLOR]],1, instr(1, [[COLOR=Red]TableName[/COLOR]].[[COLOR=Red]Key[/COLOR]], "/") - 1) & "/1/" & mid([[COLOR=Red]TableName[/COLOR]].[[COLOR=Red]Key[/COLOR]], instr(1, [[COLOR=Red]TableName[/COLOR]].[[COLOR=Red]Key[/COLOR]], "/") + 3)))
5. Finally, to calculate the Pips, we also need another alias field. The following goes into a new column too:
Code:
CalcPips: IIF(Instr(1, [[COLOR=Red]Description[/COLOR]], "JPY") > 0, PriceDiff * 100, PriceDiff * 10000)
Obviously you need to change TableName to the name of the table and amend the field names too. I've highlighted exactly what you need to amend but don't remove the square brackets [ ].
 

vbaInet

AWF VIP
Local time
Today, 14:19
Joined
Jan 22, 2010
Messages
26,374
1. & 2. I have amended the code in my previous post so it should sort out the problem.
3. Nope. Don't create new fields. Delete those two fields and follow steps 4 and 5.
4. This is a different question. We can worry about that later.
 

yasmeen

Registered User.
Local time
Today, 07:19
Joined
Dec 19, 2010
Messages
30
when i insert the code for creteria of 'Pricediff' and 'calcpips', it is showing the following messages:

You Cannot set the creteria before you add a field or expression to the field row.
&
The expression you have entered has an invalid .(dot) or operator or invalid parentheses

Please tell me where is the problem.
 

vbaInet

AWF VIP
Local time
Today, 14:19
Joined
Jan 22, 2010
Messages
26,374
Let me see your PriceDiff statement and a screenshot of where you put it in the query.
 
Last edited:

yasmeen

Registered User.
Local time
Today, 07:19
Joined
Dec 19, 2010
Messages
30
Here i have attached the screen shot of my query

and here are the codes which i am adding as follows:

CalcPips: IIf(InStr(1,[Description],"JPY")>0,"Price Difference * 100","Price Difference * 10000")

PriceDiff:
Abs([1st dec].[Price]-(SELECT TOP 1 Q.[Price] FROM [1st dec] AS Q WHERE Q.[Key] = mid([1st dec].[Key],1, instr(1, [1st dec].[Key], "/") - 1) & "/1/" & mid([1st dec].[Key], instr(1, [1st dec].[Key], "/") + 3)))

Mid([Key],InStr(1,[Key],"/")+1,1)="0"


Thanks

Let me see your PriceDiff statement and a screenshot of where you put it in the query.
 

Attachments

  • code.jpg
    code.jpg
    65 KB · Views: 118

vbaInet

AWF VIP
Local time
Today, 14:19
Joined
Jan 22, 2010
Messages
26,374
I will mention two things:

1. The PriceDiff statement is not the same as the amended code in my post #9. There are missing Nz() functions. Copy that code (i.e. the code from post #9) and edit it with the right table names and field names.
2. You put the PriceDiff and CalcPips code in the wrong place. Put them on the same line with Description, so move it up. It is not criteria, it is an alias field (as I mentioned). If you don't know what an Alias field is always Google the term to get a better understanding.
 

yasmeen

Registered User.
Local time
Today, 07:19
Joined
Dec 19, 2010
Messages
30
Hi,

I have changed what you have suggested me

Here is my new query for PriceDiff:

PriceDiff: Abs([1st dec].[Price]-(SELECT TOP 1 Q.[Price] FROM [1st dec] AS Q WHERE Q.[Key] = mid([1st dec].[Key],1, instr(1, [1st dec].[Key], "/") - 1) & "/1/" & mid([1st dec].[Key], nz(instr(1, [1st dec].[Key], "/")) + 3)))

and it is working but when i run the query, there are no results.

Creatia for key is : Mid([Key],nz(InStr(1,[Key],"/"),0)+1,1)

When i remove the createia then i get some results.

I am here attaching the screenshots of my query design view and result page withouyt key creteria.

Please suggest whats goin wrong

Thanks
 

Attachments

  • access.JPG
    access.JPG
    56.5 KB · Views: 78
  • access1.JPG
    access1.JPG
    65.5 KB · Views: 73

vbaInet

AWF VIP
Local time
Today, 14:19
Joined
Jan 22, 2010
Messages
26,374
yasmeen, it's really not hard to copy and paste code and make a few changes. The code I gave you was:
Code:
Mid([Key], nz(instr(1, [Key], "/"), 0) + 1, 1)[COLOR=Red][B] = "0"[/B][/COLOR]
Note the highlighted bit.

But you have:
Creatia for key is : Mid([Key],nz(InStr(1,[Key],"/"),0)+1,1)
 

yasmeen

Registered User.
Local time
Today, 07:19
Joined
Dec 19, 2010
Messages
30
Hi,

I am very sorry to trouble you with my silly mistakes.

I got the result of that query now. Will you please assist me further on this? It would be very helpful

As i am very new to MS Access and i don't know much :(

Thanks for your help
 

vbaInet

AWF VIP
Local time
Today, 14:19
Joined
Jan 22, 2010
Messages
26,374
For new questions, simply post a new thread and someone should be able to advise you.
 

yasmeen

Registered User.
Local time
Today, 07:19
Joined
Dec 19, 2010
Messages
30
But this is a connected question, I have fit this query with my subform coulms. I have to explain the whole thing to other person again.
 

Users who are viewing this thread

Top Bottom