Best way to build my structure for a specific query situation

[2Zons]

I have a database of 47 boolean fields. I need to build a table where each record indicates which of those fields are true. For each record more than one of the fields can be true. I need to then query all the records to find groups of the boolean fields that are never found true together in the same record. I haven't built the table yet because I'm not sure which design to use so I can build the query. I could build a table that has all 47 booleans as individual fields, or I could build a table that has each of the 47 fields as a record, and then another table that links to all the boolean fields that are true for each record. I am not sure how the query will look either. If it helps I can post a simplified table as an example.

I'm pretty rusty at Access and any help pointing me in the right direction would be most appreciated.

If your curious or if it helps understand: I'm designing a CPU. In order to optimize the microcode I need to know which control signals groups are exclusive (never used together). If I can query this from a table of all my micro-instructions, I can greatly reduce the instruction decode circuitry.



Thanks

[orange]

I do not have an answer for your set up/request. I did work with someone who designed a set of algorithms to review names. More specifically, potential company and product names that might sound alike or confuse the marketplace. The algorithm(s) were built on a machine CDC with (if I recall correctly) a 60 bit word. Each bit signified some letter, vowel, sound, dipthong..... It was by using boolean arithmetic/algebra and the meaning of specific bits that he could resolve and separate similar terms. I realize there was much more to it than I have described. He said the technique was/could be used in all sorts of pattern matching and similarity analysis. His interest was in names and copyright, but he did talk about human characteristics, fingerprints, DNA, predisposition of medical issues etc. He also had a PhD in mathematics and linguistics and a thorough knowledge of machine architecture where he ran this.

You may find more info here for tutorials and extra materials on Boolean algebra and digital hardware design. I found this link (blog and youtube videos) when searching for boolean algebra. I have no affiliation with the site or individual.

Good luck with your project.

[2Zons]

I think I need to illustrate to make it more clear.

ID	bol1	bol2	bol3	bol4	bol5
1		X		X
2		X			X
3	X
4			X

Using the above data as an example: The X's representing true or yes, and the blank's representing false or no. I want to write a query or queries that will find groups of bol's that are never used together. In the above example bol1, bol2, and bol3 are never used together; and bol4 and bol5 are never used together. I can see how there are multiple combinations you could make. One step might be to find the most values used simultaneously, 2 in this example. This would define the min number of groups. Step 2 would be to define those groups. Is this possible to use a query for? I'ts easy to see them with a small table like this but when I've got 50 bol's and near a thousand records it's not so easy.

I would have thought this was quite a common database problem and have been done quite a bit, but I'm not a database guru.

[orange]

I'm pretty sure it can be done. It might take a little creativity and some experimenting. Regardless, I think as you start to add some realistic sample data, not only will you have analyzed your issue more thoroughly, but other readers may have some experience and suggestions.

Good luck with your project.

Also, I did a little more Googling and would suggest youtube videos on boolean algebra by Kevin Drumm may be better than my previous suggestion.

Update: I did a little testing. Create a table with ID and B1--B7 (boolean field) with 100 records.
Populated with Random number (mod 2 to get True/False).
Then set up query to do Boolean And for each combination.
In Access False is 0, True is -1.
I haven't gone to the point of counting that you're requesting, but here is some stuff I used that you might consider.



A sample of the random data generated.


Query57 SQL

SELECT ID,[B1] And [B2]  AS B1B2,  [B1] And [B3]  AS B1B3
, B1 and b4 as B1B4, B1 and B5 as B1B5, B1 and B6 as B1B6
,B1 and B7 as B1B7
,  [B2] And [B3]  AS B2B3
, B2 and b4 as B2B4, B2 and B5 as B2B5, B2 and B6 as B2B6
,B2 and B7 as B2B7
, B3 and b4 as B3B4, B3 and B5 as B3B5, B3 and B6 as B3B6
,B3 and B7 as B3B7
, B4 and B5 as B4B5, B4 and B6 as B4B6
,B4 and B7 as B4B7
, B5 and B6 as B5B6
,B5 and B7 as B5B7
, B6 and B7 as B6B7
FROM TblBoole;

