Help With Vba Pleaseeeee

[EL-g]

Hey People, im kinda giving up hope on ACCESS. Ive been killing myself tring to figure out a few thing for several weeks and still havent came to any conclusions. This is a desperate call for HELP. SO PLEASE HELP ME OUT GUYS, cuz im almost loosing hope here.


Ok Guys so here it goes : I have a table called Indices with the following fields:

Month, DRS, 3-Month Treasury, Lehman Bros. Agg. Bd Index, Lehman Bros. 1-3Y US Treas., Lehman Bros. 7-10Y US Treas., Lehman Bros. 20+Y US Treas, JPMorgan EMBI+, SP 500 Index.

First I need to calculate the BAlancedAllocation which has this formula: =(0.05*[3-Month Treasury]+0.45*[Lehman Bros. Agg. Bd Index]+0.5*[SP 500 Index])

Then I needed to make a table with the formula: =IF(Field<0;1;0)

And After that make a table with this rows in the left with the formulas in the right:

Currency = USD
Period From= Min Date
Period To = Max date
T.A.R =PRODUCT(1+field)^(12/COUNT(field))-1
S.D. =STDEVP(field)*SQRT(12)

And all the fields as colums!

Should i use VB or SQL? Ive been tring to make it by SQL for several weeks and havent gotten good resolts. If you guy could help me out writing a VBA for it id be more than glad.

 

[namliam]

First off you really shouldnt use spaces dots and dashes in your column names... Not all databases support that...

By "=IF(Field<0;1;0)" I am guessing you mean that formula??

I think it would be verry doable using a query... but your information (to me) is a bit scetchy ...

 

[EL-g]

Well
The thing is that i have to make this excel table in access. I attached both files here:
http://www.access-programmers.co.uk/forums/showthread.php?t=111006

And im confused wheter to make it with sql or vba.

And by the IF thing i mean the formula.

Thanks a lot for the help!!!

 

[namliam]

ColumnName: (0.05*[3-Month Treasury]+0.45*[Lehman Bros. Agg. Bd Index]+0.5*[SP 500 Index])

You can use that in a query to calculate your formula....

same you can do for the IF thing, tho I seem to miss the logic of the IF....
ANotherColumnName: IF((0.05*[3-Month Treasury]+0.45*[Lehman Bros. Agg. Bd Index]+0.5*[SP 500 Index])<0;1;0)

 

[EL-g]

ColumnName: (0.05*[3-Month Treasury]+0.45*[Lehman Bros. Agg. Bd Index]+0.5*[SP 500 Index])

You can use that in a query to calculate your formula....

same you can do for the IF thing, tho I seem to miss the logic of the IF....
ANotherColumnName: IF((0.05*[3-Month Treasury]+0.45*[Lehman Bros. Agg. Bd Index]+0.5*[SP 500 Index])<0;1;0)

Well the thing is that i need this IF formula to the whole table. Not only to balanced allocation.

Thanks for the help

 

[namliam]

A little more than 3 words please? I dont understand...

The balanced allocation is not per record?

 

[EL-g]

Ok. It works like this:

I have an excel file that has a main table. Basically this table has 9 columns and 39 row. What i need to do from this table is:

Calculate an index called BalancedAllocation with the following excel formula: =(0.05*O5+0.45*P5+0.5*V5).
Then I need to calculate a binary table with zeros and ones that follow this logical formula:=IF(N4<0;1;0).

After calculationg this binary for all 39 rows and 9 columns I have to make another binary with 39 rows and 2 columns. 1st column has this forumla: =IF(V4>=0;1;0) and the 2nd has this one =ABS(1-X4)

After that i have to calculate a table with many complicated formulas.

The BalancedAllocation then is first a logical formula that i did like this with sql and worked out:

SELECT Indices.Month, Sum(Indices![3-Month Treasury]*0.05+Indices![Lehman Bros Agg Bd Index]*0.45+Indices![SP 500 Index]*0.5) AS BalancedAllocation
FROM Indices
GROUP BY Indices.Month;

