Merging 2 tables into 1

[djieon]

Hi I have similar data coming from 2 different sql tables, so I created a details a and a details b section in my report. The data I am expecting is coming back however I want to be able to sort this data on a date from 2 different tables,

Can I write a formula to look at the date in details a and then the date in details b?

Thanks in advance

David.

 

[IanWaterman]

Not really sure what you are trying to achieve. Have you joined the tables?

You might be better using a command and unioning the tables together.

something like

Select Field1, field2, Datefield
from table1
union
Select Field3, field4, Datefield
from table2

Using Union will only bring in non duplicated fields from table 2.

If you want to include duplicates use UNION ALL

You can then sort on the common date field

Ian

 

[djieon]

Hi Ian, thanks for your reply.

My data is structured as

table 1
item_unique_ID

Table 2 - data new
item_unique_ID
date created
item number

table 3 - data old
item_unique_ID
date created
item number

Table 2 has data from 2000 to 2008 and table 3 has 2008 onwards.

What I want to do is create a seamless report so that to the reader of the report it looks like all the data comes from the same place. I want it to sort by the date created.

At the moment I have put table 2 into details a and table 3 into details b.

Is there a better way of doing this?

Thanks.

 

[IanWaterman]

The best way will be to use a command and Union data as I explained.

Splitting details will not give you waht you want

Use a command like

select
item_unique_ID, date created, item number
from Table2
union all
select
item_unique_ID, date created, item number
from Table3

Ian

 

[djieon]

Hi Ian, thanks for the reply.

Do i do that in SQL Expression Name? I dont have access to the SQL database so all of my work has to be done in the report....

 

[lbass]

You can't do this in the SQL expression area. Instead you should open a new report->select your datasource->above your table list, select "add command" and then enter something like:

select
item_unique_ID, date created, item number
from Table 1 inner join Table2 on
Table 1.item_unique_ID = Table2.item_unique_ID
where <selection criteria here>
union all
select
item_unique_ID, date created, item number
from Table 1 inner join Table3 on
Table 1.item_unique_ID = Table3.item_unique_ID
where <selection criteria here>

-LB