Error - lack of memory....tried INNER JOINS

J

jeevanshah21

I tried twisting an old code that hangs up with a new code, which gave
a JOIN SYNTAX error. I am a novice at access....can someone look down
at the new code and please tell me what I may be doing wrong.

Old - No error - HANGS UP

INSERT INTO ZFSM_ALL ( ID, RESPONSIBILITY_ID, TOP_MENU_ID,
TOP_FUNCTION_ID, [LEVEL], ENTRY_SEQUENCE, PROMPT, SUB_MENU_ID,
SUB_MENU_NAME, FUNCTION_ID, USER_FUNCTION_NAME, FUNCTION_DESCRIPTION,
MENU_ENTRY_DESCRIPTION )
SELECT [ZFSM_ALL.ID] & [fnd_menu_entries.ENTRY_SEQUENCE] AS ID,
ZFSM_ALL.RESPONSIBILITY_ID, ZFSM_ALL.SUB_MENU_ID AS TOP_MENU_ID,
ZFSM_ALL.FUNCTION_ID AS TOP_FUNCTION_ID, 3 AS [LEVEL],
fnd_menu_entries.ENTRY_SEQUENCE, fnd_menu_entries_tl.PROMPT,
fnd_menu_entries.SUB_MENU_ID, fnd_menus_tl.USER_MENU_NAME AS
SUB_MENU_NAME, fnd_menu_entries.FUNCTION_ID,
fnd_form_functions_tl.USER_FUNCTION_NAME,
fnd_form_functions_tl.DESCRIPTION AS FUNCTION_DESCRIPTION,
fnd_menu_entries_tl.DESCRIPTION AS MENU_ENTRY_DESCRIPTION

FROM ZFSM_ALL INNER JOIN
(((fnd_menu_entries LEFT JOIN fnd_menu_entries_tl ON
(fnd_menu_entries.MENU_ID = fnd_menu_entries_tl.MENU_ID) AND
(fnd_menu_entries.ENTRY_SEQUENCE =
fnd_menu_entries_tl.ENTRY_SEQUENCE)) LEFT JOIN fnd_menus_tl ON
fnd_menu_entries.SUB_MENU_ID = fnd_menus_tl.MENU_ID) LEFT JOIN
fnd_form_functions_tl ON fnd_menu_entries.FUNCTION_ID =
fnd_form_functions_tl.FUNCTION_ID) ON ZFSM_ALL.SUB_MENU_ID =
fnd_menu_entries.MENU_ID

WHERE (((ZFSM_ALL.LEVEL)=2))
ORDER BY ZFSM_ALL.RESPONSIBILITY_ID, ZFSM_ALL.ID,
ZFSM_ALL.SUB_MENU_ID, fnd_menu_entries.ENTRY_SEQUENCE;

NEW - CODE

INSERT INTO ZFSM_ALL ( ID, RESPONSIBILITY_ID, TOP_MENU_ID,
TOP_FUNCTION_ID, [LEVEL], ENTRY_SEQUENCE, PROMPT, SUB_MENU_ID,
SUB_MENU_NAME, FUNCTION_ID, USER_FUNCTION_NAME, FUNCTION_DESCRIPTION,
MENU_ENTRY_DESCRIPTION )
SELECT
[ZFSM_ALL.ID] & [fnd_menu_entries.ENTRY_SEQUENCE] AS ID,
ZFSM_ALL.RESPONSIBILITY_ID, ZFSM_ALL.SUB_MENU_ID AS TOP_MENU_ID,
ZFSM_ALL.FUNCTION_ID AS TOP_FUNCTION_ID, 3 AS [LEVEL],
fnd_menu_entries.ENTRY_SEQUENCE, fnd_menu_entries_tl.PROMPT,
fnd_menu_entries.SUB_MENU_ID, fnd_menus_tl.USER_MENU_NAME AS
SUB_MENU_NAME, fnd_menu_entries.FUNCTION_ID,
fnd_form_functions_tl.USER_FUNCTION_NAME,
fnd_form_functions_tl.DESCRIPTION AS FUNCTION_DESCRIPTION,
fnd_menu_entries_tl.DESCRIPTION AS MENU_ENTRY_DESCRIPTION
FROM (SELECT * FROM ZFSM_ALL WHERE ZFSM_ALL.LEVEL = 2)
INNER JOIN
((
(fnd_menu_entries
LEFT JOIN fnd_menu_entries_tl
ON (fnd_menu_entries.MENU_ID = fnd_menu_entries_tl.MENU_ID)
AND (fnd_menu_entries.ENTRY_SEQUENCE =
fnd_menu_entries_tl.ENTRY_SEQUENCE))
LEFT JOIN fnd_menus_tl
ON fnd_menu_entries.SUB_MENU_ID = fnd_menus_tl.MENU_ID)
LEFT JOIN fnd_form_functions_tl
ON fnd_menu_entries.FUNCTION_ID = fnd_form_functions_tl.FUNCTION_ID)
ON ZFSM_ALL.SUB_MENU_ID = fnd_menu_entries.MENU_ID
ORDER BY ZFSM_ALL.RESPONSIBILITY_ID, ZFSM_ALL.ID,
ZFSM_ALL.SUB_MENU_ID, fnd_menu_entries.ENTRY_SEQUENCE;
 
