Tbl modification - VBA might work

[alex]

Hello all,

I have a table that contains about twenty columns. It’s a table that
was used to group a particular location. Something like:

Location Group1 Group2 Group3…
02116 false true false
02115 true false false

I want to run a query that takes the table and for every [Group]
column, if the value is true, to replace true (it’s a check box) with
the name of the column. Something like:

Location Group1 Group2 Group3…
02116 false Group2 false
02115 Group1 false false

I don’t think that I could do this with a query. I’m hoping someone
with more VBA experience than myself might have an idea.

You astute Access users may be seeing what I’m eventually trying to do
with this table! Something like:

Location Grouping
02115 Group1
02116 Group2

....much more normalized

Thanks,
alex

 

[John W. Vinson]

Hello all,

I have a table that contains about twenty columns. It’s a table that
was used to group a particular location. Something like:

Location Group1 Group2 Group3…
02116 false true false
02115 true false false

I want to run a query that takes the table and for every [Group]
column, if the value is true, to replace true (it’s a check box) with
the name of the column. Something like:

Location Group1 Group2 Group3…
02116 false Group2 false
02115 Group1 false false

I don’t think that I could do this with a query. I’m hoping someone
with more VBA experience than myself might have an idea.

You astute Access users may be seeing what I’m eventually trying to do
with this table! Something like:

Location Grouping
02115 Group1
02116 Group2

...much more normalized

Thanks,
alex

Your intermediate step is not needed! Good thing too, because it would be a
big pain (you can't store text in a yes/no field and would need to completely
rebuild the table).

You can do this in one pass using a "Normalizing Union Query". Create your new
table with the Location and Grouping fields, empty; then go into the SQL
editor and manually construct this query:

SELECT Location, "Group1" AS Grouping
FROM yourtable
WHERE Group1 = True
UNION ALL
SELECT Location, "Group2"
FROM yourtable
WHERE Group2 = True
UNION ALL
<etc.etc. through all the groups>

Open this query as a datasheet to verify that you're getting the right
results; if so, save it as qryAllGroups, and base an Append query on it to
populate your normalized table.

 

[alex]

Hello all,

I have a table that contains about twenty columns.  It’s a table that
was used to group a particular location.  Something like:

Location    Group1          Group2          Group3…
02116       false           true            false
02115       true            false           false

I want to run a query that takes the table and for every [Group]
column, if the value is true, to replace true (it’s a check box) with
the name of the column.  Something like:

Location    Group1          Group2          Group3…
02116       false           Group2          false
02115       Group1          false           false

I don’t think that I could do this with a query.  I’m hoping someone
with more VBA experience than myself might have an idea.

You astute Access users may be seeing what I’m eventually trying to do
with this table!  Something like:

Location    Grouping
02115       Group1
02116       Group2

...much more normalized

Thanks,
alex

Your intermediate step is not needed! Good thing too, because it would bea
big pain (you can't store text in a yes/no field and would need to completely
rebuild the table).

You can do this in one pass using a "Normalizing Union Query". Create your new
table with the Location and Grouping fields, empty; then go into the SQL
editor and manually construct this query:

SELECT Location, "Group1" AS Grouping
FROM yourtable
WHERE Group1 = True
UNION ALL
SELECT Location, "Group2"
FROM yourtable
WHERE Group2 = True
UNION ALL
<etc.etc. through all the groups>

Open this query as a datasheet to verify that you're getting the right
results; if so, save it as qryAllGroups, and base an Append query on it to
populate your normalized table.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Thanks for your help John. The union query worked just fine!
alex