Okay...here is an example, but I will have to show you the boards leading up
to the last one as well:
Query1
SELECT [MVA].[Yr], [AA].[Cn], [MVA]![Bb]*([AA]![Cc]-[AA]![Dd]*0.95)/1000000
AS [Ee], [Ee]*[MVA]![Cx] AS [Ff],
([AA]![Gg]*([AA]![Hh]-[MVA]![Bb])/1000000)*[AA]![Ii]/([AA]![Ii]+[AA]![Dd]) AS
[Kk], [Kk]*[MVA]![Cx] AS [Ll],
([MCA]![Mm]*[AA]![Ii]/1000000)-(([AA]![Cc]-[AA]![Ii]-0.95*[AA]![Dd])*50/1000000)
AS [Nn], [Nn]*[MVA]![Cx] AS [Oo], [Ee]+[Kk]+[Nn] AS [Pp], [Pp]*[MVA]![Cx] AS
[Rr]
FROM [MCA], [MVA] INNER JOIN [AA] ON [MVA].[Yr] = [AA].[Yr];
Query2
SELECT [MVA].[Yr], [AA].[Cn], [AA]![Dd]*[MVA]![Bb]/1000000 AS [Ss],
[Ss]*[MVA]![Cx] AS [Tt],
([AA]![Gg]*([AA]![Hh]-[MVA]![Bb])/1000000)-([AA]![Gg]*([AA]![Hh]-[MVA]![Bb])/1000000)*[AA]![Ii]/([AA]![Ii]+[AA]![Dd])
AS [Uu], [Uu]*[MVA]![Cx] AS [Vv], [MCA]![Ww]*[AA]![Dd]/1000000 AS [Xx],
[Xx]*[MVA]![Cx] AS [Yy], [Ss]+[Uu]+[Xx] AS [Zz], [Zz]*[MVA]![Cx] AS [Ab]
FROM [MCA], [MVA] INNER JOIN [AA] ON [MVA].[Yr] = [AA].[Yr];
Query3
SELECT [MVA].[Yr], [Ac].[Cn], [Ad]/[MVA]![Cx] AS [Ae],
[Af]![Ag]*[Af]![Ah]/1000000 AS [Ad]
FROM [MVA] INNER JOIN ([Ac] INNER JOIN [Af] ON [Ac].[Cn] = [Af].[Cn]) ON
[MVA].[Yr] = [Af].[Yr];
Query4
SELECT [MVA].[Yr], [AK].[Cn],
([Al]![Am]*[MVA]![Bb]/1000000)-([AK]![An]*[Ao]![Ap]/1000000) AS [Ar],
[Ar]*[MVA]![Cx] AS [As],
((IIf(([AK]![An]*[AK]![At])=0,0,([AK]![An]*[AK]![At])*[Au]![Aw]/[Al]![Ax]))*0.85)*([MVA]![Ay]/1000000)-((IIf(([AK]![An]*[AK]![At])=0,0,([AK]![An]*[AK]![At])*[Au]![Aw]/[Al]![Ax])/[AK]![Az])*([Ao]![Ba]/1000000))
AS [Bc], [Bc]*[MVA]![Cx] AS [Bd], [Be]/[MVA]![Cx] AS [Bf],
([Bg]![Bh]/([AK]![Bk]/[AK]![Bl]))*(([AK]![Bk]/[AK]![Bl])/0.01)*75*([MVA]![Bb]/1000)/1000000
AS [Be], ([Bm]![Bn]*([MVA]![Bb]/1000000))-([Bm]![Bo]*([Ao]![Ap]/1000000)) AS
[Bp],
([Bp]*[MVA]![Cx])+(((([MVA]![Br]*600)/[Ac]![Bs])+([MVA]![Bt]/[Bu]![Bw]))*Bx![By]/1000000)
AS [Bz], ([Ca]![Bn]*([MVA]![Bb]/1000000))-([Ca]![Bo]*([Ao]![Ap]/1000000)) AS
[Cb],
([Cb]*[MVA]![Cx])+(((([MVA]![Br]*600)/[Ac]![Bs])+([MVA]![Bt]/[Bu]![Bw]))*[Ca]![Cd]/1000000)
AS [Ce], [Cf]/[MVA]![Cx] AS [Cg], [As]+[Bd]+[Be]+[Bz]+[Ce] AS [Cf],
[Ch]/[MVA]![Cx] AS [Ck], IIf([MVA]![Yr]=2004, 91,(IIf([MVA]![Yr]=2005,
243,(IIf([MVA]![Yr]=2006, 188, (IIf([MVA]![Yr]=2007, 431,
(IIf([MVA]![Yr]>2007,135,0))))))))) AS [Ch], [Cl]/[MVA]![Cx] AS [Cm],
[Cf]+[Ch] AS [Cl]
FROM [MCA], ((((((([MVA] INNER JOIN (([AK] INNER JOIN [Al] ON [AK].[Cn] =
[Al].[Cn]) INNER JOIN [Ao] ON [Al].[Yr] = [Ao].[Yr]) ON [MVA].[Yr] =
[Al].[Yr]) INNER JOIN ([Au] INNER JOIN [Bg] ON ([Au].[Yr] = [Bg].[Yr]) AND
([Au].[Cn] = [Bg].[Cn])) ON ([Ao].[Yr] = [Au].[Yr]) AND ([Al].[Cn] =
[Au].[Cn])) INNER JOIN [Bm] ON ([Bg].[Yr] = [Bm].[Yr]) AND ([Bg].[Cn] =
[Bm].[Cn])) INNER JOIN [Ac] ON [Bm].[Cn] = [Ac].[Cn]) INNER JOIN [Bu] ON
[Ac].[Cn] = [Bu].[Cn]) INNER JOIN Bx ON [Bu].[Cn] = Bx.[Cn]) INNER JOIN Laos
ON Bx.[Cn] = Laos.[Cn]) INNER JOIN [Ca] ON (Laos.[Cn] = [Ca].[Cn]) AND
([Bm].[Yr] = [Ca].[Yr]);
Query5
SELECT [MVA].[Yr], [AA].[Cn],
([MVA]![Bb]*([AA]![Cc]-[AA]![Dd]*(0.95)+[AA]![Dd]-[AA]![Gg])+[AA]![Gg]*[AA]![Hh]+[MCA]![Mm]*[AA]![Ii]-50*([AA]![Cc]-[AA]![Ii]-[AA]![Dd]*(0.95))+[MCA]![Ww]*[AA]![Dd])/1000000
AS [Co], IIf([MVA]![Yr]=2004, 91,(IIf([MVA]![Yr]=2005,
243,(IIf([MVA]![Yr]=2006, 188, (IIf([MVA]![Yr]=2007, 431,
(IIf([MVA]![Yr]>2007,135,0))))))))) AS [Ch],
(([Af]![Ag]*[Af]![Ah]/1000000)+[Ch])/[MVA]![Cx]+[Co] AS [Cp]
FROM [MCA], ([MVA] INNER JOIN [AA] ON [MVA].[Yr]=[AA].[Yr]) INNER JOIN [Af]
ON ([AA].[Yr]=[Af].[Yr]) AND ([AA].[Cn]=[Af].[Cn]);
Query6
SELECT [Query5].[Yr], [Query5].[Cn], [Query5].[Cp], [Query5]![Cp]*[MVA]![Cx]
AS [Cr]
FROM ([Query5] INNER JOIN [MVA] ON [Query5].[Yr] = [MVA].[Yr]) INNER JOIN
[Ao] ON [MVA].[Yr] = [Ao].[Yr];
Query7
SELECT [Query4].[Yr], [Query4].[Cn], [Query4].[Cg], [Query4].[Cf],
[Query4]![Cg]+[Query6]![Cp] AS ConUSD, [Query4]![Cf]+[Query6]![Cr] AS ConTHB,
[Query6].[Cp], [Query6].[Cr]
FROM [Query4], [Query6];
Notes:
- Query4 is so long because I had to rebuild all the formulas from scratch
otherwise I would get “Query too complexâ€
- Query6 should ideally be included in Query5, but I would get a message
saying “Query too complexâ€. It would only work if I put it into a new query.
- I cannot get Query7 to work. The different boards cannot be put into one
query.
- Ideally, the information on Queries 5,6,&7 should all be one query.
I know this is very long, but I couldn’t show you just Query7 without
showing you what precedes it.
Any suggestions?
Klatuu said:
Without seeing the SQL of your queries, we can't even hazard a guess.
--
Dave Hargis, Microsoft Access MVP
:
words I have seen way too much of today! And if it is not "too compex" then I
get a message saying "insufficient resources".
I have spent the day trying to break up boards into smaller sections and
trying to find other ways to get the same answer. Eventually I have to bring
the seperated boards back together again, so the end result is still the
same: Query is too complex
Does anyone have a suggestion that I could try to get around these
frustrating comments? Should I be using subqueries? - would that help
simplify things in the FROM clause?
Tara
