Have I a fatal table design or am I asking for the wrong type ofReport?

  • Thread starter Thread starter Realitygdk
  • Start date Start date
R

Realitygdk

I have one table named OSworksheet that has the following fields.

AccessUniqueNumber
SPA_Dist_Number
SPA_Dist_Number_Name
SPA_Dist_Number_Name_Incorporated
SPA_Dist_Number_Name_UnIncorporated
SPA_Dist_Number_Name_Federal_Legislators
SPA_Dist_Number_Name_Federal_Legislators_House_of_Represenatives
SPA_Dist_Number_Name_Federal_Legislators_Senate
SPA_Dist_Number_Name_Federal_Legislators_House_First_Name
SPA_Dist_Number_Name_Federal_Legislators_House_Last_Name
SPA_Dist_Number_Name_Federal_Legislators_House_Party
SPA_Dist_Number_Name_Federal_Legislators_House_District_Number
SPA_Dist_Number_Name_Federal_Legislators_House_District_City
SPA_Dist_Number_Name_Federal_Legislators_Senate_First_Name
SPA_Dist_Number_Name_Federal_Legislators_Senate_Last_Name
SPA_Dist_Number_Name_Federal_Legislators_Senate_Party
SPA_Dist_Number_Name_Federal_Legislators_Senate_District_Number
SPA_Dist_Number_Name_Federal_Legislators_Senate_District_City
SPA_Dist_Number_Name_State_Legislators
SPA_Dist_Number_Name_State_Legislators_StAssembly
SPA_Dist_Number_Name_State_Legislators_StSenate
SPA_Dist_Number_Name_State_Legislators_StAssembly_First_Name
SPA_Dist_Number_Name_State_Legislators_StAssembly_Last_Name
SPA_Dist_Number_Name_State_Legislators_StAssembly_Party
SPA_Dist_Number_Name_State_Legislators_StAssembly_District_Number
SPA_Dist_Number_Name_State_Legislators_StAssembly_District_City
SPA_Dist_Number_Name_State_Legislators_StSenate_First_Name
SPA_Dist_Number_Name_State_Legislators_StSenate_Last_Name
SPA_Dist_Number_Name_State_Legislators_StSenate_Party
SPA_Dist_Number_Name_State_Legislators_StSenate_District_Number
SPA_Dist_Number_Name_State_Legislators_StSenate_District_City
SPA_Dist_Number_Name_County_Legislators_Board_Of_Supervisors
SPA_Dist_Number_Name_County_Legislators_County_First_Name
SPA_Dist_Number_Name_County_Legislators_County_Last_Name
SPA_Dist_Number_Name_County_Legislators_County_Party
SPA_Dist_Number_Name_County_Legislators_County_District_Number
SPA_Dist_Number_Name_County_Legislators_County_District_City
SPA_Dist_Number_Name_City_Legislators_Council
SPA_Dist_Number_Name_City_Legislators_Council_First_Name
SPA_Dist_Number_Name_City_Legislators_Council_Last_Name
SPA_Dist_Number_Name_City_Legislators_Council_Party
SPA_Dist_Number_Name_City_Legislators_Council_District_Number
SPA_Dist_Number_Name_City_Legislators_Council_District_City

I then created eight (8) queries based on the OSworkseet table field,
which is one query for each planning area number, SPA_1, SPA_2, SPA_3,
and so forth, ending with SPA_8.

I then linked these eight (8) queues to the main table’s unique number
that is OSworsheet Access Unique Number to the SPA_1’s, then continued
to linked the SPA_1’s Access Unique Number to that of SPA_2’s, then I
linked the SPA_2’s Access Unique Number to that of SPA_3’s, and so
forth to the last linked which tied in SPA_8’s Access Unique Number.
I have linked these queues to the main table, in case there are
additions or deletions, which I am hoping allows me to make the
changes to the effective queue and thus update the table.

I then attempted to create a form, then a report in Access and then I
then tried to merge the document into MS Words…each time I could not …
too many fields, unable to construct.

I am attempting to separate each Planning Regional Area out into a
report which there are eight planning areas, so as to assign the
listed incorporated and unincorporated cities to the governing
legislators: Fed, State, County and City.

