Is this possible in a query, if unique field then value = 1, else value = 0 (1 Viewer)

tucker61 · Jan 19, 2018

Excel sheet attached to try and show what i am trying to achieve.

The first 7 fields are exported straight out of access as a select query. the 8th field is a formula that i have to input into excel, and is a check for a unique reference and if unique give me a "1", if duplicated gives me a "0". =IF(G3=G2,"",1)

How can i replicate this formula is my query so i do not have to carry out any additional calculations in excel ?

bastanu · Jan 19, 2018

you can use a Unique Delivery: dcount("[YourField]","[YourTable]","[Deliveri_Refeence]= " & [Delivery_Reference]. The duplicates will have numbers higher than 1. If you really want your original Excel formula:
Unique Delivery: iif(dcount("[YourField]","[YourTable]","[Deliveri_Refeence]= " & [Delivery_Reference]>1,0,1)

Cheers,
Vlad

plog · Jan 19, 2018

Looking at your data, 'unique' is not the correct term. You've marked some Delivery_reference values as unique which clearly have duplicates (e.g. 6010217).

Looking at your sample data, it seems you want to mark the delivery_reference record with the lowest Cat_no value. For that you would need a subquery. Let's call the query that produced that data 'SubQuery1'. You would then call the below sql 'SubQuery2':

Code:

SELECT Delivery_Reference, MIN(Cat_No) AS LowestCatNo
FROM SubQuery1
GROUP BY Delivery_Reference

That identifies the records to mark. Next, you build the main query using both those sub-queries like so:

Code:

SELECT *.SubQuery1, Iif(IsNull(LowestCatNo)=False, 1) AS UniqueDelivery
FROM SubQuery1
LEFT JOIN SubQuery2 ON SubQuery2.Delivery_reference = SubQuery1.Delivery_reference AND SubQuery2.Cat_No=SubQuery1.LowestCat_No

Is this possible in a query, if unique field then value = 1, else value = 0 (1 Viewer)

tucker61

Registered User.

Attachments

bastanu

AWF VIP

plog

Banishment Pending

Similar threads

Users who are viewing this thread