SQL joins are whack

[Dell]

This works:
SELECT [TBL_Shift pay].[Employee ID], [TBL_Shift pay].[Adjuster Name], [TBL ACR_RCM List].ACR, [TBL ACR_RCM List].RCM, [TBL ACR_RCM List].FCM, [TBL_Shift pay].[Earn Descr], [TBL_Shift pay].[Other Hours Sum], [TBL_Shift pay].[Pay End Dt], IIf(([TBL_Shift pay].[Earn Descr]<>"Shift Premium 15%"),[Other Hours Sum]*0.75,[Other Hours Sum]*0.15) AS [New Hours Calculation], IIf(([TBL_Shift pay].[Earn Descr]<>"Shift Premium 15%"),([Other Hours Sum]*0.75)+[TBL_Shift pay].[Other Hours Sum],([Other Hours Sum]*0.15)+[TBL_Shift pay].[Other Hours Sum]) AS [Sum Other & New]
FROM ([TBL HR Data] INNER JOIN ([TBL_Not eligible] RIGHT JOIN [TBL_Shift pay] ON [TBL_Not eligible].[Employee ID] = [TBL_Shift pay].[Employee ID]) ON [TBL HR Data].[Employee ID] = [TBL_Shift pay].[Employee ID]) INNER JOIN [TBL ACR_RCM List] ON [TBL HR Data].[Current Direct Mgr Name] = [TBL ACR_RCM List].[ACR Full Name]
WHERE ((([TBL_Shift pay].[Pay End Dt]) Between [start date] And [end date]));


Now I just want to add another inner join, but it gets rejected. I add
this (i don't have the final formula in there, I just want to see a number to test)

Switch([TBL_Hours Report].[Standard Hours]=20,20,[TBL_Hours Report].[Standard Hours]=30, 30, True, 0)AS Expr1

I need to put an INNER JOIN if for this, simply
INNER JOIN [TBL_Hours Report] ON [TBL HR Data].[Employee ID] = [TBL_Hours Report].[Employee ID]

It just blows up. Part of my quandry is ANSI SQL doesn't allow "join x on y on z towit (from above)

FROM
([TBL HR Data] INNER JOIN ([TBL_Not eligible] RIGHT JOIN [TBL_Shift pay] ON [TBL_Not eligible].[Employee ID] = [TBL_Shift pay].[Employee ID])


ON [TBL HR Data].[Employee ID] = [TBL_Shift pay].[Employee ID])
INNER JOIN [TBL ACR_RCM List] ON [TBL HR Data].[Current Direct Mgr Name] = [TBL ACR_RCM List].[ACR Full Name]

It won't take the inner join internally or before or after the last INNER JOIN. I tried re-writing it using "regular" ANSI but the Access engine won't run it!

Any help appreciated!

[Rawb]

What you can do it make the INNERmost Joins their own Query and then make another Query based off of that one (instead of basing it off of a Table).

[Dell]

Can you illistrate what you mean?

======================

To me, this is ANSI SQL and it should run but it won't.

FROM
[TBL HR Data]
INNER JOIN [TBL_Not eligible] ON [TBL HR Data].[Employee ID]= [TBL_Not eligible].[Employee ID]
RIGHT JOIN [TBL_Shift pay] ON [TBL_Shift pay].[Employee ID] = [TBL HR Data].[Employee ID]
INNER JOIN [TBL_Hours Report] ON [TBL HR Data].[Employee ID] = [TBL_Hours Report].[Employee ID]
INNER JOIN [TBL ACR_RCM List] ON [TBL HR Data].[Current Direct Mgr Name]=[TBL ACR_RCM List].[ACR Full Name]

[weekend00]

Design in design view, that will be much easier. then modify in SQL view if you need.

[Dell]

I do my work in Oracle and Netezza using SQL. I am just trying to help someone out with Access. I don't know how to make a query using IFF or SWITCH etc. - I can join the tables in the Querybuilder. Is that helpful? Does the query builder take into account the order you join the tables (i.e. "build on the go")? Would your FROM look different depending on
what order you drew the lines to the join fields??

Thanks

[weekend00]

The order of joins don't make any difference to the result, may have different look in SQL statement.

[Rawb]

I do my work in Oracle and Netezza using SQL. I am just trying to help someone out with Access. I don't know how to make a query using IFF or SWITCH etc. - I can join the tables in the Querybuilder. Is that helpful? Does the query builder take into account the order you join the tables (i.e. "build on the go")? Would your FROM look different depending on
what order you drew the lines to the join fields??

Thanks

Hey, I'm really sorry it took me so long to get back to you, but I've been hecka busy!

I can't really illustrate what I'm talking about because it requires a complex SQL Query to diagram out, and I don't have anything that complex handy. . .

Basically, what you do though, is take the first few steps you want performed and save of just that portion in the Query Builder. Then you make another new Query for the remainder of the steps. Except this time, instead of linking to the Tables directly, point to the saved Query.

This way, you're Querying against an already filtered/JOINed result so there's no "ambiguous order" to worry about.

If you still have questions, post the layout of your Tables and I'll see if I can't show you what I mean using your Query as an example.