C

Chris2

I tried twisting an old code that hangs up with a new code, which gave
a JOIN SYNTAX error. I am a novice at access....can someone look down
at the new code and please tell me what I may be doing wrong.

Old - No error - HANGS UP

INSERT INTO ZFSM_ALL ( ID, RESPONSIBILITY_ID, TOP_MENU_ID,
TOP_FUNCTION_ID, [LEVEL], ENTRY_SEQUENCE, PROMPT, SUB_MENU_ID,
SUB_MENU_NAME, FUNCTION_ID, USER_FUNCTION_NAME, FUNCTION_DESCRIPTION,
MENU_ENTRY_DESCRIPTION )
SELECT [ZFSM_ALL.ID] & [fnd_menu_entries.ENTRY_SEQUENCE] AS ID,
ZFSM_ALL.RESPONSIBILITY_ID, ZFSM_ALL.SUB_MENU_ID AS TOP_MENU_ID,
ZFSM_ALL.FUNCTION_ID AS TOP_FUNCTION_ID, 3 AS [LEVEL],
fnd_menu_entries.ENTRY_SEQUENCE, fnd_menu_entries_tl.PROMPT,
fnd_menu_entries.SUB_MENU_ID, fnd_menus_tl.USER_MENU_NAME AS
SUB_MENU_NAME, fnd_menu_entries.FUNCTION_ID,
fnd_form_functions_tl.USER_FUNCTION_NAME,
fnd_form_functions_tl.DESCRIPTION AS FUNCTION_DESCRIPTION,
fnd_menu_entries_tl.DESCRIPTION AS MENU_ENTRY_DESCRIPTION

FROM ZFSM_ALL INNER JOIN
(((fnd_menu_entries LEFT JOIN fnd_menu_entries_tl ON
(fnd_menu_entries.MENU_ID = fnd_menu_entries_tl.MENU_ID) AND
(fnd_menu_entries.ENTRY_SEQUENCE =
fnd_menu_entries_tl.ENTRY_SEQUENCE)) LEFT JOIN fnd_menus_tl ON
fnd_menu_entries.SUB_MENU_ID = fnd_menus_tl.MENU_ID) LEFT JOIN
fnd_form_functions_tl ON fnd_menu_entries.FUNCTION_ID =
fnd_form_functions_tl.FUNCTION_ID) ON ZFSM_ALL.SUB_MENU_ID =
fnd_menu_entries.MENU_ID

WHERE (((ZFSM_ALL.LEVEL)=2))
ORDER BY ZFSM_ALL.RESPONSIBILITY_ID, ZFSM_ALL.ID,
ZFSM_ALL.SUB_MENU_ID, fnd_menu_entries.ENTRY_SEQUENCE;

NEW - CODE

