Calculation Query

[jalmar]

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.

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];

[MMcCarthy]

 
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

[jalmar]

 
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

[jalmar]

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"));

[MMcCarthy]

Try this ...

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

[jalmar]

Try this ...

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.

[MMcCarthy]

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?

[jalmar]

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.

[MMcCarthy]

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

[NeoPa]

Well, I'm glad you got that one sorted out Mary ;)