A District will equals a
PlanningAreaNumber
PlanningAreaName
Incorporated Cities Covered (listing)
Unincorporated Cities Covered (listing)
Federal Legs (listing)
FSenate (listing)
First Name
Last Name
FHouse (listing)
First Name
Last Name
FedDistrict Number (listing by name)
Party (listing by name)
State Legs (listing)
StSenate (listing)
First Name
Last Name
StAssembly(listing)
First Name
Last Name
StateDistrictNumber(listing)
Party (listing)

County Legs (listing)
CtySupv(listing)
First Name
Last Name
CountyDistrictNumber(listing)

City Legs (listing)
Council (listing)
First Name
Last Name
CityDistrictNumber


I understand sharing of time, professional knowledge--experiences and
advice here in this group is strictly done voluntarily. I am not
attempting to cheat this group by seeking a professional report. What
in all honesty I am asking is why I am missing the logic of Access,
rather why is my illogical attempts seemed by me as logical. The best
ways to have Access filter the listed cities and then apply the
correct legislators to those cities are as I have listed them.
Thank You for sharing your time and your experiences in this matter
with me, G. Kennedy
 
First off, I have NO IDEA what you're trying to say. Second, you must be an
old COBOL programmer (like me), because you like 120 character field names.
I'd get rid of the following part of the field names:

SPA_Dist_Number_Name_

That does nothing for you.

Further down in your post, you start using the term "queues". I'm not sure
in what context you mean that.

In any event, I'd have a Contacts table that lists than name, party,
district number, etc, but then add a code indicating what body they belong to
(i.e State legislature, federal legislature, house, senate, etc.)

I'd create a second table that had all those possibilities, each associated
with a code number, and that code number goes into each Contacts record. When
you link the tables, the code will allow you to pull up the body's
description from the other table.

The way your table is currently created is IMO some very bad design. It is
not normalized properly at all.

Another way would be to have a LegislativeBody table, and assign Contacts TO
that body via another table. Lots of ways to do it, but NOT the way you've
tried.
 
WOW!
Drop those long field names.
Have names fields, Branch
(Represenative/StLegislature/Senate/StSenate/Council/etc), Party, District,
City

Use the report Sorting And Grouping feature.
 
I have one table named OSworksheet that has the following fields.

AccessUniqueNumber
SPA_Dist_Number
SPA_Dist_Number_Name
SPA_Dist_Number_Name_Incorporated
SPA_Dist_Number_Name_UnIncorporated
SPA_Dist_Number_Name_Federal_Legislators
SPA_Dist_Number_Name_Federal_Legislators_House_of_Represenatives
SPA_Dist_Number_Name_Federal_Legislators_Senate
SPA_Dist_Number_Name_Federal_Legislators_House_First_Name
SPA_Dist_Number_Name_Federal_Legislators_House_Last_Name
SPA_Dist_Number_Name_Federal_Legislators_House_Party
SPA_Dist_Number_Name_Federal_Legislators_House_District_Number
SPA_Dist_Number_Name_Federal_Legislators_House_District_City
SPA_Dist_Number_Name_Federal_Legislators_Senate_First_Name
SPA_Dist_Number_Name_Federal_Legislators_Senate_Last_Name
SPA_Dist_Number_Name_Federal_Legislators_Senate_Party
SPA_Dist_Number_Name_Federal_Legislators_Senate_District_Number
SPA_Dist_Number_Name_Federal_Legislators_Senate_District_City
SPA_Dist_Number_Name_State_Legislators
SPA_Dist_Number_Name_State_Legislators_StAssembly
SPA_Dist_Number_Name_State_Legislators_StSenate
SPA_Dist_Number_Name_State_Legislators_StAssembly_First_Name
SPA_Dist_Number_Name_State_Legislators_StAssembly_Last_Name
SPA_Dist_Number_Name_State_Legislators_StAssembly_Party
SPA_Dist_Number_Name_State_Legislators_StAssembly_District_Number
SPA_Dist_Number_Name_State_Legislators_StAssembly_District_City
SPA_Dist_Number_Name_State_Legislators_StSenate_First_Name
SPA_Dist_Number_Name_State_Legislators_StSenate_Last_Name
SPA_Dist_Number_Name_State_Legislators_StSenate_Party
SPA_Dist_Number_Name_State_Legislators_StSenate_District_Number
SPA_Dist_Number_Name_State_Legislators_StSenate_District_City
SPA_Dist_Number_Name_County_Legislators_Board_Of_Supervisors
SPA_Dist_Number_Name_County_Legislators_County_First_Name
SPA_Dist_Number_Name_County_Legislators_County_Last_Name
SPA_Dist_Number_Name_County_Legislators_County_Party
SPA_Dist_Number_Name_County_Legislators_County_District_Number
SPA_Dist_Number_Name_County_Legislators_County_District_City
SPA_Dist_Number_Name_City_Legislators_Council
SPA_Dist_Number_Name_City_Legislators_Council_First_Name
SPA_Dist_Number_Name_City_Legislators_Council_Last_Name
SPA_Dist_Number_Name_City_Legislators_Council_Party
SPA_Dist_Number_Name_City_Legislators_Council_District_Number
SPA_Dist_Number_Name_City_Legislators_Council_District_City