INSERT INTO ZFSM_ALL ( ID, RESPONSIBILITY_ID, TOP_MENU_ID,
TOP_FUNCTION_ID, [LEVEL], ENTRY_SEQUENCE, PROMPT, SUB_MENU_ID,
SUB_MENU_NAME, FUNCTION_ID, USER_FUNCTION_NAME, FUNCTION_DESCRIPTION,
MENU_ENTRY_DESCRIPTION )
SELECT
[ZFSM_ALL.ID] & [fnd_menu_entries.ENTRY_SEQUENCE] AS ID,
ZFSM_ALL.RESPONSIBILITY_ID, ZFSM_ALL.SUB_MENU_ID AS TOP_MENU_ID,
ZFSM_ALL.FUNCTION_ID AS TOP_FUNCTION_ID, 3 AS [LEVEL],
fnd_menu_entries.ENTRY_SEQUENCE, fnd_menu_entries_tl.PROMPT,
fnd_menu_entries.SUB_MENU_ID, fnd_menus_tl.USER_MENU_NAME AS
SUB_MENU_NAME, fnd_menu_entries.FUNCTION_ID,
fnd_form_functions_tl.USER_FUNCTION_NAME,
fnd_form_functions_tl.DESCRIPTION AS FUNCTION_DESCRIPTION,
fnd_menu_entries_tl.DESCRIPTION AS MENU_ENTRY_DESCRIPTION
FROM (SELECT * FROM ZFSM_ALL WHERE ZFSM_ALL.LEVEL = 2)
INNER JOIN
((
(fnd_menu_entries
LEFT JOIN fnd_menu_entries_tl
ON (fnd_menu_entries.MENU_ID = fnd_menu_entries_tl.MENU_ID)
AND (fnd_menu_entries.ENTRY_SEQUENCE =
fnd_menu_entries_tl.ENTRY_SEQUENCE))
LEFT JOIN fnd_menus_tl
ON fnd_menu_entries.SUB_MENU_ID = fnd_menus_tl.MENU_ID)
LEFT JOIN fnd_form_functions_tl
ON fnd_menu_entries.FUNCTION_ID = fnd_form_functions_tl.FUNCTION_ID)
ON ZFSM_ALL.SUB_MENU_ID = fnd_menu_entries.MENU_ID
ORDER BY ZFSM_ALL.RESPONSIBILITY_ID, ZFSM_ALL.ID,
ZFSM_ALL.SUB_MENU_ID, fnd_menu_entries.ENTRY_SEQUENCE;
Click to expand...

jeevenshah21,

Here is your non-functioning new query, straightened up.

INSERT INTO ZFSM_ALL
(ID
,RESPONSIBILITY_ID
,TOP_MENU_ID
,TOP_FUNCTION_ID
,[LEVEL]
,ENTRY_SEQUENCE
,PROMPT
,SUB_MENU_ID
,SUB_MENU_NAME
,FUNCTION_ID
,USER_FUNCTION_NAME
,FUNCTION_DESCRIPTION
,MENU_ENTRY_DESCRIPTION)
SELECT [ZFSM_ALL.ID] & [fnd_menu_entries.ENTRY_SEQUENCE] AS ID
,ZFSM_ALL.RESPONSIBILITY_ID
,ZFSM_ALL.SUB_MENU_ID AS TOP_MENU_ID
,ZFSM_ALL.FUNCTION_ID AS TOP_FUNCTION_ID
,3 AS [LEVEL]
,fnd_menu_entries.ENTRY_SEQUENCE
,fnd_menu_entries_tl.PROMPT
,fnd_menu_entries.SUB_MENU_ID
,fnd_menus_tl.USER_MENU_NAME ASSUB_MENU_NAME
,fnd_menu_entries.FUNCTION_ID
,fnd_form_functions_tl.USER_FUNCTION_NAME
,fnd_form_functions_tl.DESCRIPTION AS FUNCTION_DESCRIPTION
,fnd_menu_entries_tl.DESCRIPTION AS MENU_ENTRY_DESCRIPTION
FROM (SELECT *
FROM ZFSM_ALL
WHERE ZFSM_ALL.LEVEL = 2)
INNER JOIN
(((fnd_menu_entries
LEFT JOIN
fnd_menu_entries_tl
ON (fnd_menu_entries.MENU_ID = fnd_menu_entries_tl.MENU_ID)
AND (fnd_menu_entries.ENTRY_SEQUENCE =
fnd_menu_entries_tl.ENTRY_SEQUENCE))
LEFT JOIN
fnd_menus_tl
ON fnd_menu_entries.SUB_MENU_ID = fnd_menus_tl.MENU_ID)
LEFT JOIN
fnd_form_functions_tl
ON fnd_menu_entries.FUNCTION_ID =
fnd_form_functions_tl.FUNCTION_ID)
ON ZFSM_ALL.SUB_MENU_ID = fnd_menu_entries.MENU_ID
ORDER BY ZFSM_ALL.RESPONSIBILITY_ID
,ZFSM_ALL.ID
,ZFSM_ALL.SUB_MENU_ID
,fnd_menu_entries.ENTRY_SEQUENCE;


