Show multi-activities and determine who paid most...?

[indyaries]

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;

 

[Jon K]

Try these two queries (type/paste each in the SQL View of a new query):

qryOne:-
SELECT NUM_PO_CON2,
(Select Count(*) from t_CLIN_and_FSN where Activity="DFAS" and Num_Po_Con2=a.Num_Po_Con2) AS DFAS,
(Select Count(*) from t_CLIN_and_FSN where Activity="DAFS" and Num_Po_Con2=a.Num_Po_Con2) AS DAFS
FROM t_CLIN_and_FSN AS a
WHERE Activity in ("DFAS","DAFS")
GROUP BY NUM_PO_CON2;

qryTwo:-
SELECT a.Num_Po_Con2, Last(b.DFAS) AS CLIN_DFAS, Last(b.DAFS) AS CLIN_DAFS,
Sum(IIf([Activity]="DFAS",[Amt_Contra],0)) AS Amt_DFAS,
Sum(IIf([Activity]="DAFS",[Amt_Contra],0)) AS Amt_DAFS,
IIf(Amt_DFAS=Amt_DAFS,"Same",IIf(Amt_DFAS>Amt_DAFS,"DFAS","DAFS")) AS Who_Paid_Most
FROM t_CLIN_and_FSN AS a INNER JOIN qryOne AS b ON a.NUM_PO_CON2=b.Num_Po_Con2
WHERE b.DFAS>0 and b.DAFS>0
GROUP BY a.Num_Po_Con2;


Run the second query.

Hope it helps.

 

[indyaries]

Sorry...didn't work

Jon,

I pasted the two SQL statements into two different queries, but it's not working.

I've created a stripped-down version of the data. It includes;

A. 2 contracts with both DFAS and DAFS in the Activity column - POID 528864 and 523204.

B. 3 contracts with DFAS as the activity

C. 3 contracts with DAFS as the activity

D. 2 contracts with no activity

Note that there are some contracts that have either DFAS or DAFS, but also have some lines (CLIN) that have no activity.

Thanks again for your help !!!!!

 

[Jon K]

I found that instead of being NUM_PO_CON2 as stated in your post, the field name in table t_CLIN_and_FSN was actually NUM_PO_CO2.

After renaming the field as NUM_PO_CON2 in the table and pasting the two queries again in the attached DB, the queries worked.

Hope the query results are what you required.


Note. Because the queries involve subqueries and iif() functions, running them on a large table may take time.

 

[indyaries]

Outstanding !!

Jon,

It indeed worked like a charm!!

My apologies on the typo of that field name. I have several books on Access, and yet none of them really explain SQL such as what you provided.

So that I may learn from this, may I ask for an explaination on what is happening in the SQL you provided?

Also, are there any books, etc., available where I may learn how to write SQL queries?

Hoping you and yours have a wonderful New Years holiday.

Sincerely,

Bob

 

[raskew]

Jon K-

Very elegant solution!

Got to playing around with it and I think the following modification captures all the required info in one query.

SELECT a.NUM_PO_COn2, (Select Count(*) from t_CLIN_and_FSN where Activity="DFAS" 
and Num_Po_Con2=a.Num_Po_Con2) AS DFASC, (Select Count(*) from t_CLIN_and_FSN where 
Activity="DAFS" and Num_Po_Con2=a.Num_Po_Con2) AS DAFSC, (Select Sum([Amt_Contra]) from 
t_CLIN_and_FSN where Activity="DFAS" and Num_Po_Con2=a.Num_Po_Con2) AS [DFAS$], (Select 
Sum([amt_contra]) from t_CLIN_and_FSN where Activity="DAFS" and Num_Po_Con2=a.Num_Po_Con2) 
AS [DAFS$], IIf((Select Sum([Amt_Contra]) from t_CLIN_and_FSN where Activity="DFAS" and 
Num_Po_Con2=a.Num_Po_Con2)>(Select Sum([amt_contra]) from t_CLIN_and_FSN where 
Activity="DAFS" and Num_Po_Con2=a.Num_Po_Con2),"DFAS","DAFS") AS Owner 
FROM t_CLIN_and_FSN AS a 
WHERE (((a.Activity) In ("DFAS","DAFS"))) 
GROUP BY a.NUM_PO_COn2 
HAVING ((((Select Sum([Amt_Contra]) from t_CLIN_and_FSN where Activity="DFAS" and 
Num_Po_Con2=a.Num_Po_Con2))>0) AND (((Select Sum([amt_contra]) from t_CLIN_and_FSN where 
Activity="DAFS" and Num_Po_Con2=a.Num_Po_Con2))>0));

 

[Jon K]

Raskew,
Excellent query! Your query is very much superior to mine in running speed. On my PC, your query takes only 0.05 second to run on the 766 records in the table, but my queries take over 3 seconds.


Bob,
Obviously you should use Raskew's query.

My first query counts the DFAS and the DAFS in the Activity field for each Num_Po_Con2 in the table by means of two subqueries (i.e. select statements in brackets.)

In Num_Po_Con2=a.Num_Po_Con2, the first Num_Po_Con2 is the Num_Po_Con2 in the subquery, while the latter a.Num_Po_Con2 refers to the Num_Po_Con2 in the main query. (As the same table is used in both the subqueries and the main query, the table in the main query uses the alias a for distinction.)

The second query then joins table t_CLIN_and_FSN with those Num_Po_Con2s from the first query that have both the DFAS and DAFS counts >0 (that is those Num_Po_Con2s that have been processed by both DFAS and DAFS), and sums the DFAS Amount and DAFS Amount. It then uses the iif() functions to compare the two sums and determine who paid most. (Here the aliases a and b are used as shorthand symbols to avoid having to type the long table name and query name.)

Hence, the queries carry out the counting, the summing, and the comparison.


Raskew groups the records in the table by Num_Po_Con2 and ingeniously uses a Having Clause to select only those Num_Po_Con2s whose sums of DFAS Amount and DAFS Amount are both > 0.

Raskew's approach has two benefits over mine. Firstly, the Having Clause in conjunction with Group By drastically reduces the number of records involved. Secondly, it eliminates the need to join the table to another query. These account for the great improvement on performance.


The books that I have only cover the SQL language slightly and the examples given in them are very basic. I often look up the Help in Access97.

 

[indyaries]

WOW !!!!!

Jon and Raskew,

Thank you both !!

I ran both samples (Jon & Raskew) against the main database (22 Meg). Jon, your wonderful example ran in 130 seconds; Raskew's ran in 5 seconds.

I am perfectly happy with BOTH examples, and the help you both have offered.

Alas, I thought the major portion of reinventing the wheel for this project was over. I discovered this morning that there is much more to do.

I think I can make it work as they need it to, but it's good to know that there are very knowledgeable and helpful people out there to ask!

Once again, thank you BOTH !!

Bob in Indy

 

[raskew]

Indyaries & Jon_K:

All the kudos go to Jon_K--he setup the solution and I just modified it! Have never been able to internalize the "select ... as a.xxx" approach. (But, am able to modify it if someone else sets it up.)

I'm amazed at the difference in run time--we may have hit on something here!

Glad to hear it worked for you--and thanks to Jon_K.

Bob