[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1188
  • Last Modified:

Nested or Sub query not working in Access 2003

Hi. My brain is fried. I have some funky queries joined together as separate queries in Access that work just fine (the explanation for their funkiness is far too long to go into here). When I try to combine the queries into a single query (using "nested" or "sub" queries- not sure which term is correct), I receive a "Syntax error in FROM clause" message, and the "INNER" after L3Map is highlighted when I return to SQL view in the Access query window.

Can anyone tell me why this isn't working? Any help would be very appreciated!

Tim

TRANSFORM Count(ColMap.ID)
SELECT RowMap.Sort FROM [SELECT DISTINCT DATA2.ID, LabelTable.Sort AS Sort FROM DATA2 RIGHT JOIN LabelTable ON DATA2.FR_EXT2 = LabelTable.Value
WHERE LabelTable.Scale='SatScale' AND LabelTable.Sort In (3,1)] As RowMap
LEFT JOIN [SELECT ColMap.Sort, ColMap.ID
FROM [SELECT DISTINCT DATA2.ID, LabelTable.Sort AS Sort FROM DATA2 INNER JOIN LabelTable ON DATA2.OMC_YN= LabelTable.Value
WHERE LabelTable.Scale = 'OMC_YN' AND LabelTable.Sort In (2)] As L3Map  
INNER JOIN [SELECT DISTINCT DATA2.ID, LabelTable.Sort AS Sort
FROM DATA2 INNER JOIN LabelTable ON DATA2.RV_BODY= LabelTable.Value
WHERE LabelTable.Scale = 'RV_BODY' AND LabelTable.Sort In (2,1)] As ColMap ON L3Map.ID = ColMap.ID]
ON RowMap.ID = ColMap.ID
GROUP BY RowMap.Sort
PIVOT ColMap.Sort
0
bobafunk
Asked:
bobafunk
  • 4
  • 4
1 Solution
 
sweekes283Commented:
Try This:

TRANSFORM Count(ColMap.ID)
SELECT RowMap.Sort FROM [SELECT DISTINCT DATA2.ID, LabelTable.Sort AS Sort FROM DATA2 RIGHT JOIN LabelTable ON DATA2.FR_EXT2 = LabelTable.Value
WHERE LabelTable.Scale='SatScale' AND LabelTable.Sort In (3,1)] As RowMap
LEFT JOIN [SELECT ColMap.Sort, ColMap.ID
FROM [SELECT DISTINCT DATA2.ID, LabelTable.Sort AS Sort FROM DATA2 INNER JOIN LabelTable ON DATA2.OMC_YN= LabelTable.Value
WHERE LabelTable.Scale = 'OMC_YN' AND LabelTable.Sort In (2)] As L3Map  
INNER JOIN [SELECT DISTINCT DATA2.ID, LabelTable.Sort AS Sort
FROM DATA2 INNER JOIN LabelTable ON DATA2.RV_BODY= LabelTable.Value
WHERE LabelTable.Scale = 'RV_BODY' AND LabelTable.Sort In (2,1)] As ColMap ON L3Map.ID = ColMap.ID
ON RowMap.ID = ColMap.ID
GROUP BY RowMap.Sort
PIVOT ColMap.Sort
0
 
bobafunkAuthor Commented:
Nope, same error occurs.
0
 
sweekes283Commented:
Right - strike that, copied the original :D

This look better?

TRANSFORM Count(ColMap.ID)

SELECT RowMap.Sort
FROM
[
      SELECT DISTINCT DATA2.ID, LabelTable.Sort AS Sort FROM DATA2 RIGHT JOIN LabelTable ON DATA2.FR_EXT2 = LabelTable.Value
      WHERE LabelTable.Scale='SatScale' AND LabelTable.Sort In (3,1)
] As RowMap

LEFT JOIN

[
      SELECT ColMap.Sort, ColMap.ID
      FROM
      [

            [
                  SELECT DISTINCT DATA2.ID, LabelTable.Sort AS Sort
                  FROM DATA2 INNER JOIN LabelTable ON DATA2.RV_BODY= LabelTable.Value
                  WHERE LabelTable.Scale = 'RV_BODY' AND LabelTable.Sort In (2,1)
            ] As ColMap
                  
            INNER JOIN
      
            [
                  SELECT DISTINCT DATA2.ID, LabelTable.Sort AS Sort FROM DATA2 INNER JOIN LabelTable ON DATA2.OMC_YN= LabelTable.Value
                  WHERE LabelTable.Scale = 'OMC_YN' AND LabelTable.Sort In (2)
            ] As L3Map  
      
            ON L3Map.ID = ColMap.ID
      ]

]
ON RowMap.ID = ColMap.ID
GROUP BY RowMap.Sort

PIVOT ColMap.Sort

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
bobafunkAuthor Commented:
Thought it looked familiar :)
No this gives me the same "Syntax error in FROM clause" as before, the "INNER" after 'As ColMap' is highlighted in the SQL view of my query when I click OK in the Access error pop-up.
0
 
sweekes283Commented:
Gotta love nested SQL...

If you could, please post the individual queries, as well as the query joining them all together.  It'll help to see where you're coming from.
0
 
bobafunkAuthor Commented:
In my original query, do I have the nested ColMap and L3Map queries reversed in order?

Here are the individual queries (the final one joins them all toogether)

1st Query: RowMap
SELECT DISTINCT DATA2.ID, LabelTable.Sort AS Sort
FROM DATA2 RIGHT JOIN LabelTable ON DATA2.FR_EXT2= LabelTable.Value
WHERE LabelTable.Scale = 'SatScale' AND LabelTable.Sort In (3,1);

2nd Query: ColMap
SELECT DISTINCT DATA2.ID, LabelTable.Sort AS Sort
FROM DATA2 INNER JOIN LabelTable ON DATA2.RDA_RV_BODY= LabelTable.Value
WHERE LabelTable.Scale = 'RDA_RV_BODY' AND LabelTable.Sort In (2,1);

3rd Query: L3Map
SELECT DISTINCT DATA2.ID, LabelTable.Sort AS Sort
FROM DATA2 INNER JOIN LabelTable ON DATA2.OMC_YN= LabelTable.Value
WHERE LabelTable.Scale = 'OMC_YN' AND LabelTable.Sort In (2);

4th Query: ColMapEmbed (...combining the 2nd and 3rd queries)
SELECT ColMap.Sort, ColMap.ID
FROM L3Map INNER JOIN ColMap ON L3Map.ID = ColMap.ID;

Final Query:
TRANSFORM Count(ColMapEmbed.ID) AS CountOfID
SELECT RowMap.Sort
FROM RowMap LEFT JOIN ColMapEmbed ON RowMap.ID = ColMapEmbed.ID
GROUP BY RowMap.Sort
PIVOT ColMapEmbed.Sort;

0
 
sweekes283Commented:
Okay, Try this:

TRANSFORM Count(ColMapEmbed.ID) AS CountOfID
SELECT RowMap.Sort
FROM (SELECT DISTINCT DATA2.ID, LabelTable.Sort AS Sort FROM DATA2 RIGHT JOIN LabelTable ON DATA2.FR_EXT2= LabelTable.Value WHERE LabelTable.Scale = 'SatScale' AND LabelTable.Sort In (3,1)) as RowMap LEFT JOIN (SELECT ColMap.Sort, ColMap.ID FROM (SELECT DISTINCT DATA2.ID, LabelTable.Sort AS Sort FROM DATA2 INNER JOIN LabelTable ON DATA2.OMC_YN= LabelTable.Value WHERE LabelTable.Scale = 'OMC_YN' AND LabelTable.Sort In (2)) as L3Map INNER JOIN (SELECT DISTINCT DATA2.ID, LabelTable.Sort AS Sort FROM DATA2 INNER JOIN LabelTable ON DATA2.RDA_RV_BODY= LabelTable.Value WHERE LabelTable.Scale = 'RDA_RV_BODY' AND LabelTable.Sort In (2,1)) as ColMap ON L3Map.ID = ColMap.ID) as ColMapEmbed ON RowMap.ID = ColMapEmbed.ID
GROUP BY RowMap.Sort
PIVOT ColMapEmbed.Sort;
0
 
bobafunkAuthor Commented:
Thanks Sweekes, including the extra "AS" did the trick!
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now