If we compare this to the prior query:

FROM ZFSM_ALL

Has changed to:

FROM (SELECT *
FROM ZFSM_ALL
WHERE ZFSM_ALL.LEVEL = 2)


This is a table expression creating a new table on the FROM clause.
The new table has no name/alias.

The main query refers to a table named ZFSM_ALL outside this table
expression in several places, but the outer query has no idea what
that table name is because that name only exists inside the table
expression on the FROM clause.

I added some table aliases to the table expression so that the main
query could determine what was going on.

This following saves without an error in MS Access, but it is untested
and I have no idea if it will do what you want it to.


INSERT INTO ZFSM_ALL
(ID
,RESPONSIBILITY_ID
,TOP_MENU_ID
,TOP_FUNCTION_ID
,[LEVEL]
,ENTRY_SEQUENCE
,PROMPT
,SUB_MENU_ID
,SUB_MENU_NAME
,FUNCTION_ID
,USER_FUNCTION_NAME
,FUNCTION_DESCRIPTION
,MENU_ENTRY_DESCRIPTION)
SELECT [ZFSM_ALL.ID] & [fnd_menu_entries.ENTRY_SEQUENCE] AS ID
,ZFSM_ALL.RESPONSIBILITY_ID
,ZFSM_ALL.SUB_MENU_ID AS TOP_MENU_ID
,ZFSM_ALL.FUNCTION_ID AS TOP_FUNCTION_ID
,3 AS [LEVEL]
,fnd_menu_entries.ENTRY_SEQUENCE
,fnd_menu_entries_tl.PROMPT
,fnd_menu_entries.SUB_MENU_ID
,fnd_menus_tl.USER_MENU_NAME AS SUB_MENU_NAME
,fnd_menu_entries.FUNCTION_ID
,fnd_form_functions_tl.USER_FUNCTION_NAME
,fnd_form_functions_tl.DESCRIPTION AS FUNCTION_DESCRIPTION
,fnd_menu_entries_tl.DESCRIPTION AS MENU_ENTRY_DESCRIPTION
FROM (SELECT Z0.*
FROM ZFSM_ALL AS Z0
WHERE Z0.LEVEL = 2) AS Z1
INNER JOIN
(((fnd_menu_entries
LEFT JOIN
fnd_menu_entries_tl
ON (fnd_menu_entries.MENU_ID = fnd_menu_entries_tl.MENU_ID)
AND (fnd_menu_entries.ENTRY_SEQUENCE =
fnd_menu_entries_tl.ENTRY_SEQUENCE))
LEFT JOIN
fnd_menus_tl
ON fnd_menu_entries.SUB_MENU_ID = fnd_menus_tl.MENU_ID)
LEFT JOIN
fnd_form_functions_tl
ON fnd_menu_entries.FUNCTION_ID =
fnd_form_functions_tl.FUNCTION_ID)
ON Z1.SUB_MENU_ID = fnd_menu_entries.MENU_ID
ORDER BY Z1.RESPONSIBILITY_ID
,Z1.ID
,Z1.SUB_MENU_ID
,fnd_menu_entries.ENTRY_SEQUENCE;



