Joins and Links and avoiding a view table

[TerriO]

Two Tables

Budget table
CO AV ACC AMT
9100 123 1000 100
9100 234 2000 50

Actuals Table
CO AV ACC AMT
9100 123 1000 102
9100 234 1500 40

Any help on attempting to have the data return for line 2 in the budget and Actuals table even though there isnt a budget and vice versa? Ultimately my result set should have 3 lines of data..
I have tried different join types and would really like to avoid using a view table or resorting to an Access table(which really isnt an option)

THANKS!
Terri

 

[dgillz]

Not a lot to go on. How do you have these links set up now, and what is your report doing that you do not want it to do? Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[synapsevampire]

Are you saying that you want to combine these two tables, and eliminate duplicates?

Use a Union query:

Build the report to return the rows from the Budget table only (do not include the Actuals table).

Select Database->Show SQL Query and under the existing SQL that CR created place:

UNION

Now copy and paste the SQL from above the UNION to below the UNION and change the table name to Actuals in the query.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[TerriO]

Based on the ACC in both tables I need to retrieve 3 lines of data.. one is in one table and not the other I need to capture them regardless of whether data exists in the other table.. I have tried various links and not able to isolate them to three lines only.. Terri

 

[dgillz]

Which one in table A do you need? Also, how is it that you would need the other 2 lines of data "regardless of whether data exists in the other table"? What if data DOES exist in the other table? How would the report be different in that case?

Please respond with a LOT more detail. They do not charge by the word here at tek-tips! Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[synapsevampire]

Did you try a Union? It does precisely what you're asking.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com