Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2008
    Posts
    2

    Question Unanswered: Building a project tracking database

    I want to build a database where I have the following tables created:
    -Contract Numbers
    -Engineering Status-->with subcontracts of the main contract in Contract Numbers table with dates and quantity
    -Procurement Status-->with subcontracts of the subcontracts in Engineering Status table with dates and quantity
    -Fabrication Status-->with subcontracts of the subcontracts in Engineering Status table with dates and quantity

    I want to create one report for a specific period (weekly, monthly ....) that displays the total quantities from each table. Taking into consideration that a specific date might not exist in every Status table.

    How do I create the links between tables and produce the report with the correct summations?

    Thanks.
    Last edited by Zakarian; 04-10-08 at 11:33. Reason: spelling mistake!!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    How do I create the links between tables and produce the report with the correct summations?
    This is the only part I understand.

    You create links between tables by putting a common field in each table and then using Relationships.

    As for the rest, it's impossible to say. Have you created your database yet? Sounds to me like you are still in the early design planning phase, yet you are asking how to produce a report?? So. I'm confused.

    If you have a database in existence, you need to show us your ERD.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Feb 2007
    Posts
    348
    hmm so it recursive? You have a contract with engineering, procurement and fabrication, each of which could have their own contract, is that right?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    It's impossible to say because I don't really understand your requirements.

    You can have one contract that relates to many elements... of any kind. You might have a ContractElement table that holds the contract ID and the element you are referring to (engineering, procurement or fabrication) along with the quantity and date of that element. You could then have multiple records in this table to reflect the differing elements of each contract.

    Whether this is the right approach or not remains to be determined, because again, I don't fully understand your goals.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Apr 2008
    Posts
    2

    Talking Attached actual database file for easy reference

    From the attached database I want to create a report showing the following:
    Contract Number, total amount to produce
    Week No.
    discipline (Enginnering, Procurement, Fabrication), planned amount, actual amount
    discipline, total planned amount, total actual amount
    discipline, cummulative planned amount, cummulative actual amount

    Knowing that not for every planned date there is actual amount produced.
    Also, each discipline consits of further breakdown of the contract that will need to be summed to get the actual amounts.

    I hope this clarifies the issue I am presenting.
    Thanks for your assistance.

    Zakarian
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ah .... I thought you were planning your database build, not extending an existing one.

    Since you're database already exists, all I can say is that you include all the tables that you need to produce the numbers you want to see in the report, plus the ones you don't use data from in order to maintain the existing relationships.

    By the sounds of your needs and looks of that database (without spending lots of time in analysis) you're going to need to use many queries to get to your desired figures and then relate those queries together.

    Oh... and don't forget to compact and repair. I shaved 10MB off the file by doing so
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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