madman411 Posted September 11, 2015 Share Posted September 11, 2015 I'm trying to count related records (within the same table) where two values are met. The first value is a serial number (Serial), the second value is a "1" (flag). For example: Record 1 / Serial: 12345 / Flag: 1Record 2 / Serial: 12345 / Flag: 1Record 3 / Serial: 12345 / Flag : (empty)Record 4 / Serial: 6789 / Flag: 1Record 5 / Serial: 6789 / Flag: (empty) Records with serial number 12345 should have a count of 2. Records with serial number 6789 should have a count of 1. To quickly explain, this is for conditional formatting. I have a portal with groups of records that fall under a "header" row. If one or more items within a group (joined by Serial) have a 1 in the flag field then the header needs to be a certain color for that specific group. I think I'm over complicating this but I haven't found a solution that works. Would this be better calculated as an SQL statement? Link to comment Share on other sites More sharing options...
madman411 Posted September 11, 2015 Author Share Posted September 11, 2015 Ok, using the following SQL statement I achieved what I was looking for, but it's extremely slow. Is there a way to make this statement a little lighter on the computer? ExecuteSQL ("SELECT Count (*) FROM Items WHERE Flag = 1 and Serial IN ( ? , ? , ? , ? ) " ; "" ; "" ; Serial ; "" ; "" ; "" ) Link to comment Share on other sites More sharing options...
eos Posted September 11, 2015 Share Posted September 11, 2015 What you want is probably ExecuteSQL ( " SELECT COUNT (*) FROM Items WHERE Flag = 1 AND Serial = ? " ; "" ; "" ; Items::Serial ) or create a self-join for Items by Serial, then use Count ( Items_selfJoinBySerial::Flag ). Link to comment Share on other sites More sharing options...
madman411 Posted September 12, 2015 Author Share Posted September 12, 2015 or create a self-join for Items by Serial, then use Count ( Items_selfJoinBySerial::Flag ). Just what I thought - totally overthinking it. Thanks eos! Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 3284 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now