sql subquery difficulties

  • Thread starter Thread starter jonnyjon
  • Start date Start date
J

jonnyjon

I am currently trying to shoehorn an analysis piece I'm doing into
Access - however I'm having trouble writing the appropriate SQL query
to extract data. The challenge is that I have two tables - the query
I want to run should aggregate the rows in the first table and put
together some totals for a particular geography. I then want to take
that query (via a subquery?) and join that with another table based on
some value.

The query which I have tried to create is:

SELECT * FROM(
SELECT DISTINCT DSSPLUS.REGION AS "LOCATION", SUM(YTDSLS) AS "SALES",
SUM(ACTUAL_COST) FROM DSSPLUS
WHERE MKT_PRODUCT_LINE='THREADED-EXTERNAL'
GROUP BY DSSPLUS.REGION
)
LEFT JOIN LOCATION_COST ON LOCATION_COST.REGION = DSSPLUS.REGION

Any thoughts?
 
jonnyjon said:
I am currently trying to shoehorn an analysis piece I'm doing into
Access - however I'm having trouble writing the appropriate SQL query
to extract data. The challenge is that I have two tables - the query
I want to run should aggregate the rows in the first table and put
together some totals for a particular geography. I then want to take
that query (via a subquery?) and join that with another table based on
some value.

The query which I have tried to create is:

SELECT * FROM(
SELECT DISTINCT DSSPLUS.REGION AS "LOCATION", SUM(YTDSLS) AS "SALES",
SUM(ACTUAL_COST) FROM DSSPLUS
WHERE MKT_PRODUCT_LINE='THREADED-EXTERNAL'
GROUP BY DSSPLUS.REGION
)
LEFT JOIN LOCATION_COST ON LOCATION_COST.REGION = DSSPLUS.REGION

Any thoughts?
Click to expand...

I haven't laid out test tables to verify, but you might try something
like

SELECT * FROM
(
SELECT
DSSPLUS.REGION AS LOCATION,
SUM(YTDSLS) AS SALES,
SUM(ACTUAL_COST)
FROM DSSPLUS
WHERE MKT_PRODUCT_LINE='THREADED-EXTERNAL'
GROUP BY DSSPLUS.REGION
) AS D
LEFT JOIN
LOCATION_COST
ON LOCATION_COST.REGION = D.REGION

If you build this query in the query designer and then save it, you'll
probably find when you go back to look at it that Access has transformed
to use its own special syntax for derived tables:

SELECT * FROM
[SELECT
DSSPLUS.REGION AS LOCATION,
SUM(YTDSLS) AS SALES,
SUM(ACTUAL_COST)
FROM DSSPLUS
WHERE MKT_PRODUCT_LINE='THREADED-EXTERNAL'
GROUP BY DSSPLUS.REGION]. AS D
LEFT JOIN
LOCATION_COST
ON LOCATION_COST.REGION = D.REGION

You can write it that way yourself, if you like. The only trouble with
this is that you mustn't use the square brackets ([]) inside the
subquery, or else it won't be parsed correctly.
 
Back
Top