Table redesign

[Guest]

I have inherited a contacts database that needs serious work. One problem is
that a series of categories (e.g., Member, Executive Member, Forum Member,
Committee member) - actually 136 categories - have been created in the
original Contacts table as individual named fields and then as Yes/No. I'm
trying to rearrange and disagregate the data, so I now have a Contacts Table
for all my original records and Categories Type table with the 136 as
records rather than fields. How on earth do I transfer the information over?
I've got over 4000 records and some of them have up to 20 category types, so
doing it manually would be a nightmare. Help!!

 

[Rick B]

I have a suggestion that may not be the best.

You have a one-to-many relationship (at least one) with a one side of your
current members table, and a many side of the membership level. Create a
new table for the "membership level". Include two fields, "member_number"
and "category". This will be your "many" in your one-to-many relationship.

You could create an append query and pull all records from your existing
table with "member=True" and append the selected results (member number and
category) to your new "membership" table and set the "Category" to "member".

Then, modify your query to pull all records with "Executive Member-true".
Append the selected records to your "membership" table and set the
"category" to "executive member".

Do this 136 times.

Again, there may be more elegant suggestions from the MVPs, but this would
work if you don't get any better suggestions.

 

[Guest]

Hi Rick
Thanks for this; I was reluctantly coming to the conclusion that this might
be the only way round it. If anyone has any better ideas, please let me know!

 

[Guest]

The first thing I would do is find the person who originally designed the
database and beat him to a bloody pulp.

Then, what you are wanting to do can be done in VBA without having to repeat
a query 136 times. The code could be similar to something like this example.
Now, when you see the statement .Fields(intFldNdx) below, I am assuming (and
hoping) all 136 fields are contiguous, that is, there are no fields between
them. For example purposes, I will assume the first category field is field
number 40. Since field indexing is 0 based, I will be starting the loop at
39.

Dim dbf As Database
Dim rstMembers as Recordset
Dim rstCats as Recordset
Dim intFldNdx as Integer

Set dbf = Currentdb
Set rstMembers = dbf.OpenRecordset("MemberTableName")
Set rstCats = dbf.OpenRecordset("CategoryTable")

rstMembers.MoveLast
rstMembers.MoveFirst

Do While Not rstMembers.EOF
For intFldNdx = 39 To 135
If rstMembers.Fields(intFldNdx) = True Then
With rstCats
.AddNew
'Here you will have to get the field names and values to work for how you
have them set up
!CatName = rstMembers.Fields(intFldNdx).Name
!MemberID= rstMembers.MemberID
.Update
End With
End If
rstMembers.MoveNext
Loop
rstMembers.Close
Set rstMembers = Nothing
Set rstCats = Noting
Set dbf = Nothing

Hope this will be helpful

 

[John Vinson]

I have inherited a contacts database that needs serious work. One problem is
that a series of categories (e.g., Member, Executive Member, Forum Member,
Committee member) - actually 136 categories - have been created in the
original Contacts table as individual named fields and then as Yes/No. I'm
trying to rearrange and disagregate the data, so I now have a Contacts Table
for all my original records and Categories Type table with the 136 as
records rather than fields. How on earth do I transfer the information over?
I've got over 4000 records and some of them have up to 20 category types, so
doing it manually would be a nightmare. Help!!

As a possible alternative to Rick and Klatuu's suggestions (both of
which will work, Klatuu's being probably the easiest) you might try a
"Normalizing Union Query". This will be moderately tedious to set up,
but with cut and paste in a good text editor may be pretty quick.

First set up a UNION query (or for 136 categories, probably two or
three of them, since the query will likely get too big to process):

SELECT ContactID, "Member" As Category
FROM contacts
WHERE [Member]
UNION ALL
SELECT ContactID, "Executive Member"
FROM contacts
WHERE [Executive Member]
UNION ALL
SELECT ContactID, "Forum Member"
From [Contacts]
WHERE [Forum Member]

<and so on and so on>

You don't need = True on the criteria, you'll only get TRUE records.

You can then base an Append query on this UNION query to populate the
tall-thin table.

John W. Vinson[MVP]