IIF Statement Trouble

[skumar14]

Hello,

I was hoping someone could help me with this problem or let me know if it isn't possible. I have three fields Contract Number, Sustainable, and Total Amount. The sustainable field is a flag with 0 or 1. In my data set i keep on getting duplicates for the contract number because in some instances the flag is 0 and others its 1. I would like to create a new field "Final sustainable field" with the IIF statement that says if the contract number has the flag of 0 and 1 then make it 1 and leave all the other values as is. Is this possible, any help would be appreciated.

Thanks!

 

[Joe4]

I am not sure it is quite clear what you are trying to do. Could you post a small data sample, and then a sample of what your expected result from that sample data should look like?

Note that you can copy and paste from the query grid to get Access images here.

For example:

Main	Reject	Return	Expr1
8/27/2014	10/21/2014	10/22/2014	0
1/24/2015	1/25/2015
2/17/2015	4/18/2015

<tbody>
</tbody>

 

[skumar14]

Yes I'm sorry you're right that does sound confusing. As you can see in the bottom of the data set there are two identical contracts but one has the sustainable flag at 1 and the other at 0. I would like to create a new field "Final Sustainable" that says if the contract number's sustainable flag equals 1 and 0, then mark it as 1 and if not then mark it as 0.

Contract Number	Sustainable	Final Sustainable	Total Amount
7967	1		$287,453.30
8181	1		$61,953.78
8238	1		$4,318,130.33
8267	1		$276,700.94
8267	0		$3,621.32

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>

 

[Joe4]

OK, that's the "before" picture. Can you show me you desired "after" picture?

And a few other questions:
- What do you want to happen with Contract Numbers only have one record, and it has a 0 is Sustainable?
- Can a given Contract Number have more than one record that has a 1 is Sustainable, or more than one record with 0 is Sustainable?
If so, how should those be handled?

 

[skumar14]

Afterwards i'd like it to look like this(below). For the contract number that has one record i'd like it to keep its original flag. For example at the end of the dataset the one contract with the zero flag I want its final sustainable to be zero as well. And a contract number wouldn't have more than one record that has a 1 and a 0. The contract would either have one record with 1 or 0 or it would have two records with 1 AND 0.

Contract Number	Sustainable	Final Sustainable	Total Amount
7967	1	1	$287,453.30
8181	1	1	$61,953.78
8238	1	1	$4,318,130.33
8267	1	1	$276,700.94
8267	0	1	$3,621.32
7946	0	0	$3,495.60

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>

 

[Joe4]

You can create a calculated field in a query to get this. You can use the DMAX function to pull the maximum value of Sustainable for each Contract Number, i.e.

Final Sustainable: DMax("Sustainable","TableName","[Contract Number] =" & [Contract Number])

Here is a explanation of the DMAX function: MS Access: DMax Function

 

[skumar14]

I keep on trying but it just keeps running and either crashes or just won't stop querying. Maybe written it incorrectly?

Code: DMax("MfrContractSustainability","qSS0203SustainabilitySpendContactStatusRollupMFR","[Mfr_Contract_ID] =" & [MFR_CONTRACT_ID])

 

[Joe4]

Is your MFR_CONTRACT_ID field a text field or numeric field?

 

[skumar14]

It is a numeric field, also thank you for all your help with this Joe.

 

[Joe4]

OK, so I can duplicate your scenario, please do the following.
Create a basic query, where you just add your table and add the Contact Number, Sustainable, and Total Amount fields.
Then switch your query to SQL View, and copy and paste the SQL code from that query here.