Nathan,
Wouldn't it make more sense to have three tables, one to store the
members, one for the acts, and a join showing the member's vote?
It would look like:
MEMBER table
MemberID MemberName etc.
1 Jones
2 Johnson
etc.
ACT tables
ActID ActTitle etc.
1 Act1
2 Act1A
etc.
VoteTable
MemberID ActID VoteBit
1 1 Y
1 2 Y
2 1 N
etc.
(or something along these lines)
Anyway, I had to do something similar a while back where the table had
columns that really should have been rows in a different table. I
wrote a function that looped through the columns of the table and
looked for key naming structures then populated the new table with the
appropriate values. Of course, this assumes that the columns to be
normalized follow a structured naming convention that can easily be
converted to field values. Unfortunately, I cannot share the specific
function, as it is not available to me now, but the general concept is
to use the FOR EACH loop to loop through the columns of the table or
select the table schema from an ADO recordset.
Dim fld As Field
For Each fld In CurrentDB.TableDefs("YourTable").Fields
If [fld.Name condition to determine which columns to process]
Then
[Build and process a SQL string to update the data to
the new table structure]
End If
Next fld
OR
Dim rs As New ADODB.Recordset
Set rs= [YourADOConnection].OpenSchema(adSchemaColumns, Array(Empty,
Empty,
"YourTableName"))
While Not rs.EOF
If [rs!COLUMN_NAME condition to determine which columns
to process] Then
[Build and process a SQL string to update the data
to the new table structure]
End If
rs.MoveNext
Wend
rs.Close
I still had to customize my function due to irregularities in some
column names, but the names generally followed a pattern that could be
used within a CASE construct or IF statement to convert to field
values.
I hope this helps you.
- GH
Nathan Benefield wrote:
An Act is and Act - passed and signed by the governor. The Vote on the Veto
is a vote to pass a bill which was passed and then vetoed.
"MGFoster" <me@privacy.com> wrote in message
news:1w****************@newsread1.news.pas.earthli nk.net... Nathan Benefield wrote: I currently have a spreadsheet tracking votes on legislation in a matrix
type
format. It is something like this
Name Act1 Veto1 Act1A
Jones yes No Yes
Johnson Yes Yes Ex.
Only with many more members and bills. I want to normalize this so
that i can create reports by both Bill and by member - linking it to
tables
with bill descriptions and member profiles. I think what I want is a
table
like this:
Name Act Vote
Jones Act1 Yes
Jones Veto1 No
Johnson Act1 Yes
I need help writing a function or a query that can do that.
What's an Act, an Action (what type of action), the member is a Actor...?
Is the Vote on the Veto a vote to veto the bill ?? Wouldn't that be a yea
or nay vote on the bill itself?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)