435,377 Members | 3,038 Online
Need help? Post your question and get tips & solutions from a community of 435,377 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 SELECT [Market Value Table 12-05 to 12-06].[Market Value 6/30/06],        [Fee Table].[Basis Points],        [MarketValue6/30/06]*[BasisPoints] AS FeeAmount FROM [Market Value Table 12-05 to 12-06],      [Fee Table]; Mar 2 '07 #1
9 Replies

 Expert Mod 10K+ P: 14,534 Expand|Select|Wrap|Line Numbers   SELECT [Market Value Table 12-05 to 12-06].[Market Value 6/30/06], [Fee Table].[Basis Points], [MarketValue6/30/06]*[BasisPoints] AS FeeAmount 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   SELECT [Market Value Table 12-05 to 12-06].[Market Value 6/30/06], [Fee Table].[Basis Points], [MarketValue6/30/06]*[BasisPoints] AS FeeAmount 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

 Expert Mod 10K+ P: 14,534 Try this ... Expand|Select|Wrap|Line Numbers 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] 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])  WHERE ((([Trust Table].Relationship)="M Family")); Mary Mar 2 '07 #5

 P: 21 Try this ... Expand|Select|Wrap|Line Numbers 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] 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])  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

 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

 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

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