After that i calculate the resoult of it in the binary query. The thing is in the binary query im getting 1521 binary numbers instead of 39. RV told me to join them, but the JOIN thinggy is olny to join info from 2 dif tables, and i have only one table.

 

[workmad3]

are you sure that you really need to move to access? It sounds like what you are doing is suited to excel and you will be creating a large workload for yourself moving it over to access.

Just a though

 

[EL-g]

I kinda thought that. But the thing is that in excel the procedure on updating all the stuff and making the forms is very suitable for many mistakes. And i have many reports linked to that excel that i posted. I can work pretty good in excel, but i thought that if i did in access i could automataize more the procedures and diminish the room for errors. So my access would work like a database for all the calculations and the output for all the reports, and i would have less risk of making mistakes in updating my excel files. Unless you have suggestions of other programs i could use. Because there is one report that i make taht i have to update more than 20 sheets of excel. ANd in the end there are a lot of data that i update that are almost the same thing. So in access i would save a lot of trouble, and make it with less mistakes.

But so far its being hard to make the excel formulas in access. That is why im desperate for help

 

[namliam]

can you post a good sample sheet? Cause I am still not following....

 

[EL-g]

can you post a good sample sheet? Cause I am still not following....

Ok, no prob.

In this link ive upped the excel and access file: http://www.access-programmers.co.uk/forums/showthread.php?t=111002

By the way thanks a lot for the help

Ill try to reformulate the question to see if it helps:

Well, I have a database table that "in terms of excel" has 9 columns and 39 rows.

1st I needed to create one form that would allow me to include new data to the tabel. I made it ok. So far so good.

The part of queries is a bit more complicated. I needed first to calculate an index called BalancedAllocation with the excel formula that follows:
= (0.05*coluna1+0.45*coluna2+ 0.5*coluna3).

I managed to make this formula in SQL as code below:

SELECT Indices.Month, Sum(Indices![3-Month Treasury]*0.05+Indices![Lehman Bros Agg Bd Index]*0.45+Indices![SP 500 Index]*0.5) BalancedAllocation FROM Indices GROUP BY Indices.Month;

Then I had to make a table that would give me a binary code for all the columns in the table and the created BalancedAllocation using a logical formula if:

= IF(colunax = 0,1,0)) That I Used this SQL code:

SELECT Indices.Month, IIf(Indices!DRS<0,1,0) AS DRS
FROM Indices
GROUP BY Indices.Month;

The thing is that when i make this formula column by column it works fine, but I i try to make all columns in one query i get a resoult of 39*39=1521 numbers instead of only 39*10. I used the SQL statement that follows:

SELECT Indices.Month, IIf(Indices!DRS<0,1,0) AS DRS, IIf(Indices![3-Month Treasury]<0,1,0) AS [3-Month Treasury], IIf(Indices![Lehman Bros Agg Bd Index]<0,1,0) AS [Lehman Bros Agg Bd Index], IIf(Indices![Lehman Bros 1-3Y US Treas]<0,1,0) AS [Lehman Bros 1-3Y US Treas], IIf(Indices![Lehman Bros 7-10Y US Treas]<0,1,0) AS [Lehman Bros 7-10Y US Treas], IIf(Indices![Lehman Bros 20+Y US Treas]<0,1,0) AS [Lehman Bros 20+Y US Treas], IIf(Indices![JPMorgan EMBI+]<0,1,0) AS [JPMorgan EMBI+], IIf(Indices![SP 500 Index]<0,1,0) AS [SP 500 Index], IIf(Balance_Allocation!BalancedAllocation<0,1,0) AS BalancedAllocation
FROM Indices, Balance_Allocation
GROUP BY Indices.Month, DRS, [3-Month Treasury], [Lehman Bros Agg Bd Index], [Lehman Bros 1-3Y US Treas], [Lehman Bros 7-10Y US Treas], [Lehman Bros 20+Y US Treas], [JPMorgan EMBI+], [SP 500 Index], BalancedAllocation;

Then i Had to create 2 other queries that were also binary and used this SQL formulas:

SELECT Indices.Month, Sum(IIf(Indices![SP 500 Index]>=0,1,0)) AS BullDJWA
FROM Indices
GROUP BY Indices.Month;

