Show Sale and No-Sale Accounts

[jgalich]

Hi.

First time post here.

I use Access 2007 and am trying to generate what I think is a relatively simple query to show all accounts, whether they have sales history or not. Customer information is housed in a table called TBLCUSTOMERS; sales data is housed in a table called Net_Sales_2010-Present; and there is a third table call Territory_Attributes that we use to determine sales region.

I built the query as shown in the screen grab below, using an outer join on Account_Number with "Customers" and "Net Sales" thinking this will give me No Sale and Sold accounts. Still, I am only getting sold accounts:





Here's the SQL:

SELECT Territory_Attributes.Sub_Region, MARKETING_REPORTS_TBLCUSTOMERS.REP_NUMBER, MARKETING_REPORTS_TBLCUSTOMERS.ACCOUNT_NUMBER, MARKETING_REPORTS_TBLCUSTOMERS.BCUST_NAME, "20" & Left([pdate],2) AS [Year], Sum([Net_Sales_2010-Present].BILLED_AMOUNT) AS SumOfBILLED_AMOUNT
FROM (MARKETING_REPORTS_TBLCUSTOMERS INNER JOIN Territory_Attributes ON MARKETING_REPORTS_TBLCUSTOMERS.REP_NUMBER = Territory_Attributes.ter) LEFT JOIN [Net_Sales_2010-Present] ON MARKETING_REPORTS_TBLCUSTOMERS.ACCOUNT_NUMBER = [Net_Sales_2010-Present].ACCT
WHERE ((([Net_Sales_2010-Present].PDATE)<150701))
GROUP BY Territory_Attributes.Sub_Region, MARKETING_REPORTS_TBLCUSTOMERS.REP_NUMBER, MARKETING_REPORTS_TBLCUSTOMERS.ACCOUNT_NUMBER, MARKETING_REPORTS_TBLCUSTOMERS.BCUST_NAME, "20" & Left([pdate],2)
HAVING (((Territory_Attributes.Sub_Region)="NA North"));

Just to see, I tried the other two join variations to no avail. Is what I want not possible, or am I doing something wrong?

Thanks for the assistance!



John Galich



Just to see, I tried the other two join variations to no avail. Is what I want not possible, or am I doing something wrong?

Thanks for the assistance!

John Galich

[ranman256]

You could do it in 2 querys and put them together in a single UNION query.
the one above gives you SOLD,
then make another with no sales.
Put them together as :

select * from qsSold
union
select * from qsNoSale