Using MS Access 97 SR2
Greetings,
I have been tasked to devise a method of determining which activity has made the most payments against a contract. Most contracts are paid by one activity against one or several line items on the contract. However, sometimes there may be two or more activities making payments against the same contract, but on different line items. These activities are defined by their Fiscal Station Numbers (FSN). I have created a table with the applicable FSN's, and added a field called ACTIVITY. I then entered the name of the activity. Currently, there are only two activities to track. Activity A and Activity B.
The data are divided into three tables:
TABLE 1 - PO_Contract
Fields:
PIIN1_DOD_Activity_Code
PIIN2_Procurement_ID
PIIN3_Delivery_Order_Number
Misc_Pay_Indicator
Num_PO_Contract_Key THIS IS THE KEY FIELD
Date_Purchase_Order_Received
TABLE 2: PO_Contract_Line_Item
Fields:
Num_Contract_Line
Num_Job_Order
Amt_Contract_for_Acctclas
Num_PO_Contract_Key THIS IS THE KEY FIELD
TABLE 3: Job_Order
Fields:
Num_Job_Order
Fiscal_Station_Number
Currently, I have a One-to-Many relationship between TABLE 1-Num_PO_Contract_Key (One side) and TABLE 2-Num_PO_Contract_Key (Many side). I have TABLE 2-Num_Job_Order (One side) joined to TABLE 3-Num_Job_Order (Many side).
Somehow, I need to have a query that will only display CONTRACTS that have more than one ACTIVITY making payments against it. I then need a method of somehow assigning the ACTIVITY whose payments were the highest dollar amount to be permanently assigned to that particular CONTRACT. I had thought of adding a field to either TABLE 1 or to TABLE 2, perhaps calling it ASSIGNED.
I have attached a file called SampleInfo.Xls. It contains sample data that should illustrate my goal. As examples, there are three contracts that have multiple lines, and have been paid by both Activities A and B. In those three contracts, Activity A happened to have paid the most, therefore they need to be "assigned" those three contracts.
There are two sheets with information: SAMPLEINFO and ILLUSTRATIONS.
I used a MAKE TABLE query to create yet a third table, called t_CLIN_and_FSN. This table was created by combining the fields from TABLE 2 and TABLE 3 above. The query now consists of TABLE 1 and this new table ( t_CLIN_and_FSN), with a One-to-Many join at the Num_PO_Contract_Key fields. This new table is joined only in the query. It is not joined in the RELATIONSHIPS window.
Here is the SQL from this query:
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)="Activity B" Or (t_CLIN_and_FSN.Activity)="Activity A"));
Thanks in advance !!!!!
Bob in Indy
PS: It would be helpful if I could get the query to show only those Contracts and Lines that have been paid by both Activity A and Activity B. Would sure narrow down the 50K-plus records I have.
Greetings,
I have been tasked to devise a method of determining which activity has made the most payments against a contract. Most contracts are paid by one activity against one or several line items on the contract. However, sometimes there may be two or more activities making payments against the same contract, but on different line items. These activities are defined by their Fiscal Station Numbers (FSN). I have created a table with the applicable FSN's, and added a field called ACTIVITY. I then entered the name of the activity. Currently, there are only two activities to track. Activity A and Activity B.
The data are divided into three tables:
TABLE 1 - PO_Contract
Fields:
PIIN1_DOD_Activity_Code
PIIN2_Procurement_ID
PIIN3_Delivery_Order_Number
Misc_Pay_Indicator
Num_PO_Contract_Key THIS IS THE KEY FIELD
Date_Purchase_Order_Received
TABLE 2: PO_Contract_Line_Item
Fields:
Num_Contract_Line
Num_Job_Order
Amt_Contract_for_Acctclas
Num_PO_Contract_Key THIS IS THE KEY FIELD
TABLE 3: Job_Order
Fields:
Num_Job_Order
Fiscal_Station_Number
Currently, I have a One-to-Many relationship between TABLE 1-Num_PO_Contract_Key (One side) and TABLE 2-Num_PO_Contract_Key (Many side). I have TABLE 2-Num_Job_Order (One side) joined to TABLE 3-Num_Job_Order (Many side).
Somehow, I need to have a query that will only display CONTRACTS that have more than one ACTIVITY making payments against it. I then need a method of somehow assigning the ACTIVITY whose payments were the highest dollar amount to be permanently assigned to that particular CONTRACT. I had thought of adding a field to either TABLE 1 or to TABLE 2, perhaps calling it ASSIGNED.
I have attached a file called SampleInfo.Xls. It contains sample data that should illustrate my goal. As examples, there are three contracts that have multiple lines, and have been paid by both Activities A and B. In those three contracts, Activity A happened to have paid the most, therefore they need to be "assigned" those three contracts.
There are two sheets with information: SAMPLEINFO and ILLUSTRATIONS.
I used a MAKE TABLE query to create yet a third table, called t_CLIN_and_FSN. This table was created by combining the fields from TABLE 2 and TABLE 3 above. The query now consists of TABLE 1 and this new table ( t_CLIN_and_FSN), with a One-to-Many join at the Num_PO_Contract_Key fields. This new table is joined only in the query. It is not joined in the RELATIONSHIPS window.
Here is the SQL from this query:
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)="Activity B" Or (t_CLIN_and_FSN.Activity)="Activity A"));
Thanks in advance !!!!!
Bob in Indy
PS: It would be helpful if I could get the query to show only those Contracts and Lines that have been paid by both Activity A and Activity B. Would sure narrow down the 50K-plus records I have.
Last edited:
