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

Topic

New Reply

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;

Reply | Quote

Replies

Perhaps this will do what you want:

1. 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.

2. 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.

3. 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.

Reply | Quote

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

Reply | Quote