Data Design Question

[Guest]

Access XP - Windows XP

I have a database with two tables that is designed to track Legislator and
Judicial Information.

Let's say one table contains text fields with Legislator information such as
FName, LName and their House District (1st House District, 2nd House, 3rd
House District, etc.).

The other table also contains text fields with Judicial Information such as
FName, LName, Jurisdiction, and the House District their court is located in
(1st House District, 2nd House District, etc.).

The House District field in the legislator table contains a single entry
because a House Rep. is only in one district. However the House District
field in the Judges table may contain multiple entries since the court may
be in more than one House District. If it does contain multiple entries they
are entered as a text string and separated by commas for example, 1st House
District, 9th House District, 22nd House District (this is how the data is
given to me, in an Excel file).

I have related the two tables based on the House District field.

My goal is to run a query that will list the judge's name and the name of
the representative in their district. However, t he fact that some courts
have multiple text entries in their House District field prohibits the query
from listing all of the reps in that district.

My thinking is the data in the judges table, House District field is not
laid out properly. I believe that instead of having a long text string of
entries in a single field the data needs to be laid out so that if a judge
has multiple House Districts, I should create multipl records for the same
judge and list a different House District for each record. For example, if
Judge Wise is in House Distrct 1 and 2, I should create two records for Judge
Wise, one with a district of House District 1 and the other with a district
of House District 2.

Is my thinking correct?

Thank you.

 

[Rick Brandt]

Access XP - Windows XP

I have a database with two tables that is designed to track
Legislator and Judicial Information.

Let's say one table contains text fields with Legislator information
such as FName, LName and their House District (1st House District,
2nd House, 3rd House District, etc.).

The other table also contains text fields with Judicial Information
such as FName, LName, Jurisdiction, and the House District their
court is located in (1st House District, 2nd House District, etc.).

The House District field in the legislator table contains a single
entry because a House Rep. is only in one district. However the
House District field in the Judges table may contain multiple entries
since the court may be in more than one House District. If it does
contain multiple entries they are entered as a text string and
separated by commas for example, 1st House District, 9th House
District, 22nd House District (this is how the data is given to me,
in an Excel file).

I have related the two tables based on the House District field.

My goal is to run a query that will list the judge's name and the
name of the representative in their district. However, t he fact
that some courts have multiple text entries in their House District
field prohibits the query from listing all of the reps in that
district.

My thinking is the data in the judges table, House District field is
not laid out properly. I believe that instead of having a long text
string of entries in a single field the data needs to be laid out so
that if a judge has multiple House Districts, I should create multipl
records for the same judge and list a different House District for
each record. For example, if Judge Wise is in House Distrct 1 and 2,
I should create two records for Judge Wise, one with a district of
House District 1 and the other with a district of House District 2.

Is my thinking correct?

Yep. Mostly. You actually should have one table with one entry per judge
containing the data that has only one instance per judge and another table
that holds only the judge ID and the district. That way you aren't
redundantly entering the static data over and over. In that setup a judge
could have zero or any higher number of district entries.

 

[PC Datasheet]

You need the following tables:
TblHouseDistrict
HouseDistrictID
HouseDistrict

TblLegislator
LegislatorID
FName
LName
HouseDistrictID

TblJudge
JudgeID
FName
LName
Jurisdiction 'not sure what this is. If a judge has multiple jurisdictions,
you need a separate table for that.

TblJudgeHouseDistrict
JudgeHouseDistrictID
JudgeID
HouseDistrictID

Note: that you can manually seperate the House Districts in Excel with
Text to Column under Data before you import or you can automatically
separate them and then import with Transferspreadsheet.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.