Convert table with separate checkbox columns into multiple rows by an index

[Holly]

This is probably a really bad way to describe my quandary.
I have a table with values like

GROUP CHECK1 CHECK2 CHECK3 CHECK4
11111 -1 0 0 -1
22222 0 -1 0 0

The Group is the key, and the CHECK boxes are for manuals,
they are boolean "check boxes" on the entry form,
so they are either -1 or 0 (true or false).

Is there a quick way to instead have a table with

GROUP CHECK
11111 1
11111 4
22222 2

Sorry for the lame question, but I'm trying to use a listbox on that
form,
where the user picks the group, then uses a listbox to multi-select
the manual (check) numbers, instead of them having to select each of
the
checkboxes like they have now (there are 99 of them~).

 

[Duane Hookom]

You can normalize your table with a union query:

SELECT GROUP, 1 as Check
FROM [with values like]
WHERE CHECK1
UNION ALL
SELECT GROUP, 2
FROM [with values like]
WHERE CHECK2
UNION ALL
SELECT GROUP, 3
FROM [with values like]
WHERE CHECK3
UNION ALL
SELECT GROUP, 4
FROM [with values like]
WHERE CHECK4;

 

[guido via AccessMonster.com]

You could set some code that would do it. Otherwise you could do separate
append queries for each check box. Code that would create and run the
separate appends would look like this:

Sub collect_data()
Dim intChk as Integer
Dim strSQL as String

For intChk=1 to 99
strSQL = "INSERT INTO NEW_TABLE_NAME ( GROUP, CHECK ) "
strSQL = strSQL & "SELECT OLD_TABLE_NAME.GROUP, " & intChk & " AS [CHECK]
"
strSQL = strSQL & "FROM OLD_TABLE_NAME "
strSQL = strSQL & "WHERE (((OLD_TABLENAME.CHECK" & intChk & ")=True));"

DoCmd.RunSQL strSQL
Next intChk
End Sub

The code only works if the names of your fields are 'check1', 'check2', etc.

 

[KARL DEWEY]

Yes there is. On you form add an "Option Group" to do what you want. It
uses only one field.

 

[Holly]

You can normalize your table with a union query:

SELECT GROUP, 1 as Check
FROM [with values like]
WHERE CHECK1
UNION ALL
SELECT GROUP, 2
FROM [with values like]
WHERE CHECK2
UNION ALL
SELECT GROUP, 3
FROM [with values like]
WHERE CHECK3
UNION ALL
SELECT GROUP, 4
FROM [with values like]
WHERE CHECK4;


--
Duane Hookom
MS Access MVP

snip

Hi Duane! Thank you very much for the tip! I have about 60+ "CHECK"
fields, so I couldn't do them all in one query (I got an error on too
many criteria). So I split them into two queries. Now I just have to
figure out how to join the information from the two queries and sort by
GROUP.

 

[Duane Hookom]

I would normalize the table structure even if it is just to create a
temporary normalized table. You can use the code suggested by quido.

--
Duane Hookom
MS Access MVP

You can normalize your table with a union query:

SELECT GROUP, 1 as Check
FROM [with values like]
WHERE CHECK1
UNION ALL
SELECT GROUP, 2
FROM [with values like]
WHERE CHECK2
UNION ALL
SELECT GROUP, 3
FROM [with values like]
WHERE CHECK3
UNION ALL
SELECT GROUP, 4
FROM [with values like]
WHERE CHECK4;


--
Duane Hookom
MS Access MVP

snip

Hi Duane! Thank you very much for the tip! I have about 60+ "CHECK"
fields, so I couldn't do them all in one query (I got an error on too
many criteria). So I split them into two queries. Now I just have to
figure out how to join the information from the two queries and sort by
GROUP.

 

[Holly]

You could set some code that would do it. Otherwise you could do separate
append queries for each check box. Code that would create and run the
separate appends would look like this:

Sub collect_data()
Dim intChk as Integer
Dim strSQL as String

For intChk=1 to 99
strSQL = "INSERT INTO NEW_TABLE_NAME ( GROUP, CHECK ) "
strSQL = strSQL & "SELECT OLD_TABLE_NAME.GROUP, " & intChk & " AS [CHECK]
"
strSQL = strSQL & "FROM OLD_TABLE_NAME "
strSQL = strSQL & "WHERE (((OLD_TABLENAME.CHECK" & intChk & ")=True));"

DoCmd.RunSQL strSQL
Next intChk
End Sub

The code only works if the names of your fields are 'check1', 'check2', etc.

Hi Guido!
I appreciate your help so much! I tried Duane's method, but I had too
many CHECK fields.
I looked at your example code, and I ran with a few changes. You are
the MAN! That was so awesome!

Thank you so much!

 

[guido via AccessMonster.com]

Great! I'm glad it helped.