Need to "normalize" table

[Nathan Benefield]

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.

[MGFoster]

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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Nathan Benefield]

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" <[e-mail] > wrote in message
news:1w******** ********@newsre ad1.news.pas.ea rthlink.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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[GH]

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.Table Defs("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= [YourADOConnecti on].OpenSchema(adS chemaColumns, 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" <[e-mail] > wrote in message
news:1w******** ********@newsre ad1.news.pas.ea rthlink.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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Nathan Benefield]

I have three tables, the first two - with member information and with Act
descriptions are done. My problem is with creating the third table in the
format needed. I have played around with the first function a bit, but
need less ambiguity, as my programming knowledge is limited. The table I am
starting from is called "house votes" with fields "Name" for legislator name
and fields for each act "Act 1" , "Act 98", "act 1A", "Veto 1", etc.

I have tried this, but get errors right away.

Function Field()
Dim fld As Field
For Each fld In CurrentDb.Table Defs("House Votes").Fields
If [fld.Name Not="Name"] Then
[SELECT fld.Name as "Act Number", Name As "Name", fld as
"Fld" INTO "Votes" ]
End If
Next fld
End Function
"GH" <bo************ **@gmail.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...

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.Table Defs("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= [YourADOConnecti on].OpenSchema(adS chemaColumns, 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" <[e-mail] > wrote in message
news:1w******** ********@newsre ad1.news.pas.ea rthlink.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:::mgf0 0 <at> earthlink <decimal-point> net
> Oakland, CA (USA)