Partial result of query57



More query57 output


If this is "in the ball park" I think it's possible to post process the output of qry57. I'm sure others will have some ideas/options. With a little logic and code, the SQL for the query could be generated. I have only tested for pairs of fields.
I just saw Micron's post while editing this--I'm not a mathematician either.

[Micron]

Here's an untested concept if that even remotely resembles a table you have. If all empty fields were 0 and x was 1 (false vs true) it would be a bit easier to work with. I'm no mathematician, so I can only envision a procedure that involves looping through each record and writing the field names to another table where the data is 1, field by field but not appending those names if there aren't at least 2 fields returning 1. That would give you

bol1	bol3	bol5
bol1	bol3	bol4
bol2	bol3	bol4	bol5
bol1	bol2	bol4	bol5

You could then query that table and remove any duplicate rows (should there be any) with DISTINCT ROW predicate. However, I would not be surprised to see a reply that this won't work due to the sample not accurately reflecting a more complex reality. By that I mean that I suspect you actually have combinations that resemble abc, cba, cab, etc. which are not really combinations but are permutations, but the sample did clear things up a bit for me.

I just don't see this being doable in a query based on the number of fields involved and the fact that the group size is volatile. If you research "ms access combinations" or similar, you'll see that there are similar inquiries but they're not as complex as yours.

[2Zons]