Possibly Unnecessary Commentary:

With proper table aliases applied throughout, this appears much
cleaner.

INSERT INTO ZFSM_ALL
(ID
,RESPONSIBILITY_ID
,TOP_MENU_ID
,TOP_FUNCTION_ID
,[LEVEL]
,ENTRY_SEQUENCE
,PROMPT
,SUB_MENU_ID
,SUB_MENU_NAME
,FUNCTION_ID
,USER_FUNCTION_NAME
,FUNCTION_DESCRIPTION
,MENU_ENTRY_DESCRIPTION)
SELECT [ZFSM_ALL.ID] & [ME1.ENTRY_SEQUENCE] AS ID
,ZFSM_ALL.RESPONSIBILITY_ID
,ZFSM_ALL.SUB_MENU_ID AS TOP_MENU_ID
,ZFSM_ALL.FUNCTION_ID AS TOP_FUNCTION_ID
,3 AS [LEVEL]
,ME1.ENTRY_SEQUENCE
,ME2.PROMPT
,ME1.SUB_MENU_ID
,M1.USER_MENU_NAME AS SUB_MENU_NAME
,ME1.FUNCTION_ID
,FF1.USER_FUNCTION_NAME
,FF1.DESCRIPTION AS FUNCTION_DESCRIPTION
,ME2.DESCRIPTION AS MENU_ENTRY_DESCRIPTION
FROM (SELECT Z0.*
FROM ZFSM_ALL AS Z0
WHERE Z0.LEVEL = 2) AS Z1
INNER JOIN
(((fnd_menu_entries AS ME1
LEFT JOIN
fnd_menu_entries_tl AS ME2
ON (ME1.MENU_ID = ME2.MENU_ID)
AND (ME1.ENTRY_SEQUENCE = ME2.ENTRY_SEQUENCE))
LEFT JOIN
fnd_menus_tl AS M1
ON ME1.SUB_MENU_ID = M1.MENU_ID)
LEFT JOIN
fnd_form_functions_tl FF1
ON ME1.FUNCTION_ID = FF1.FUNCTION_ID)
ON Z1.SUB_MENU_ID = ME1.MENU_ID
ORDER BY Z1.RESPONSIBILITY_ID
,Z1.ID
,Z1.SUB_MENU_ID
,ME1.ENTRY_SEQUENCE;


Sincerely,

Chris O.
 
J

jeevanshah21

