By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,146 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Calculation Query

P: 21
TGIF & Good morning:

Can someone please tell me why my calculation query isn't working. I will post the SQL below for your review. Thanks in advance for your help.

Expand|Select|Wrap|Line Numbers
  1. SELECT [Market Value Table 12-05 to 12-06].[Market Value 6/30/06],
  2.        [Fee Table].[Basis Points],
  3.        [MarketValue6/30/06]*[BasisPoints] AS FeeAmount
  4. FROM [Market Value Table 12-05 to 12-06],
  5.      [Fee Table];
Mar 2 '07 #1
Share this Question
Share on Google+
9 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT [Market Value Table 12-05 to 12-06].[Market Value 6/30/06], [Fee Table].[Basis Points], [MarketValue6/30/06]*[BasisPoints] AS FeeAmount
  3. FROM [Market Value Table 12-05 to 12-06], [Fee Table];
There is no JOIN between these two tables. What is the relationship between them?

Mary
Mar 2 '07 #2

P: 21
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT [Market Value Table 12-05 to 12-06].[Market Value 6/30/06], [Fee Table].[Basis Points], [MarketValue6/30/06]*[BasisPoints] AS FeeAmount
  3. FROM [Market Value Table 12-05 to 12-06], [Fee Table];
There is no JOIN between these two tables. What is the relationship between them?

Mary
OK-I created a relationship between them-the Trust Identifier. now I will try again-let you know. Thanks
Mar 2 '07 #3

P: 21
OK-I created a relationship between them-the Trust Identifier. now I will try again-let you know. Thanks
OK-Still not working for me. SQL posted below


SELECT [Trust Table].[Trust Identifier], [Trust Table].[Trust Account Number], [Trust Table].[Trust Nickname], [Market Value Table 12-05 to 12-06].[Market Value 6/30/06], [Fee Table].[Basis Points], [MarketValue6/30/06]*[BasisPoints]=[SUM] AS [Fee Amount]
FROM ([Market Value Table 12-05 to 12-06] INNER JOIN [Trust Table] ON [Market Value Table 12-05 to 12-06].[Trust Identifier] = [Trust Table].[Trust Identifier]) INNER JOIN [Fee Table] ON ([Trust Table].[Trust Identifier] = [Fee Table].[Trust Identifier]) AND ([Market Value Table 12-05 to 12-06].[Trust Identifier] = [Fee Table].[Trust Identifier])
WHERE ((([Trust Table].Relationship)="M Family"));
Mar 2 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT [Trust Table].[Trust Identifier], 
  2. [Trust Table].[Trust Account Number], 
  3. [Trust Table].[Trust Nickname], 
  4. [Market Value Table 12-05 to 12-06].[Market Value 6/30/06], 
  5. [Fee Table].[Basis Points], 
  6. [MarketValue6/30/06]*[BasisPoints] AS [Fee Amount]
  7. FROM ([Market Value Table 12-05 to 12-06] INNER JOIN [Trust Table] 
  8. ON [Market Value Table 12-05 to 12-06].[Trust Identifier] = [Trust Table].[Trust Identifier]) 
  9. INNER JOIN [Fee Table] 
  10. ON ([Trust Table].[Trust Identifier] = [Fee Table].[Trust Identifier]) 
  11. WHERE ((([Trust Table].Relationship)="M Family"));
Mary
Mar 2 '07 #5

P: 21
Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT [Trust Table].[Trust Identifier], 
  2. [Trust Table].[Trust Account Number], 
  3. [Trust Table].[Trust Nickname], 
  4. [Market Value Table 12-05 to 12-06].[Market Value 6/30/06], 
  5. [Fee Table].[Basis Points], 
  6. [MarketValue6/30/06]*[BasisPoints] AS [Fee Amount]
  7. FROM ([Market Value Table 12-05 to 12-06] INNER JOIN [Trust Table] 
  8. ON [Market Value Table 12-05 to 12-06].[Trust Identifier] = [Trust Table].[Trust Identifier]) 
  9. INNER JOIN [Fee Table] 
  10. ON ([Trust Table].[Trust Identifier] = [Fee Table].[Trust Identifier]) 
  11. WHERE ((([Trust Table].Relationship)="M Family"));
Mary
Hi Mary and thanks for your help but that code still didn't input any data into the Fee amount fields. It isn't generating anything for me. Thanks for helping me with this.
Mar 2 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Mary and thanks for your help but that code still didn't input any data into the Fee amount fields. It isn't generating anything for me. Thanks for helping me with this.
What is being stored in [MarketValue6/30/06] and in [BasisPoints] and what is the datatype of both these fields?
Mar 2 '07 #7

P: 21
What is being stored in [MarketValue6/30/06] and in [BasisPoints] and what is the datatype of both these fields?
OK-I figured it out-my equation wasn't right-I still needed to divide some numbers. Thanks for your help. Have a great weekend.
Mar 2 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
OK-I figured it out-my equation wasn't right-I still needed to divide some numbers. Thanks for your help. Have a great weekend.
No problem, you too.

Mary
Mar 2 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
Well, I'm glad you got that one sorted out Mary ;)
Mar 5 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.