Can you tie old Dept # to New Dept #? 2

[loveyoursite]

CRV10 - We are in the process of changing several hundred department numbers. Is there a way to tie the old dept data and the new dept data together for reporting? For example grouping by department, I would want the old department data included as part of the new department data.

Another Example: John's old dept # was 8010, his new is 9010. If I want to report John's YTD orders, sales or salary (just examples, could be anything that we need to tie together), how would I tie the two department numbers together as one result (Dept 9010) under John?

Any suggestions would be most appreciated.

 

[synapsevampire]

You should understand that the table layout is everything and that trying to describe a database with text is the long way there.

Provide technical information:

Database/connectivity used
Example data (show the tables/fields)
Expected output

Do you have a historical table for the old ones or another field in the same table?

If you have 2 tables, then I would create a Union query to accomplish this. If they are in the same table, then a Union may also work, or perhaps even adding the table in again, but understanding your database is key, and you've shared almost nothing about it's design.

-k

 

[loveyoursite]

Sorry - I'm not very technical with Crystal yet. The database tables are Oracle. Here is a sample of some of the fields in the Employee table:

EMPLOYEE.LAST_NAME
EMPLOYEE.FIRST_NAME
EMPLOYEE.DEPT
EMPLOYEE.JOB_CODE
EMPLOYEE.ADDRESS_LINE_1

Many other tables exist containing various types of information such as salary, hours worked, orders taken, etc.

The plan is to change affected employee records by entering the new dept nbr, then deactivating the old dept. nbr so that it can not be selected when entering new employees.

I have existing turnover reports, and hours and earnings reports that use dept as select criteria.

All of my reports use the EMPLOYEE table and the link to the other tables is usually by EMPLOYEE.NBR

There is a history table which will reflect the old and new dept for each employee changed.

If, for example, I want to report YTD sales by department, how would I tie the old dept to the new dept data?

I hope this helps some? Thanks for your assistance!

 

[Turkbear]

Hi,
I would not remove the existing dept_nbr:
Add a column to the master table (I assume, employee) called Old_dept_nbr and populate it with the existing dept_nbr..
Then you can use an Or statement to get both dept_nbrs involved in your queries..

Alternatively, you could create a new table that has just the employee info and current dept_nbr..link them to the changed table ( perhaps in a view) .
It is always a good idea to keep historical data when the linked files need it.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

I'll assume that the historical table will have some date to indicate when it was archived.

A Union All will allow for having all data by creating something like:

select 'Currentdata' DataSource, field1, field2 from realtimetable, orders
where realtimedata.orderid = orders.id
union all
select 'Archivedata' DataSource, field1, field2 from archivetable, orders
where archivetable.orderid = orders.id

Now you have all of the data in one recordset with an indicator of where each row came from.

If you have a dba, they can help you work this out, and the way that you use this SQL would be to use the Add Command functionality in CR 10.

-k