I know nothing about sql, but can build queries in Access using the query design tool. I have built the following two queries that I need to combine into one. I think I need to do a left join, but I can't seem to get the syntax right.
When I join the queries I want all of the rows and columns from the first query, but I want to add to this column: Sum(AC_MONTHLY.[S751]+[S750]), LastOfS1306 from the 2nd query.
Not all of the rows from the first query will have a LastOfS1306 value.
Query 1
SELECT AC_PROPERTY.RSV_CAT, AC_MONTHLY.OUTDATE, Sum(AC_MONTHLY.S370) AS [Gross PRODUCT_1], Sum(AC_MONTHLY.S371) AS [Gross PRODUCT_2], Sum(AC_MONTHLY.S374) AS [Gross PRODUCT_3], Sum(AC_MONTHLY.S815) AS [Net PRODUCT_1], Sum(AC_MONTHLY.S816) AS [Net PRODUCT_2], Sum(AC_MONTHLY.S819) AS [Net PRODUCT_3], Sum(AC_MONTHLY.s1062) AS [Net Opx ($)], Sum(AC_MONTHLY.[S751]+[S750]) AS [Net Cap Investment ($)]
FROM AC_MONTHLY INNER JOIN AC_PROPERTY ON AC_MONTHLY.PROPNUM = AC_PROPERTY.PROPNUM
GROUP BY AC_PROPERTY.RSV_CAT, AC_MONTHLY.OUTDATE, AC_MONTHLY.SCENARIO
HAVING (((AC_MONTHLY.SCENARIO)="RR4Q17_SEC"));
Query 2
SELECT AC_DETAIL.SCENARIO, AC_DETAIL.PROPNUM, Max(AC_MONTHLY.OUTDATE) AS MaxOfOUTDATE, Last(AC_DETAIL.S1306) AS LastOfS1306
FROM AC_DETAIL INNER JOIN AC_MONTHLY ON (AC_DETAIL.SCENARIO = AC_MONTHLY.SCENARIO) AND (AC_DETAIL.PROPNUM = AC_MONTHLY.PROPNUM)
GROUP BY AC_DETAIL.SCENARIO, AC_DETAIL.PROPNUM
HAVING (((AC_DETAIL.SCENARIO)="RR4Q17_SEC"));
Is this possible?
THANKS!
When I join the queries I want all of the rows and columns from the first query, but I want to add to this column: Sum(AC_MONTHLY.[S751]+[S750]), LastOfS1306 from the 2nd query.
Not all of the rows from the first query will have a LastOfS1306 value.
Query 1
SELECT AC_PROPERTY.RSV_CAT, AC_MONTHLY.OUTDATE, Sum(AC_MONTHLY.S370) AS [Gross PRODUCT_1], Sum(AC_MONTHLY.S371) AS [Gross PRODUCT_2], Sum(AC_MONTHLY.S374) AS [Gross PRODUCT_3], Sum(AC_MONTHLY.S815) AS [Net PRODUCT_1], Sum(AC_MONTHLY.S816) AS [Net PRODUCT_2], Sum(AC_MONTHLY.S819) AS [Net PRODUCT_3], Sum(AC_MONTHLY.s1062) AS [Net Opx ($)], Sum(AC_MONTHLY.[S751]+[S750]) AS [Net Cap Investment ($)]
FROM AC_MONTHLY INNER JOIN AC_PROPERTY ON AC_MONTHLY.PROPNUM = AC_PROPERTY.PROPNUM
GROUP BY AC_PROPERTY.RSV_CAT, AC_MONTHLY.OUTDATE, AC_MONTHLY.SCENARIO
HAVING (((AC_MONTHLY.SCENARIO)="RR4Q17_SEC"));
Query 2
SELECT AC_DETAIL.SCENARIO, AC_DETAIL.PROPNUM, Max(AC_MONTHLY.OUTDATE) AS MaxOfOUTDATE, Last(AC_DETAIL.S1306) AS LastOfS1306
FROM AC_DETAIL INNER JOIN AC_MONTHLY ON (AC_DETAIL.SCENARIO = AC_MONTHLY.SCENARIO) AND (AC_DETAIL.PROPNUM = AC_MONTHLY.PROPNUM)
GROUP BY AC_DETAIL.SCENARIO, AC_DETAIL.PROPNUM
HAVING (((AC_DETAIL.SCENARIO)="RR4Q17_SEC"));
Is this possible?
THANKS!