evaluate multiple records as a group (1 Viewer)

MilaK

Registered User.
Local time
Yesterday, 18:25
Joined
Feb 9, 2015
Messages
285
Hello,

What is the best way to return ‘no’ in a query, if all of the alleles for the same marker are less than 1000, otherwise return ‘yes’? Please see the attached example.

Thank you very much,

Mila
 

Attachments

  • markers.jpg
    markers.jpg
    14.8 KB · Views: 61

Frothingslosh

Premier Pale Stale Ale
Local time
Yesterday, 21:25
Joined
Oct 17, 2012
Messages
3,276
You'll need to create a function that accepts a marker value, pulls the value for each allele, and goes through that recordset and eventually returns yes or no based on your criteria. The easiest way would probably just be to pull up any records with value greater than 1000, and return yes if there are any records, and no if there are not.

I would include that in a query that returns a result for each marker (field 1 is Marker, field two is include, with Include being the function's returned value). Then join that query to your main query, if you want, showing ID, Marker, Allele, Value, and Include.

Also, be aware that ID and Value are reserved words in Access, and using them as field names will cause you no end of difficulties in the future. I'd suggest adding something to them; in an Employees table, for example, you'd use EmployeeID instead of ID.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:25
Joined
Oct 29, 2018
Messages
21,358
Hi. I wonder if this will work...
Code:
Include: IIf(DCount("*","TableName","[value]>=1000 AND [marker]='" & [marker] & "'")>0,"yes","no")
 

Frothingslosh

Premier Pale Stale Ale
Local time
Yesterday, 21:25
Joined
Oct 17, 2012
Messages
3,276
Actually, even better:

Select DISTINCT all records from the table with value greater than 1000. Query should just return the Marker. Let's call it IncludeList.

Next, left join that to your main query on the Marker fields. For Include, use this:
Code:
Include: IIf(IsNull(IncludeList.Marker, "No", "Yes")

That will run faster than including a procedure call.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Yesterday, 21:25
Joined
Oct 17, 2012
Messages
3,276
DBGuy - I wonder which would run faster, your solution in 3 or mine in 4? The one I gave in 2 kind of sucks.
 

plog

Banishment Pending
Local time
Yesterday, 20:25
Joined
May 11, 2011
Messages
11,613
First, don't use "value" as a field name. It's a reserved word and makes coding/querying a little more difficult.

Code:
SELECT YourTable.marker, IIf(Max([AlleleValue])<1000,"no","yes") AS Include
FROM YourTable
GROUP BY YourTable.marker;

Then, if you need it at an allele level, just make a new query bumping that query against your table.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Yesterday, 21:25
Joined
Oct 17, 2012
Messages
3,276
Aaand the winner with the most elegant solution is plog!
 

Users who are viewing this thread

Top Bottom