Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003

    Question Unanswered: Report/Query question.

    Hello everyone,

    I'm having an issue.

    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.

    Is this possible?

  2. #2
    Join Date
    Apr 2004
    outside the rim
    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.

    good luck
    have fun!

    Todd S.
    click to email

    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET

    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts