Query too Complex

[Bob Blooms]

I have two queries that are what I consider to be quite simple. When I run aAlda1 and alda2 everything works ok. When I try to bring the two together it appears that the query is too complex. Do not know where to start to investigate why it is too complex.



The intent of the query is combine the number of positions and their costs by program, subprogrma nad account (Line Seq Description) over two fiscal years.

SELECT qAlda1.Name, qAlda1.FTE AS [FY 2013 FTE], qAlda1.Cost AS [FY2013 Cost], qAlda1.Source, qAlda2.[Line Seq Descrpition], qAlda2.Program, qAlda2.[Sub-Program]
FROM (qAlda1 INNER JOIN qAlda2 ON (qAlda1.Account = qAlda2.RIFANS) AND (qAlda1.Name = qAlda2.Name)) INNER JOIN qAlda3 ON qAlda1.Name = qAlda3.Name;

[June7]

Want to provide db for analysis? Follow instructions at bottom of my post.

[Bob Blooms]

MyDB.zip

Keep looking at it and I cannot see the forest because of the trees.

[June7]

Which queries?

Opening the Switchboard errors on Me![Option1].SetFocus. Suggest you use TabOrder property to designate control that gets focus when form opens.

[Bob Blooms]

June7,

this is my first attempt at such a project. I know that I really messed up the Switchboard (a little knowledge is dangerous). Once I get this project held together with bubblegum and tape I will get rid of the Switchboard and replace it with my own command buttons, because I know I will need more that 8 buttons. The Switchboard was only intended at a way to control the two important user input tables.

[June7]

This is very complicated query structure. Why do you join qAlda3 in the query but don't pull any fields from it?

[Bob Blooms]

I pulled field Total Cost from qAlda3 to see if it this is what was causing the error. The query works without it but not with it. I have tried playing with the relationships, but solved nothing.

[June7]

Still don't understand why qAlda3 is involved. The other 2 will join and run.

All 3 queries have the same number of records, but qAlda3 has different names, for example Axelson, Douglas is not in qAlda3 but is in the other 2. I don't think that causes the failure but would the resulting recordset be accurate?

[Bob Blooms]

June 7, I just tried to query FY 2013 Total Costs with that of FY 2014 and get a Too Complicated. You may be right about the different names. They should all be the same. Will go back to square one and check the congurence of the list. Will get back to you. Thanks for you efforts. Crossing over from Excel to Access is a bumpy road.

[Bob Blooms]

I learned a tought lesson on this issue. Thinking modular is the best approach in Access Query; it appears that this is not the case. The original intent was to build a query with FY2013 personnel cost projections with included total cost and the number of positions. The same would be done for FY2014. Finally, for each employee, their program, subprogram and activity would be established. The three queries would then be brought together. It BLEW UP!!. I rebuilt it into one query and it works fine. I failed to remember that each query links back to the queries that preceded it. There is not an infinite number of calculations available in Access Query.