Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show multi-activities and determine who paid most. (Access 97 SR2)

    Hello all,

    I'm using Access 97.

    I have 2 tables in a One-to-Many relationship,
    1. t_PO_Contract - PK = Num_PO_Con / One
    2. t_CLIN_and_FSN - Key = Num_PO_Con2 / Many

    These contracts can be processed by more than one activity (DFAS or DAFS).

    Example: Num_PO_Con = 523204
    This particular contract (t_PO_Contract) has 117 associated records in t_CLIN_and_FSN,
    Of those 117 records, 107 were processed by DFAS with a total CLIN amount of $89,465.50, 10 were processed by DAFS with a total CLIN amount of $9,876.00. Therefore, DFAS would be the "owner" of this contract. The number of CLIN's is not the determining factor, the dollar amount is (AMT_CONTRA).
    The field that identifies the activity is called...Activity (smile), and is in the t_CLIN_and_FSN table.

    I need a query that will;
    A. Single out any contracts that have been processed by BOTH DFAS and DAFS
    B. Count how many contract lines (CLIN) were proccessed by DFAS and DAFS
    C. Sum the amounts of the contract lines for DFAS and DAFS for this particular contract
    D. Determine which activity paid the most towards that contract. (I don't know if the MAX function would work here or not.)

    In this database, I have 9007 distinct contracts, and 37,559 CLIN's. Not every contract has CLIN's that have been processed by BOTH activities DFAS and DAFS. Just need to concentrate on those, and perform the steps as above.

    Below is the SQL of a query that includes both tables. Thanks in advance for any help or suggestions.

    Bob in Indy

    SELECT t_PO_Contract.NUM_PO_CON, [PIIN1_DOD_] & " " & [PIIN2_PROC] & " " & [PIIN3_DELI] AS Contract_Number, t_PO_Contract.DATE_PURCH, t_CLIN_and_FSN.NUM_CONTRA, t_CLIN_and_FSN.NUM_JOB_OR, t_CLIN_and_FSN.AMT_CONTRA, t_CLIN_and_FSN.FISCAL_STA, t_CLIN_and_FSN.Activity
    FROM t_PO_Contract RIGHT JOIN t_CLIN_and_FSN ON t_PO_Contract.NUM_PO_CON = t_CLIN_and_FSN.NUM_PO_CO2
    WHERE (((t_CLIN_and_FSN.Activity)="DAFS" Or (t_CLIN_and_FSN.Activity)="DFAS"));

    Here is the SQL that will show amounts for DFAS and DAFS, but it only seems to work if I know a contract ID number (Num_PO_Con) that definately has both DFAS and DAFS activity in the CLIN's (from t_CLIN_and_FSN).

    SELECT qJoin_Contract_and_Clin_FSN.NUM_PO_CON, qJoin_Contract_and_Clin_FSN.Contract_Number, Sum(qJoin_Contract_and_Clin_FSN.AMT_CONTRA) AS SumOfAMT_CONTRA, qJoin_Contract_and_Clin_FSN.Activity
    FROM qJoin_Contract_and_Clin_FSN
    GROUP BY qJoin_Contract_and_Clin_FSN.NUM_PO_CON, qJoin_Contract_and_Clin_FSN.Contract_Number, qJoin_Contract_and_Clin_FSN.Activity;

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Show multi-activities and determine who paid most. (Access 97 SR2)

    Perhaps this will do what you want:
    1. <LI>Create a query that sums the amount for all contracts processed by DAFS:

      SELECT t_CLIN_and_FSN.Num_PO_Con2, Sum(t_CLIN_and_FSN.AMT_CONTRA) AS DAFS_Amount
      FROM t_CLIN_and_FSN
      WHERE (((t_CLIN_and_FSN.Activity)="DAFS"))
      GROUP BY t_CLIN_and_FSN.Num_PO_Con2;

      Let's name it qTest_DAFS.

      <LI>Create a similar query for DFAS:

      SELECT t_CLIN_and_FSN.Num_PO_Con2, Sum(t_CLIN_and_FSN.AMT_CONTRA) AS DFAS_Amount
      FROM t_CLIN_and_FSN
      WHERE (((t_CLIN_and_FSN.Activity)="DFAS"))
      GROUP BY t_CLIN_and_FSN.Num_PO_Con2;

      Name it qTest_DFAS.

      <LI>Create a third query that joins the previous two and determines the owner. Since it uses an inner join, only contracts processed by both DAFS and DFAS are returned:

      SELECT qTest_DAFS.Num_PO_Con2, IIf([DAFS_Amount]>[DFAS_Amount],"DAFS","DFAS") AS Owner, qTest_DAFS.DAFS_Amount, qTest_DFAS.DFAS_Amount
      FROM qTest_DAFS INNER JOIN qTest_DFAS ON qTest_DAFS.Num_PO_Con2 = qTest_DFAS.Num_PO_Con2;
    If you wish to add information from t_PO_Contract, you can add that table to the design of the third query, joining it on Num_PO_Con / Num_PO_Con2.

  3. #3
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show multi-activities and determine who paid most. (Access 97 SR2)

    Hans,

    Works like a charm. Fast, too!! Went through over 50K records in 2 seconds. I had another query that took 130 seconds.

    It's nice to know that there is such great help here.

    Regards,

    Bob in Indy

Posting Permissions

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