I tried twisting an old code that hangs up with a new code, which
gave
a JOIN SYNTAX error. I am a novice at access....can someone look down
at the new code and please tell me what I may be doing wrong.
Click to expand...
Old - No error - HANGS UP
Click to expand...
INSERT INTO ZFSM_ALL ( ID, RESPONSIBILITY_ID, TOP_MENU_ID,
TOP_FUNCTION_ID, [LEVEL], ENTRY_SEQUENCE, PROMPT, SUB_MENU_ID,
SUB_MENU_NAME, FUNCTION_ID, USER_FUNCTION_NAME,
FUNCTION_DESCRIPTION,

MENU_ENTRY_DESCRIPTION )
SELECT [ZFSM_ALL.ID] & [fnd_menu_entries.ENTRY_SEQUENCE] AS ID,
ZFSM_ALL.RESPONSIBILITY_ID, ZFSM_ALL.SUB_MENU_ID AS TOP_MENU_ID,
ZFSM_ALL.FUNCTION_ID AS TOP_FUNCTION_ID, 3 AS [LEVEL],
fnd_menu_entries.ENTRY_SEQUENCE, fnd_menu_entries_tl.PROMPT,
fnd_menu_entries.SUB_MENU_ID, fnd_menus_tl.USER_MENU_NAME AS
SUB_MENU_NAME, fnd_menu_entries.FUNCTION_ID,
fnd_form_functions_tl.USER_FUNCTION_NAME,
fnd_form_functions_tl.DESCRIPTION AS FUNCTION_DESCRIPTION,
fnd_menu_entries_tl.DESCRIPTION AS MENU_ENTRY_DESCRIPTION
Click to expand...
FROM ZFSM_ALL INNER JOIN
(((fnd_menu_entries LEFT JOIN fnd_menu_entries_tl ON
(fnd_menu_entries.MENU_ID = fnd_menu_entries_tl.MENU_ID) AND
(fnd_menu_entries.ENTRY_SEQUENCE =
fnd_menu_entries_tl.ENTRY_SEQUENCE)) LEFT JOIN fnd_menus_tl ON
fnd_menu_entries.SUB_MENU_ID = fnd_menus_tl.MENU_ID) LEFT JOIN
fnd_form_functions_tl ON fnd_menu_entries.FUNCTION_ID =
fnd_form_functions_tl.FUNCTION_ID) ON ZFSM_ALL.SUB_MENU_ID =
fnd_menu_entries.MENU_ID
Click to expand...
WHERE (((ZFSM_ALL.LEVEL)=2))
ORDER BY ZFSM_ALL.RESPONSIBILITY_ID, ZFSM_ALL.ID,
ZFSM_ALL.SUB_MENU_ID, fnd_menu_entries.ENTRY_SEQUENCE;
Click to expand...
NEW - CODE
Click to expand...
INSERT INTO ZFSM_ALL ( ID, RESPONSIBILITY_ID, TOP_MENU_ID,
TOP_FUNCTION_ID, [LEVEL], ENTRY_SEQUENCE, PROMPT, SUB_MENU_ID,
SUB_MENU_NAME, FUNCTION_ID, USER_FUNCTION_NAME,
FUNCTION_DESCRIPTION,



MENU_ENTRY_DESCRIPTION )
SELECT
[ZFSM_ALL.ID] & [fnd_menu_entries.ENTRY_SEQUENCE] AS ID,
ZFSM_ALL.RESPONSIBILITY_ID, ZFSM_ALL.SUB_MENU_ID AS TOP_MENU_ID,
ZFSM_ALL.FUNCTION_ID AS TOP_FUNCTION_ID, 3 AS [LEVEL],
fnd_menu_entries.ENTRY_SEQUENCE, fnd_menu_entries_tl.PROMPT,
fnd_menu_entries.SUB_MENU_ID, fnd_menus_tl.USER_MENU_NAME AS
SUB_MENU_NAME, fnd_menu_entries.FUNCTION_ID,
fnd_form_functions_tl.USER_FUNCTION_NAME,
fnd_form_functions_tl.DESCRIPTION AS FUNCTION_DESCRIPTION,
fnd_menu_entries_tl.DESCRIPTION AS MENU_ENTRY_DESCRIPTION
FROM (SELECT * FROM ZFSM_ALL WHERE ZFSM_ALL.LEVEL = 2)
INNER JOIN
((
(fnd_menu_entries
LEFT JOIN fnd_menu_entries_tl
ON (fnd_menu_entries.MENU_ID = fnd_menu_entries_tl.MENU_ID)
AND (fnd_menu_entries.ENTRY_SEQUENCE =
fnd_menu_entries_tl.ENTRY_SEQUENCE))
LEFT JOIN fnd_menus_tl
ON fnd_menu_entries.SUB_MENU_ID = fnd_menus_tl.MENU_ID)
LEFT JOIN fnd_form_functions_tl
ON fnd_menu_entries.FUNCTION_ID = fnd_form_functions_tl.FUNCTION_ID)
ON ZFSM_ALL.SUB_MENU_ID = fnd_menu_entries.MENU_ID
ORDER BY ZFSM_ALL.RESPONSIBILITY_ID, ZFSM_ALL.ID,
ZFSM_ALL.SUB_MENU_ID, fnd_menu_entries.ENTRY_SEQUENCE;
Click to expand...

jeevenshah21,

Here is your non-functioning new query, straightened up.

