Joined: 29 Dec 2009
Online Status: Offline
Posts: 2
Topic: IF-THEN statements and DAO? Posted: 29 Dec 2009 at 12:18pm
Problem: Numerous reports need to be manually updated every time this company hires a new employee or reassigns employee ID numbers.
Question: How can this process be automated using the databases we currently use (ProSystem fx Practice, Excel) and Crystal Reports 9?
Description: I have to update Crystal Reports at an accounting firm and have a limited technical knowledge of Crystal Reports and of programming languages. There are a hundred reports that use about 50 IF-THEN statements in the formula workshop to convert employee ID numbers into names. The ID number comes from the PROJECT database in a program called Prosystem fx Practice. When setting up ID numbers we also assign a First, Middle, and Last names, all of which have their own fields in the STAFF database, but I don't know how to connect the dots there. For example, a report searches the PROJECT database in Practice for employee ID numbers and then returns their assignments, but instead of showing their ID number, the report shows their name due to this formula: IF{ID}="#" THEN "NAME"
This company has about 50 employees and so there are currently as many IF-THEN statements in each report. One solution I would like to try and implement is replacing the IF-THEN statements in each report and instead link to a single Excel spreadsheet that has the same IF-THEN statements. That way, when a new employee ID number is assigned, I only have to edit one spreadsheet instead of 100 reports. However, while I can see the solution, I don't know the steps to reach it.
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 29 Dec 2009 at 1:18pm
I think you could replace the formula with a master excel list of employees. One column would be ID number which you would use to link to your other DB (project?). Another field would your staff name which would replace your IF then formula fields.
When you updat the excel spreadsheet the reports should have access to the new info so you only have to manage it in one location.
Joined: 29 Dec 2009
Online Status: Offline
Posts: 2
Posted: 30 Dec 2009 at 7:53am
I assume that this would be done in Database Expert when Creating A New Connection. Should I connect using "Access/Excel (DAO)" or "Database Files"? Most importantly, how do I link to just one column?
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
