Show all records

[Ali29J]

Hi All

I am trying to show data from 2 tables, joined by a customer link, at present i can only show all records from one or the other through join properties, but i would like it to return all values from both tables whether they are equal or not.

reason:
Forecast Table - Forecast sales is enterred against a customer
Orders Table - Actual sales enterred against customer

So i would like to show actual sales even if none forecasted, and forecast even if no sales achieved

Can anyone help??

Ali

 

[PHV]

What is your actual SQL code ?
I guess that you may play with outer joins and union.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ali29J]

HI PHV

SQL code is as follows:

SELECT DISTINCTROW Format$([Fore-Nov].Date,'mmmm yyyy') AS [Date By Month], (Sum(Nz([Fore-Nov].calculatedtotal1,0))+Sum(Nz([Fore-Nov].calculatedtotal2,0))+Sum(Nz([Fore-Nov].calculatedtotal3,0))+Sum(Nz([Fore-Nov].calculatedtotal,0))) AS [Sum USD], [Fore-Nov].Customer, Forecast.Customer, Forecast.Nov
FROM [Fore-Nov] INNER JOIN Forecast ON [Fore-Nov].Customer = Forecast.Customer
GROUP BY Format$([Fore-Nov].Date,'mmmm yyyy'), [Fore-Nov].Customer, Forecast.Customer, Forecast.Nov, Format$([Fore-Nov].Date,'yyyymm')
ORDER BY Format$([Fore-Nov].Date,'yyyymm');

at present i have 2 chices either show all records from Fore-Nov or all records Forecast, not both, i need to show all records from both....

 

[lespaul]

have you looked into the UNION clause?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Ali29J]

OK I have advanced further now, incorporated segregated queries into one, SQL as below, but i have tried to use the UNION command, but it prompts the following "The number of columns in the two selected tables or queries of a union do not match"

am i using this command wrong? i must admit i am not 100% clear how this works... appreciate any further assistance...

SELECT DISTINCTROW Format$(ForeBase.Date,'mmmm yyyy') AS [Date By Month], (Sum(Nz(ForeBase.calculatedtotal1,0))+Sum(Nz(ForeBase.calculatedtotal2,0))+Sum(Nz(ForeBase.calculatedtotal3,0))+Sum(Nz(ForeBase.calculatedtotal,0))) AS [Sum USD], ForeBase.Customer, Forecast.Jan, Forecast.Feb, Forecast.Mar, Forecast.Apr, Forecast.May, Forecast.Jun, Forecast.Jul, Forecast.Aug, Forecast.Sep, Forecast.Oct, Forecast.Nov, Forecast.Dec
FROM ForeBase, Forecast
GROUP BY Format$(ForeBase.Date,'mmmm yyyy'), ForeBase.Customer, Forecast.Jan, Forecast.Feb, Forecast.Mar, Forecast.Apr, Forecast.May, Forecast.Jun, Forecast.Jul, Forecast.Aug, Forecast.Sep, Forecast.Oct, Forecast.Nov, Forecast.Dec, Format$(ForeBase.Date,'yyyymm')
ORDER BY Format$(ForeBase.Date,'yyyymm');
UNION SELECT Customer FROM Forecast;

 

[lespaul]

A union command only works when the number of fields in the two select statement are the same:

SELECT Field2, Field4, Field6 FROM Table1
UNION
SELECT Feild1, Field3, Field5 FROM Table2

why don't you show us some sample data from the Forecast and ForeBase Tables and your expected results from those tables.

When you said you had two queries, one returning forecasted information and one returing actual sales/order information I assumed that one query returned the forecast and one returned the sales and you were trying to return that information combined. Why are you trying to union the Customer?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Ali29J]

Hi This is ultimately what i m looking for, but as you will tell from my code the calcs are nt to simple:

Customer Fore-Jan Jan-Sales Fore-Feb Feb-Sales
Customer 1 10 0 10 0
Customer 2 0 10 0 20
Customer 3 12 13 11 2

Fore = from forecast sales, entered into table directly
Sales = Calculated from a table PO using the query Forebase

Possibly i am going about this wrong....