SELECT Indices.Month, Sum(Abs(Bull_DJWA!BullDJWA-1)) AS BearDJWA
FROM Bull_DJWA
GROUP BY Indices.Month;

You will notice that the second query is calculated after the first, but the thing is that i wanted to calculate both in the same query.

Then I had to find the max and min for the dates which i did with no troble using the SQL statement that follows:

SELECT Max(Indices.Month) The MaxOfMonth, Min(Indices.Month) MinOfMonth FROM Indices;

Then i had to create a very complicated table with lots of excel formulas. The 1st one is this one here:

= PRODUCT(1+O4:O42)^(12/COUNT(O4:O42))-1

As far as this formula i could manage top make the 1+(column) thing. But could not find the Product formula. I also could create the 1^(12/COUNT(column)-1. The thing is that i have to make the product to make this whole formula work.

 

[namliam]

All I find is a database?? Using that database I find you changed it, so I try to change the database as I think you did *BIG PAIN* (which is why I asked you to post HERE in this thread your database as you have it now !!! )

Anyway this is what I come up with....

The thing is that when i make this formula column by column it works fine, but I i try to make all columns in one query i get a resoult of 39*39=1521 numbers instead of only 39*10. I used the SQL statement that follows:

SELECT Indices.Month, IIf(Indices!DRS<0,1,0) AS DRS, IIf(Indices![3-Month Treasury]<0,1,0) AS [3-Month Treasury], IIf(Indices![Lehman Bros Agg Bd Index]<0,1,0) AS [Lehman Bros Agg Bd Index], IIf(Indices![Lehman Bros 1-3Y US Treas]<0,1,0) AS [Lehman Bros 1-3Y US Treas], IIf(Indices![Lehman Bros 7-10Y US Treas]<0,1,0) AS [Lehman Bros 7-10Y US Treas], IIf(Indices![Lehman Bros 20+Y US Treas]<0,1,0) AS [Lehman Bros 20+Y US Treas], IIf(Indices![JPMorgan EMBI+]<0,1,0) AS [JPMorgan EMBI+], IIf(Indices![SP 500 Index]<0,1,0) AS [SP 500 Index], IIf(Balance_Allocation!BalancedAllocation<0,1,0) AS BalancedAllocation
FROM Indices, Balance_Allocation
GROUP BY Indices.Month, DRS, [3-Month Treasury], [Lehman Bros Agg Bd Index], [Lehman Bros 1-3Y US Treas], [Lehman Bros 7-10Y US Treas], [Lehman Bros 20+Y US Treas], [JPMorgan EMBI+], [SP 500 Index], BalancedAllocation;

That seems logical, there is no where and/or no "joining" of the tables. Therefor access doesnt know how to but 1 and 1 together so every record from the table Indiced and Balance_Allocation is linked together thus creating 39*39 records....

Try below SQL instead, note the bold part that was changed.....

SELECT Indices.Month AS Expr1, IIf(Indices!DRS<0,1,0) AS DRS, IIf(Indices![3-Month Treasury]<0,1,0) AS [3-Month Treasury], IIf(Indices![Lehman Bros Agg Bd Index]<0,1,0) AS [Lehman Bros Agg Bd Index], IIf(Indices![Lehman Bros 1-3Y US Treas]<0,1,0) AS [Lehman Bros 1-3Y US Treas], IIf(Indices![Lehman Bros 7-10Y US Treas]<0,1,0) AS [Lehman Bros 7-10Y US Treas], IIf(Indices![Lehman Bros 20+Y US Treas]<0,1,0) AS [Lehman Bros 20+Y US Treas], IIf(Indices![JPMorgan EMBI+]<0,1,0) AS [JPMorgan EMBI+], IIf(Indices![SP 500 Index]<0,1,0) AS [SP 500 Index], IIf(Balance_Allocation!BalancedAllocation<0,1,0) AS BalancedAllocation
[b][u]FROM Indices INNER JOIN Balance_Allocation ON Indices.M = Balance_Allocation.M[/b][/u]
GROUP BY Indices.Month, Indices.DRS, [3-Month Treasury], [Lehman Bros Agg Bd Index], [Lehman Bros 1-3Y US Treas], [Lehman Bros 7-10Y US Treas], [Lehman Bros 20+Y US Treas], [JPMorgan EMBI+], [SP 500 Index], Indices.BalancedAllocation;

This way Access does know how to put 1 and 1 together and you should get only 39 records. Note that I cannot test it because the query you gave cannot run on this database
Also I think the group by is not needed

Nothing VBA about this BTW...

SELECT Indices.Month, Sum(IIf(Indices![SP 500 Index]>=0,1,0)) AS BullDJWA
FROM Indices
GROUP BY Indices.Month;

SELECT Indices.Month, Sum(Abs(Bull_DJWA!BullDJWA-1)) AS BearDJWA
FROM Bull_DJWA
GROUP BY Indices.Month;

You will notice that the second query is calculated after the first, but the thing is that i wanted to calculate both in the same query.

SELECT Indices.Month, Sum(IIf(Indices![SP 500 Index]>=0,1,0)) AS BullDJWA, Sum(Abs(IIf(Indices![SP 500 Index]>=0,1,0)-1)) AS BearDJWA
FROM Indices
GROUP BY Indices.Month;
or
SELECT Indices.Month, Sum(IIf(Indices![SP 500 Index]>=0,1,0)) AS BullDJWA, Sum(Abs(IIf(Indices![SP 500 Index]>=0,0,-1))) AS BearDJWA
FROM Indices
GROUP BY Indices.Month;

Again, I dont see the need for the GroupBy on the given dataset and no need for the SUM either
Also AGAIN ... NO VBA...

Then i had to create a very complicated table with lots of excel formulas. The 1st one is this one here:

= PRODUCT(1+O4:O42)^(12/COUNT(O4:O42))-1

As far as this formula i could manage top make the 1+(column) thing. But could not find the Product formula. I also could create the 1^(12/COUNT(column)-1. The thing is that i have to make the product to make this whole formula work.

I do not understand what you are doing with the product thing, I think the formula like you put it should not work properly....

 

[EL-g]

Oh ok. Sorry about that. Im attaching the database now. About the INNER JOIN, I've tried it before getting 39 at all the columns that should be 1 and 10 in the BalancedAllocation colulmn. The thing is that i need to use this resolut on another query afterwards, and that is why i need 0 and 1 instead of 0 and 39 otherwise my formula will not work. And that was my main concern about that query.

About the grouping you are sure RIGHT. Not needed at all. Thanks for the tip by the way! And yes, so far just SQL and no VBA. The thing is that i was wondering if it was better using VBA instead of SQL for this procedures. That is why i made this thread on the Modules & VBA.

Concerning the PRODUCT. It works like a sum formula, but instead of adding all the values it multiplies. And the thing is that i did not find a corresponding formula to that in SQL programing.

Thanks a lot for all the help

 

[namliam]

Oh ok. Sorry about that. Im attaching the database now. About the INNER JOIN, I've tried it before getting 39 at all the columns that should be 1 and 10 in the BalancedAllocation colulmn. The thing is that i need to use this resolut on another query afterwards, and that is why i need 0 and 1 instead of 0 and 39 otherwise my formula will not work. And that was my main concern about that query.

Thanks for the (working) database, but here you go again beeing not very specific...
getting 39 at all the columns... WTF do you mean? You have 39 rows in your original table, thus you get 39 rows as output of the query... why is this wrong?

Based on the content of the database that you posted what do you want/expect this query to produce?

Concerning the PRODUCT. It works like a sum formula, but instead of adding all the values it multiplies. And the thing is that i did not find a corresponding formula to that in SQL programing.

Yeah I know what the product thing does and how it does it... HOWEVER the way you put it on this forum... IT DOESNT WORK

Thanks a lot for all the help

Just trying to help

General tip:
NAMING CONVENTION... search the forum and read up on that subject then REDESIGN what you currenly have, cause (sorry to say so) it is a mess.

 

[EL-g]

Thanks for the (working) database, but here you go again beeing not very specific...
getting 39 at all the columns... WTF do you mean? You have 39 rows in your original table, thus you get 39 rows as output of the query... why is this wrong?

Based on the content of the database that you posted what do you want/expect this query to produce?


Yeah I know what the product thing does and how it does it... HOWEVER the way you put it on this forum... IT DOESNT WORK

Just trying to help

General tip:
NAMING CONVENTION... search the forum and read up on that subject then REDESIGN what you currenly have, cause (sorry to say so) it is a mess.

Sorry again. I hate being that noob. But im working hard to get better. Well let me try it again to see if it is better explained: What I meant about the 39 is: The resoult of the query will be a table in which every column will have only a zero or a one, because this binary code is connected to the other table that i have to make which in the attachment is called tabelao and is not yet done. When i use the join statement i get a table with zeros and 39s which are not a good resoult concerning the next calculation ill have to make. So basically the tabelao query is one of the 3 table youll find in the excel atachment. And as you can see this table runs some hard crazy huge mathmatical formulas. And im also concerned if il lll be able to make SQL queries in access with this formulas.

About the PRODUT thing im really sorry if i posted it worngly. How was i supposed to do? Please let me know so i dont make the same mistake again.

About the reformulate im sure gonna do that, because i agree its a total mess so far.

And thanks again for being so compreensive to me. Its been a hard task tring to do this access for me and you guys are being a geat help.

 

[namliam]

Sorry again. I hate being that noob.

No probs we all were starters at one point or another

But im working hard to get better.

Thats why you are here right?

Well let me try it again to see if it is better explained: What I meant about the 39 is: The resoult of the query will be a table in which every column will have only a zero or a one, because this binary code is connected to the other table that i have to make which in the attachment is called tabelao and is not yet done. When i use the join statement i get a table with zeros and 39s which are not a good resoult concerning the next calculation ill have to make. So basically the tabelao query is one of the 3 table youll find in the excel atachment. And as you can see this table runs some hard crazy huge mathmatical formulas. And im also concerned if il lll be able to make SQL queries in access with this formulas.

What excel attachment? You attached an Access database??
Anything can be done, it is just the matter of is it usefull?
The query that we are talking about is the "Below" query right? If we are, I do not see ANY values 39. There are way to many records because you didnt "join" month:

SELECT Indices.Month, IIf(Indices!DRS<0,1,0) AS DRS, IIf(Indices![3-Month Treasury]<0,1,0) AS [3-Month Treasury], IIf(Indices![Lehman Bros Agg Bd Index]<0,1,0) AS [Lehman Bros Agg Bd Index], IIf(Indices![Lehman Bros 1-3Y US Treas]<0,1,0) AS [Lehman Bros 1-3Y US Treas], IIf(Indices![Lehman Bros 7-10Y US Treas]<0,1,0) AS [Lehman Bros 7-10Y US Treas], IIf(Indices![Lehman Bros 20+Y US Treas]<0,1,0) AS [Lehman Bros 20+Y US Treas], IIf(Indices![JPMorgan EMBI+]<0,1,0) AS [JPMorgan EMBI+], IIf(Indices![SP 500 Index]<0,1,0) AS [SP 500 Index], IIf(Balance_Allocation!BalancedAllocation<0,1,0) AS BalancedAllocation
[b]FROM Indices INNER JOIN Balance_Allocation ON Indices.Month = Balance_Allocation.Month;[/b]

and you are still using the "Group by" thing which is totaly not needed....
i really cannot see how any value could return 39 because of the Iif's allways returning either 0 or 1.

About the PRODUT thing im really sorry if i posted it worngly. How was i supposed to do? Please let me know so i dont make the same mistake again.

Well I dont know you tell me... The PRODUCT formula normaly works like so:
=Product(2,3,4)
which will be the same as 2*3*4 = 24 beeing the output of the cell.
The way you put it... well it simply dont work... Or if you can Enlighten me as to how it does work???

About the reformulate im sure gonna do that, because i agree its a total mess so far.

And thanks again for being so compreensive to me. Its been a hard task tring to do this access for me and you guys are being a geat help.

Try and read up on the naming convention before your start back up... Having a proper naming convention is KEY.