INSERT INTO ZFSM_ALL
(ID
,RESPONSIBILITY_ID
,TOP_MENU_ID
,TOP_FUNCTION_ID
,[LEVEL]
,ENTRY_SEQUENCE
,PROMPT
,SUB_MENU_ID
,SUB_MENU_NAME
,FUNCTION_ID
,USER_FUNCTION_NAME
,FUNCTION_DESCRIPTION
,MENU_ENTRY_DESCRIPTION)
SELECT [ZFSM_ALL.ID] & [fnd_menu_entries.ENTRY_SEQUENCE] AS ID
,ZFSM_ALL.RESPONSIBILITY_ID
,ZFSM_ALL.SUB_MENU_ID AS TOP_MENU_ID
,ZFSM_ALL.FUNCTION_ID AS TOP_FUNCTION_ID
,3 AS [LEVEL]
,fnd_menu_entries.ENTRY_SEQUENCE
,fnd_menu_entries_tl.PROMPT
,fnd_menu_entries.SUB_MENU_ID
,fnd_menus_tl.USER_MENU_NAME ASSUB_MENU_NAME
,fnd_menu_entries.FUNCTION_ID
,fnd_form_functions_tl.USER_FUNCTION_NAME
,fnd_form_functions_tl.DESCRIPTION AS FUNCTION_DESCRIPTION
,fnd_menu_entries_tl.DESCRIPTION AS MENU_ENTRY_DESCRIPTION
FROM (SELECT *
FROM ZFSM_ALL
WHERE ZFSM_ALL.LEVEL = 2)
INNER JOIN
(((fnd_menu_entries
LEFT JOIN
fnd_menu_entries_tl
ON (fnd_menu_entries.MENU_ID = fnd_menu_entries_tl.MENU_ID)
AND (fnd_menu_entries.ENTRY_SEQUENCE =
fnd_menu_entries_tl.ENTRY_SEQUENCE))
LEFT JOIN
fnd_menus_tl
ON fnd_menu_entries.SUB_MENU_ID = fnd_menus_tl.MENU_ID)
LEFT JOIN
fnd_form_functions_tl
ON fnd_menu_entries.FUNCTION_ID =
fnd_form_functions_tl.FUNCTION_ID)
ON ZFSM_ALL.SUB_MENU_ID = fnd_menu_entries.MENU_ID
ORDER BY ZFSM_ALL.RESPONSIBILITY_ID
,ZFSM_ALL.ID
,ZFSM_ALL.SUB_MENU_ID
,fnd_menu_entries.ENTRY_SEQUENCE;

If we compare this to the prior query:

FROM ZFSM_ALL

Has changed to:

FROM (SELECT *
FROM ZFSM_ALL
WHERE ZFSM_ALL.LEVEL = 2)

This is a table expression creating a new table on the FROM clause.
The new table has no name/alias.

The main query refers to a table named ZFSM_ALL outside this table
expression in several places, but the outer query has no idea what
that table name is because that name only exists inside the table
expression on the FROM clause.

I added some table aliases to the table expression so that the main
query could determine what was going on.

This following saves without an error in MS Access, but it is untested
and I have no idea if it will do what you want it to.

