Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 12-20-2010, 02:11 PM   #1
yasmeen
Newly Registered User
 
Join Date: Dec 2010
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
yasmeen is on a distinguished road
Create Query for calculated column

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



yasmeen is offline   Reply With Quote
Old 12-20-2010, 02:19 PM   #2
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Create Query for calculated column

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 12-20-2010, 02:35 PM   #3
yasmeen
Newly Registered User
 
Join Date: Dec 2010
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
yasmeen is on a distinguished road
Re: Create Query for calculated column

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

Quote:
Originally Posted by boblarson View Post
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 is offline   Reply With Quote
Old 12-20-2010, 02:38 PM   #4
yasmeen
Newly Registered User
 
Join Date: Dec 2010
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
yasmeen is on a distinguished road
Re: Create Query for calculated column

I have only one table in my database. So i have to use the same table in my form and subform
yasmeen is offline   Reply With Quote
Old 12-20-2010, 02:58 PM   #5
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Create Query for calculated column

Quote:
Originally Posted by yasmeen View Post
I have only one table in my database. So i have to use the same table in my form and subform
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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 12-20-2010, 03:14 PM   #6
yasmeen
Newly Registered User
 
Join Date: Dec 2010
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
yasmeen is on a distinguished road
Re: Create Query for calculated column

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?

Quote:
Originally Posted by boblarson View Post
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 is offline   Reply With Quote
Old 12-20-2010, 03:20 PM   #7
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Create Query for calculated column

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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 12-20-2010, 03:37 PM   #8
yasmeen
Newly Registered User
 
Join Date: Dec 2010
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
yasmeen is on a distinguished road
Re: Create Query for calculated column

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
yasmeen is offline   Reply With Quote
Old 12-20-2010, 08:49 PM   #9
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Create Query for calculated column

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([Key], nz(instr(1, [Key], "/"), 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([TableName].[Price] - (SELECT TOP 1 Q.[Price] FROM [TableName] AS Q WHERE Q.[Key] = mid([TableName].[Key], 1, nz(instr(1, [TableName].[Key], "/"), 0) - 1) & "/1/" & mid([TableName].[Key], nz(instr(1, [TableName].[Key], "/"), 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, [Description], "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 by vbaInet; 12-21-2010 at 06:52 PM. Reason: amended code
vbaInet is offline   Reply With Quote
Old 12-21-2010, 01:48 PM   #10
yasmeen
Newly Registered User
 
Join Date: Dec 2010
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
yasmeen is on a distinguished road
Re: Create Query for calculated column

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

Quote:
Originally Posted by vbaInet View Post
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([Key], instr(1, [Key], "/") + 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([TableName].[Price] - (SELECT TOP 1 Q.[Price] FROM  [TableName] AS Q WHERE Q.[Key] = mid([TableName].[Key],1, instr(1, [TableName].[Key], "/") - 1) & "/1/" & mid([TableName].[Key], instr(1, [TableName].[Key], "/") + 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, [Description], "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 [ ].
yasmeen is offline   Reply With Quote
Old 12-21-2010, 06:52 PM   #11
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Create Query for calculated column

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.
vbaInet is offline   Reply With Quote
Old 12-22-2010, 07:11 PM   #12
yasmeen
Newly Registered User
 
Join Date: Dec 2010
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
yasmeen is on a distinguished road
Re: Create Query for calculated column

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.
yasmeen is offline   Reply With Quote
Old 12-22-2010, 07:23 PM   #13
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Create Query for calculated column

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

Last edited by vbaInet; 12-22-2010 at 07:41 PM.
vbaInet is offline   Reply With Quote
Old 12-23-2010, 02:14 PM   #14
yasmeen
Newly Registered User
 
Join Date: Dec 2010
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
yasmeen is on a distinguished road
Re: Create Query for calculated column

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

Quote:
Originally Posted by vbaInet View Post
Let me see your PriceDiff statement and a screenshot of where you put it in the query.
Attached Images
File Type: jpg code.jpg (65.0 KB, 86 views)
yasmeen is offline   Reply With Quote
Old 12-23-2010, 06:27 PM   #15
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Create Query for calculated column

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.

vbaInet is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query to create a new column for each week interval April15Hater Queries 10 11-11-2008 07:01 AM
Create 9.99 column with SQL query inoxo Modules & VBA 2 01-22-2008 12:26 AM
create a new row for each table column in query Eyor Queries 11 11-01-2006 08:55 AM
create calc column in query based on criteria from another column mdbBound Queries 2 03-05-2004 08:07 AM
Code to create query with calculated fields Mechele Modules & VBA 1 04-30-2002 07:00 AM




All times are GMT -8. The time now is 06:59 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World