Query over Query

[EL-g]

Hey guys,

I'm a newby here. Im programing an Access as follows:

I have 1 table called indices with the 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.

Each filed has 39 observations. First of all I created a Query Called BalancedAllocation as follows:

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;

Then I created a Query called BullDJWA as follows

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

Then I had to create another query called BearDJWA that uses the query BullDJWA as follows:

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

SO THE FIRST QUESTION IS: Can i create the BearDJWA in the same query as the BullDJWA?

Then i had to create a binary column using IIF from all the fields in the table indices. Creating a query separated for each field qould look like this using the 3-month trasury example:

SELECT IIf(Indices![3-Month Treasury]<0,1,0) AS [3-Month Treasury]
FROM Indices;

SO HERE IS THE SECOND QUESTION: I tried to create all "IIF" in one big query and the resoult was funny. I used this formula:

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

and instead of giving me the resoult for the 39 observations it gave me 1521. I dont have a clue why???

And the THIRD QUESTION: I have to make this excel formula: =PRODUCT(1+O4:O42)^(12/COUNT(O4:O42))-1 in another query. Could somene help me out with the SQL code??????????

Im starting to think that maybe it will be easier to make it on VB.

What do you guys think. I appreciate all the help.

REgards

 

[RV]

FIRST QUESTION

Try this:

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

SECOND QUESTION

and instead of giving me the resoult for the 39 observations it gave me 1521. I dont have a clue why???

That's because you didn't join your tables in your query, hence resulting in the number of combinations being 39*39 = 1521

THIRD QUESTION

Use the search facility of the forum.

RV

 

[EL-g]

Thanks a lot RV.

QUESTION 1
For Q1 I did this:

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

And its asking me to enter parameter value for Indices![SP 500 Index], dunno why? Maybe its because its not referenced the table Indices in the FROM clause

QUESTION 2:
For question 2 how can i join the tables.

QUESTION 3:
And for 3 ill take a look.

And yet another thing i forgot:
How can i make this 2 queries in one:

SELECT Indices!Month AS Period_Considered_FROM
FROM Indices
WHERE ((([Indices]![Month]) In (SELECT [Indices]![Month] FROM Indices WHERE Month = (SELECT MIN(Month) FROM Indices))))
GROUP BY Indices!Month;


SELECT Indices!Month AS Period_Considered_TO
FROM Indices
WHERE ((([Indices]![Month]) In (SELECT [Indices]![Month] FROM Indices WHERE Month = (SELECT MAX(Month) FROM Indices))))
GROUP BY Indices!Month;


Thanks a lot for the help

 

[EL-g]

anyone please???

 

[Banana]

I'd be concerned with you using "Month" as a fieldname considering it is a reserved by Jet. Be sure to take a look at this list reserved for Access, also.

Furthermore, I'd avoid using any special characters and spaces. The delimiters may work, but why put it up to chance? Best to have all fields, queries, tables name in ThisFormatWithAllFirstLettersCapitalized. That will ensure that it doesn't do anything weird with your queries.

 

[RV]

QUESTION 1

You need to replace the name of your query by the table name:

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

(not tested)

QUESTION 2

Search the forum or have a look at Access Help.
This is basic knowledge you should have before you even consider to start building Access applications

And Banana is right, you should not use Month for any object names.

RV

 

[EL-g]

Oh Ok Banana. Thanks a lot for the help. Maybe I should rename my fields then! About the JOIN im gonna use the INNER JOIN. Is that correct? Because it seems to me that The INNER JOIN operation can be used in any FROM clause to combine records from two tables. The thing is that i have only one table. So i dont know what to do.

The Question 1, Ive tried and now its asking me for Indices.BullDJWA. I could name my query as Indices, but i cannot because i get the following msg: YOU HAVE GIVEN THIS QUERY THE SAME NAME AS AN EXISTING TABLE IN YOUR DATABASE.

How can i make this 2 queries in one:

SELECT Indices!Month AS Period_Considered_FROM
FROM Indices
WHERE ((([Indices]![Month]) In (SELECT [Indices]![Month] FROM Indices WHERE Month = (SELECT MIN(Month) FROM Indices))))
GROUP BY Indices!Month;


SELECT Indices!Month AS Period_Considered_TO
FROM Indices
WHERE ((([Indices]![Month]) In (SELECT [Indices]![Month] FROM Indices WHERE Month = (SELECT MAX(Month) FROM Indices))))
GROUP BY Indices!Month;

Anyone PLEASE

 

[EL-g]

How can i make this 2 queries in one:

SELECT Indices!Month AS Period_Considered_FROM
FROM Indices
WHERE ((([Indices]![Month]) In (SELECT [Indices]![Month] FROM Indices WHERE Month = (SELECT MIN(Month) FROM Indices))))
GROUP BY Indices!Month;


SELECT Indices!Month AS Period_Considered_TO
FROM Indices
WHERE ((([Indices]![Month]) In (SELECT [Indices]![Month] FROM Indices WHERE Month = (SELECT MAX(Month) FROM Indices))))
GROUP BY Indices!Month;

Anyone PLEASE

Ok. I solved this:
SELECT Max(Indices.Month) AS MaxOfMonth, Min(Indices.Month) AS MinOfMonth
FROM Indices;

BUT i still couldnt make Q1 Work

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

And The JOIN thingy does not work too

Anyone

 

[EL-g]

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.

I upped the excel file that i have to create in access and also the access im working at here:

http://forums.aspfree.com/microsoft-access-help-18/please-help-123806.html

Please help me out guys, cuz im in a deadline to create this access and i cannot solve this problems

Thanks a lot fotr the help

 

[Banana]

Did you already rename all fields to the ThisFormatWithAllFirstLettersCaptializedAndNoSpecialCharactersAndNoSpaces?

Did you change the fieldname "Month" in Indices table, which is a reserved word as I posted above, to another name (e.g. RecentMonth or CurrentMonth or something like that)?

Until you've fixed those, we can be able to help more.

If you're are on a deadline, remember this is a forum and we give free advices on our time; we are under no obligation to answer a particular person's question. If you *MUST* finish it, and are stuck, I can only suggest you to hire a database consultant to finsh it for you. Otherwise ask your boss for extension.

 

[EL-g]

Did you already rename all fields to the ThisFormatWithAllFirstLettersCaptializedAndNoSpecialCharactersAndNoSpaces?

Did you change the fieldname "Month" in Indices table, which is a reserved word as I posted above, to another name (e.g. RecentMonth or CurrentMonth or something like that)?

Until you've fixed those, we can be able to help more.

If you're are on a deadline, remember this is a forum and we give free advices on our time; we are under no obligation to answer a particular person's question. If you *MUST* finish it, and are stuck, I can only suggest you to hire a database consultant to finsh it for you. Otherwise ask your boss for extension.

Ok. Its all renamed as attachment. Sorry btw. I know u guys are doing me a favor. Sorry if i was unpatient.

And thanks for the help