I then created eight (8) queries based on the OSworkseet table field,
which is one query for each planning area number, SPA_1, SPA_2, SPA_3,
and so forth, ending with SPA_8.

I then linked these eight (8) queues to the main table’s unique number
that is OSworsheet Access Unique Number to the SPA_1’s, then continued
to linked the SPA_1’s Access Unique Number to that of SPA_2’s, then I
linked the SPA_2’s Access Unique Number to that of SPA_3’s, and so
forth to the last linked which tied in SPA_8’s Access Unique Number.
I have linked these queues to the main table, in case there are
additions or deletions, which I am hoping allows me to make the
changes to the effective queue and thus update the table.

I then attempted to create a form, then a report in Access and then I
then tried to merge the document into MS Words…each time I could not …
too many fields, unable to construct.

I am attempting to separate each Planning Regional Area out into a
report which there are eight planning areas, so as to assign the
listed incorporated and unincorporated cities to the governing
legislators: Fed, State, County and City.

A District will equals a
PlanningAreaNumber
PlanningAreaName
Incorporated Cities Covered (listing)
Unincorporated Cities Covered (listing)
Federal Legs (listing)
                FSenate (listing)
                        First Name
                        Last Name
                FHouse (listing)
                        First Name
                        Last Name
        FedDistrict Number (listing by name)
        Party (listing by name)
State Legs (listing)
                StSenate (listing)
                        First Name
                        Last Name
                StAssembly(listing)
                        First Name
                        Last Name
StateDistrictNumber(listing)
        Party (listing)

County Legs (listing)
                CtySupv(listing)
                        First Name
                        Last Name
        CountyDistrictNumber(listing)

City Legs (listing)
                Council (listing)
                        First Name
                        Last Name
CityDistrictNumber

I understand sharing of time, professional knowledge--experiences and
advice here in this group is strictly done voluntarily.  I am not
attempting to cheat this group by seeking a professional report.  What
in all honesty I am asking is why I am missing the logic of Access,
rather why is my illogical attempts seemed by me as logical. The best
ways to have Access filter the listed cities and then apply the
correct legislators to those cities are as I have listed them.
Thank You for sharing your time and your experiences in this matter
with me, G. Kennedy
Click to expand...

Thank you Dennis for taking the time to lift me out of the fog and
thank you for having the experiences to bring me into the current
century… as a result of your advice I can now see and hear the self
inflicted madness that I had created… I shall adhere…Thank you very
much…G. Kennedy
 
First off, I have NO IDEA what you're trying to say. Second, you must be an
old COBOL programmer (like me), because you like 120 character field names.
I'd get rid of the following part of the field names:

SPA_Dist_Number_Name_

That does nothing for you.

Further down in your post, you start using the term "queues". I'm not sure
in what context you mean that.

In any event, I'd have a Contacts table that lists than name, party,
district number, etc, but then add a code indicating what body they belong to
(i.e State legislature, federal legislature, house, senate, etc.)

I'd create a second table that had all those possibilities, each associated
with a code number, and that code number goes into each Contacts record. When
you link the tables, the code will allow you to pull up the body's
description from the other table.

The way your table is currently created is IMO some very bad design. It is
not normalized properly at all.

Another way would be to have a LegislativeBody table, and assign ContactsTO
that body via another table. Lots of ways to do it, but NOT the way you've
tried.












- Show quoted text -
Click to expand...

Karl…how to do I say thank you for showing me that simplicity is
complicity in and of itself…thank you for showing to me how best to
drop those long name to show that brevity is beautiful when timely
applied…thank you for taking the time to sharing this insight with me…
G. Kennedy
 
Back
Top