In our Project Database I have 2 tables. Active Project & Items for Project Database.
Active Project holds all the "live" projects. Items for Project Database, subsequently contains all items that have occurred for each project. IFPD is more like a dumping ground.
In AP we have the following fields:
The unique field is ProjectNumber. The field "Split" also contains project numbers. It acts as an indicator to let us know that services are split between Project A & Project B.
Now in reports there is a Year To Date by Request report in which the person gathering information regarding a particular project enters the project number and a report is generated off a query based upon the AP & IFPD tables.
What we would like to do, is get the report to look up the project number and if the project has been "split", find the information for both projects and produce the report with all the applicable info.
hmmm, tricky. A *clean* way to do it is to take it up a level.
Essentially, a "Split" means that there are several "Projects" that could be related together, correct?
If that is so, then on you are standing on the "Many" side of a "One-to-Many" relationship, so you need to create the "One" side. Create a Master Project table. In the MP table, have a key field (AutoNumber) and a Created field (Date/time) - make the second field default to "=Now()".
In your AP table, add a field to hold the MP_Key. If a project is split, create a new record in AP like you do now, but assign it the same MP_Key. Use the "Split" field for reference only (you may want to get rid of it).
Now, when you call up a project number, you can search, query and report every other project with the same MP_Key.
You may want to move information that is common between projects that are split (such as Customer info) to the MP table so that you are not duplicating data.
On the user level, you won't even see the MP table - when a new project is created, have some code create a new record on the MP table and place the MP_Key value in the AP table. When a project is split, have some code copy the MP_Key into the new project record. When you report on a project, have the filter grab all records from AP with a matching value in the MP_Key field.
This allows you to split a project as many times as you want and manage each project seperately, but have them related together.