Help me use VBA to solve it (1 Viewer)

[hoatranh]

I have 1 table ratio, and I want to create a new table as YearlyRatio. The formula as: X=A-b; Y=A+B; Z=A+C. How can you help me! I don't know how to use VBA or query to do it! thank you so much.

 

[cardgunner]

I have 1 table ratio, and I want to create a new table as YearlyRatio. The formula as: X=A-b; Y=A+B; Z=A+C. How can you help me! I don't know how to use VBA or query to do it! thank you so much.

You already have table Yearly ratio?

Have you ever done a query for anything? What level do I need to explain?

Is there a relationship between the two tables? I'm guessing it year.

 

[hoatranh]

Oh, sorry, I wrong.... I say again: I only have 1 table - that is YearlyRatio table. I want to use VBA or query to create a new table as Ratio, and the new Ratio table have 3 record X,Y,Y follow every year - The formula as: X=A-b; Y=A+B; Z=A+C. I send Ratio table only for example, I can't create Ratio table from YearlyRatio table. I think VBA can do it, but I don't know how to calculate every record in every field. Thanks so much!!!

 

[cardgunner]

Well I don't think you need to go to the extent of creating some elaborate code.

Again what is your experience with creating a query?

 

[cardgunner]

Where did you go?

You asked a question and then left while I'm trying to help.

Well In any case here is a answer.

Create a query

SELECT YearlyRatio.CODE, YearlyRatio.year, YearlyRatio.value1
FROM YearlyRatio
WHERE (((YearlyRatio.CODE)="A"));

Save that as qry_A.
Change the criteria to "B" and save that as qry_B
Change the criteria to "C" and save that as qry_C

Now you have three queries qry_A, qry_B, qry_C.

Now you will need to create a union query and then a make table query.

I have created both in one SQL statement

SELECT qry_union.Common, qry_union.Value, qry_union.year 
INTO Ratio
FROM
(
select *
from 
(SELECT "X" AS Common, [qry_A]![value1]-[qry_B]![value1] AS [Value], qry_A.year
FROM (qry_A INNER JOIN qry_B ON qry_A.year = qry_B.year) INNER JOIN qry_C ON qry_B.year = qry_C.year)  qry_X

union all

select *
from
(SELECT "Y" AS Common, [qry_A]![value1]+[qry_B]![value1] AS [Value], qry_A.year
FROM (qry_A INNER JOIN qry_B ON qry_A.year = qry_B.year) INNER JOIN qry_C ON qry_B.year = qry_C.year)  qry_Y

UNION ALL select *
from
(SELECT "Z" AS Common, [qry_A]![value1]+[qry_C]![value1] AS [Value], qry_A.year
FROM (qry_A INNER JOIN qry_B ON qry_A.year = qry_B.year) INNER JOIN qry_C ON qry_B.year = qry_C.year)  qry_Z
)qry_union

If you take that and insert it into a new query while in SQL View it should work perfectly.

It did for me.

Hope it helps.

 

[hoatranh]

oh Thank you veryvery much!!! I had do it! That is the part of the project about finance, I must do many ratio as X,Y,Z.... I can use Excel to do it, but it is not professional and vary slow. Thank you so much! When I finish, May I sent my project to you. May I know your mail? my: hongkhanhng@yahoo.com