Left Join 2 Queries (1 Viewer)

hbusche

New member
Local time
Today, 06:25
Joined
Nov 15, 2017
Messages
6
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!
 

plog

Banishment Pending
Local time
Today, 07:25
Joined
May 11, 2011
Messages
11,613
Yes, save the queries you have. Lets call them qTop and qBottom. You said you know how to build queries with access design view--so do that. Bring in qTop and qBottom into a new query, JOIN them appropriately and bring in all the fields you want to show.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:25
Joined
Feb 19, 2002
Messages
42,981
Observations.
1. Your column names appear to contain data. That is a symptom of a spreadsheet and is never seen in a properly designed relational database.
2. Object names should never include special characters. Use only the upper and lower case letters, the numbers and the underscore if you need a separator.

Names like -- Net Opx ($) -- will be nothing but problems in forms and vba. Access will see this name as -- Net_Opx____ -- How's that going to work for you? Try creating something with
Net Opx ($) AND
Net Opx (%)

Access will generate the same internal name by replacing all offending characters with the underscore.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:25
Joined
May 7, 2009
Messages
19,175
I dont see any common field (same datatype and content) on both queries.
 

Users who are viewing this thread

Top Bottom