INSERT INTO ZFSM_ALL
(ID
,RESPONSIBILITY_ID
,TOP_MENU_ID
,TOP_FUNCTION_ID
,[LEVEL]
,ENTRY_SEQUENCE
,PROMPT
,SUB_MENU_ID
,SUB_MENU_NAME
,FUNCTION_ID
,USER_FUNCTION_NAME
,FUNCTION_DESCRIPTION
,MENU_ENTRY_DESCRIPTION)
SELECT [ZFSM_ALL.ID] & [fnd_menu_entries.ENTRY_SEQUENCE] AS ID
,ZFSM_ALL.RESPONSIBILITY_ID
,ZFSM_ALL.SUB_MENU_ID AS TOP_MENU_ID
,ZFSM_ALL.FUNCTION_ID AS TOP_FUNCTION_ID
,3 AS [LEVEL]
,fnd_menu_entries.ENTRY_SEQUENCE
,fnd_menu_entries_tl.PROMPT
,fnd_menu_entries.SUB_MENU_ID
,fnd_menus_tl.USER_MENU_NAME AS SUB_MENU_NAME
,fnd_menu_entries.FUNCTION_ID
,fnd_form_functions_tl.USER_FUNCTION_NAME
,fnd_form_functions_tl.DESCRIPTION AS FUNCTION_DESCRIPTION
,fnd_menu_entries_tl.DESCRIPTION AS MENU_ENTRY_DESCRIPTION
FROM (SELECT Z0.*
FROM ZFSM_ALL AS Z0
WHERE Z0.LEVEL = 2) AS Z1
INNER JOIN
(((fnd_menu_entries
LEFT JOIN
fnd_menu_entries_tl
ON (fnd_menu_entries.MENU_ID = fnd_menu_entries_tl.MENU_ID)
AND (fnd_menu_entries.ENTRY_SEQUENCE =
fnd_menu_entries_tl.ENTRY_SEQUENCE))
LEFT JOIN
fnd_menus_tl
ON fnd_menu_entries.SUB_MENU_ID = fnd_menus_tl.MENU_ID)
LEFT JOIN
fnd_form_functions_tl
ON fnd_menu_entries.FUNCTION_ID =
fnd_form_functions_tl.FUNCTION_ID)
ON Z1.SUB_MENU_ID = fnd_menu_entries.MENU_ID
ORDER BY Z1.RESPONSIBILITY_ID
,Z1.ID
,Z1.SUB_MENU_ID
,fnd_menu_entries.ENTRY_SEQUENCE;

Possibly Unnecessary Commentary:

With proper table aliases applied throughout, this appears much
cleaner.

INSERT INTO ZFSM_ALL
(ID
,RESPONSIBILITY_ID
,TOP_MENU_ID
,TOP_FUNCTION_ID
,[LEVEL]
,ENTRY_SEQUENCE
,PROMPT
,SUB_MENU_ID
,SUB_MENU_NAME
,FUNCTION_ID
,USER_FUNCTION_NAME
,FUNCTION_DESCRIPTION
,MENU_ENTRY_DESCRIPTION)
SELECT [ZFSM_ALL.ID] & [ME1.ENTRY_SEQUENCE] AS ID
,ZFSM_ALL.RESPONSIBILITY_ID
,ZFSM_ALL.SUB_MENU_ID AS TOP_MENU_ID
,ZFSM_ALL.FUNCTION_ID AS TOP_FUNCTION_ID
,3 AS [LEVEL]
,ME1.ENTRY_SEQUENCE
,ME2.PROMPT
,ME1.SUB_MENU_ID
,M1.USER_MENU_NAME AS SUB_MENU_NAME
,ME1.FUNCTION_ID
,FF1.USER_FUNCTION_NAME
,FF1.DESCRIPTION AS FUNCTION_DESCRIPTION
,ME2.DESCRIPTION AS MENU_ENTRY_DESCRIPTION
FROM (SELECT Z0.*
FROM ZFSM_ALL AS Z0
WHERE Z0.LEVEL = 2) AS Z1
INNER JOIN
(((fnd_menu_entries AS ME1
LEFT JOIN
fnd_menu_entries_tl AS ME2
ON (ME1.MENU_ID = ME2.MENU_ID)
AND (ME1.ENTRY_SEQUENCE = ME2.ENTRY_SEQUENCE))
LEFT JOIN
fnd_menus_tl AS M1
ON ME1.SUB_MENU_ID = M1.MENU_ID)
LEFT JOIN
fnd_form_functions_tl FF1
ON ME1.FUNCTION_ID = FF1.FUNCTION_ID)
ON Z1.SUB_MENU_ID = ME1.MENU_ID
ORDER BY Z1.RESPONSIBILITY_ID
,Z1.ID
,Z1.SUB_MENU_ID
,ME1.ENTRY_SEQUENCE;

Sincerely,

Chris O.
Click to expand...

Chris - thanks for your inputs, there are no erorrs in SQL save,
however when I run it I get and erro "invalid braceting" for
ZFSM_ALL.ID. Would you have any insights into this. Thanks.
 
Top