I'm starting to think that having a table of all the bol's will help things out. Like I said in previous post the minimum number of groups will be the same as the maximum number of bol's used in one record. That's easy to find and for my data should be 4 (haven't finished building all the data yet). Once I know the number of groups if I have the bols as a seperate table I can than iteravely query each bol to see which are not found with it. I think I might have worked it out. If i figure out a working solution I will post it here.

[orange]

Good stuff.
After seeing your
"number of bol's used in one record"
I took my tblBoole with its random records and ran this query to get Total Ands in a record ordered descending

select totands, count(totands) as RecsWithCount from
(
SELECT TblBoole.ID, Abs([B1]+[B2]+[B3]+[B4]+[b5]+[b6]+[b7]) AS ToTAnds
FROM TblBoole
ORDER BY Abs([B1]+[B2]+[B3]+[B4]+[b5]+[b6]+[b7]) DESC
)
Group by totands
order by totands desc;

and got this distribution

totands	RecsWithCount
7	2
6	5
5	14
4	28
3	25
2	18
1	7
0	1

Good luck with your project.

[Micron]

EDIT: following relates to post 4, not 7 as it wasn't there at the time.

Does this mean you're creating combinations of field pairs? There are 21 possible combinations for 7 fields taken 2 at a time, so I'm thinking no you're not because you're short of that number. But if yes, the first post said there are 47 fields. Taken as pairs, 1,081 combinations. Taken as quads, 178,365. Or am I missing the point, because if what you're suggesting will work I'd like to try to grasp the suggestion better than I might be.

@2Zons, yes please do post your solution as I think the task might be more rare than you think. I wasn't able to find any Access related postings anywhere that came close.

[orange]

Micron,

Re Post 4.

I was editing and "experimenting" and didn't see your post till later. Yes I only did pairs. Perhaps I missed some??
I think there are 21 there on re checking.

In post 7, I took my randomly populated table and just did an abs( sum of the in fields in the records), then did a count of records with those values.

To 2zons -- yes post any solution.

[Micron]

Ah, ok, I was counting your table and query fields as a split picture of a single object (query or table) and only counted 19.
Not sure I would want to do that for 47 items that can be grouped. As usual, I like to tackle new things and this one was no exception. Spent a long time playing around (ditched the idea of a collection because keys (as values) are a bit messier to get as well as the items, but once I did, I found that the resulting sql would have been a hair puller because of the randomness of the fields that fit the bill (the ones that met the threshold for group size and were not Null/0). I opted for one output field in a table; that output being csv. Here's the code result, sans error handling:

Sub GetCombos(GroupSize As Integer, ScopeSize As Integer)
Dim rs As DAO.Recordset
Dim i As Integer, n As Integer
Dim strOut As String, sql As String

CurrentDb.Execute "DELETE * FROM table3", dbFailOnError 'clear temp table

Set rs = CurrentDb.OpenRecordset("table1") 'get item groups from table1
If Not (rs.EOF And rs.BOF) Then
  rs.MoveFirst
  Do While Not rs.EOF
    n = 0 'ensure n = 0 on subsequent passes
    sql = "INSERT INTO Table3 (Combination) SELECT '" 'ensure sql is reset on subsequent passes
    For i = 1 To rs.Fields.Count - 1 'begin 1 to skip over ID field and trim rs.count accordingly
      'Next assumes type is a number; rs.count will be 1 higher than wanted when leaving out ID field, so < rs.fields.count
      If i < rs.Fields.Count And rs(i) = 1 Then
       strOut = strOut & rs.Fields(i).Name & ","   '<= IF the ID field gets added to collection
       n = n + 1
      End If
    Next
    If n >= GroupSize Then 'make = if only wanting groups with same count as GroupSize value
      sql = sql & Left(strOut, Len(strOut) - 1) & "'"
      CurrentDb.Execute sql, dbFailOnError
    End If
  rs.MoveNext
  strOut = ""
 Loop
End If

rs.Close
Set rs = Nothing

End Sub

DATA

ID	bol1	bol2	bol3	bol4	bol5
1	0	1	0	1	0
2	0	1	0	0	1
3	1	0	0	0	1
4	0	0	1	0	0

RESULT of calling as getcombos 2,5

Combination
bol2,bol4
bol2,bol5
bol1,bol5

csv shouldn't be too hard to work with. The function is meant to work with any number of fields and group size, BUT I have not written to trap user errors such as if the field count was less than the group size (e.g. 15 groups but only 10 fields). It's getting late; time to pack it in on a solution that may never get used anyway. If need be, I could probably tweak it some more.

[orange]

Thanks for posting. I think we should wait to hear from 2Zons.

I used your sub GetCombos with my tblBoole

Sub testMicro()
Dim grp As Integer: grp = 3
Dim scope As Integer: scope = 30    ' not understanding exactly what scope is???? Not needed/not used
 Call GetCombos(grp, scope)
End Sub

I modified your output table name to Table3Micron and added a field SrcID to capture the ID of original record my TblBoole
and got a count of records with TRUE in the fields with this query

SELECT  Table3Micron.Combination
, Count(Table3Micron.Combination) AS CountOfCombination
FROM Table3Micron
GROUP BY Table3Micron.Combination
order by combination;

Sample of the output (partial)

[2Zons]

Micron,

The output of your code seems to indicate which fields are found together, sort of the opposite of what I'm looking for. Your code did make me think of a way of getting an answer using code instead of a query. I agree that I don't think a query is the right way to solve it. I restructured my data so that the the fields that are found together are actually part of the data, let me Illustrate: (from my actual data)


Relationships


tblMicrocode


tblMicrocodeDetails


tblControlCodes - Form

These are just the first few records of each table. Tables are showing name values in place of joined fields (ID's). Using this structure I get a built in count of number of control codes used, and which are used together. For clarification, each instruction has several cycles.

I can see the logic in how to find the answer like this:


For each tblControlCode.ID (ControlCode) check all other ControlCode's to see if they are ever found in tblMicrocodeDetails with the same MicroCodeID. When a ControlCode is identified as not being found together add it to the the list.

After all ControlCodes are checked, remove the list of ControlCodes found from all ControlCodes and pass that to the function again untill you have no ControlCodes left, or only control codes that are found with each other.

I'm not sure if that is super confusing. I understand it in my head, tricky to try and explain it. I am very very rusty at Access and VBA. Last time I was using them professionally was in 2001. The knowledge is up there somewhere under some cobwebs. The discussion here has been helpful for me.