No keys to link

[Slonoma]

This is getting really annoying and I cannot figure out a solution.

I have 2 tables like the following:

To | From | Date | QTY
1 2 6-14 3
1 2 6-15 8
1 4 6-14 2

and

To | From | Date | QTY
1 2 6-14 4
1 2 6-15 7
1 4 6-14 1


In the report I need to Group by To, From and Date.

The QTY needs to be the Sum of both the QTY fields where To, From, and Date all match.

Anyone have any ideas about how I can do this?

Thanks,

Slo-No

 

[Slonoma]

Thought I should add this for easier readablility.

The report should look similar to:

To | From | Date | Total QTY
1 2 6-14 7
1 2 6-15 15
1 4 6-14 3

 

[lyanch]

How are you bringing the data in? A union query would be easiest, then you would just use a sum.

Lisa

 

[Slonoma]

Im using CR 9 and oracle. Could you give me an example of a union query that would be applicable for this?

Thanks for your help Lyanch!

Slo-No

 

[Turkbear]

Hi you could create a view:

Create or Replace view both_tables_v as
Select * from Table1
UNION 
Select * from Table2;

Then use the view for your report.

 

[Slonoma]

Turkbear,
This would automatically add the Qty fields together?

 

[Slonoma]

Nevermind,
It grabs only distinct records as if the tables were placed on top of each other. I get it now. Thanks for your help!

Slo-No

 

[Turkbear]

Hi,
Right,
UNION eliminates duplicates, UNION ALL would include them ( but not sum them up)