Concatenatation Results in Primary Key and not State Name

[Tommy T]

Here is my situation. 2 Questions both related.

Question 1: I have a query with two tables. Table1 concerns individuals and
the states that they have visited with a maximum of 3 states. Table2
contains 2 fields a numeric Primary Key(Table2.PriKey) and the names of US
States(Table2.StateName).

I have created 3 lookup fields in Table1 to reflect the states that the
individuals have visited: Table1.State1, Table1.State2, and Table1.State3
with the bound column being Table2.PriKey.

If I add State1, State2, and State3 within the QBE the state names will
display e.g. (Maine, Maryland, Massachusetts) however if I concatenate them
like this CombineStates: [Table1]![State1] & ", " & [Table1]![State2] & ", "
& [Table1]![State3] I receive 19, 20, 21. I know that it is a string
concatenation but how do I convert the numbers to StateNames and Concatenate
them?

Question 2: in the Relationships Window Table2(the States Table) only
displays once versus 3 times with a relationship to Table1.State1 and not
to Table1.State2 or Table1.State3 even though Table1.State2 and
Table1.State3 are bound to Table2.PriKey. Is this by design or am I missing
something?

Everyone thanks for all your help

Tom "T"

 

[Jeff Boyce]

Tommy

(see in-line)

Question 1: I have a query with two tables. Table1 concerns individuals and
the states that they have visited with a maximum of 3 states. Table2
contains 2 fields a numeric Primary Key(Table2.PriKey) and the names of US
States(Table2.StateName).

Having repeating fields (State1, State2, ...) is a lot like what you'd see
in a spreadsheet. Access isn't a spreadsheet. A relational database design
would have a table of individuals, plus a table that had only IndividualID
plus StateID. This join/relation table allows you to have as many states
visited as actually happen -- one row per each valid person/state
combination.

I have created 3 lookup fields in Table1 to reflect the states that the
individuals have visited: Table1.State1, Table1.State2, and Table1.State3
with the bound column being Table2.PriKey.

Second issue -- a read through the tablesdbdesign newsgroup reveals that
using the "lookup" field type in table definitions leads to serious
headaches (exactly like the one you're seeing!). Instead of a lookup
datatype, use a long integer data type, which will hold the number from the
state when you use a form, not the table, as your data entry and edit
mechanism. By the way, even though your current "lookup" field says, for
example, Arizona, what Access is storing is the StateID#. So, Access stores
the number, but displays the text -- confused?!

If I add State1, State2, and State3 within the QBE the state names will
display e.g. (Maine, Maryland, Massachusetts) however if I concatenate them
like this CombineStates: [Table1]![State1] & ", " & [Table1]![State2] & ", "
& [Table1]![State3] I receive 19, 20, 21. I know that it is a string
concatenation but how do I convert the numbers to StateNames and Concatenate
them?

Access is giving you what you asked for, the value (a number, remember?)
stored in [Table1]![State1]. If you open a new query, select the Table1 and
the State table, linked on the State1 field, you can then select the State
text field to display "Arizona". By the way, with a State1, State2, ...
StateN design, you have to join EVERY column to the State table. With a
more normalized design (see above), you only have to join the StateID field
once from the join/relation table to the State table.

Question 2: in the Relationships Window Table2(the States Table) only
displays once versus 3 times with a relationship to Table1.State1 and not
to Table1.State2 or Table1.State3 even though Table1.State2 and
Table1.State3 are bound to Table2.PriKey. Is this by design or am I missing
something?

See earlier notes about relational design.

Good luck!

Jeff Boyce
<Access MVP>

 

[Tom \T\]

Hello Jeff,

First, thanks for pointing out my errors in Database Design. I had seen on
other Access web sites that Lookup Fields are frowned upon but I did not
think that it would cause this many problems. I see now I was wrong. Would
you consider the Northwind Database a good design to pattern after or is
there a better alternative?

I thought that with a Lookup field the Primary Key for the States Table
(Table2) "is" stored in Table1. How is the lookup field different than what
you suggested, "Instead of a lookup datatype, use a long integer data type,
which will hold the number from the state when you use a form..."?

If I understand your first paragraph correctly instead of having 2 Tables
there would really be 3 tables with a one-to-many relationship from Table1
to Table2 and from Table3 to Table2 as shown below... is that correct?

Table1(SSN, Lname, Fname, MI, Address, City, State, Zip)
Table2(SSN, StateID#)
Table3(StateID#, StateName)

Also I am using a form for Data Entry and Editing and not allowing the
end-user access to the tables. I guess I got it half right. I do want to be
a better designer and I welcome your suggestions.

Tom "T"

 

[Jeff Boyce]

Tom

Someone else will need to speak to Northwind -- I don't use it.

Although the lookup field stores the foreign key (PK from your other table),
it DISPLAYS the state name. Because of this, when you query the table,
looking for "Arizona", you won't find it! Rather than have the table store
one thing and display something else, using only the long int type will
allow your data and display to be consistent.

Three tables:

tblPerson
PersonID (are you quite certain SSN is a unique identifier, possess
by all persons-of-interest?)
LName
FName
...

tblState
StateCode (you could use the State Abbreviation instead of a long
int/autonumber!)
StateName

trelStateVisited
PersonID (maybe your SSN)
StateCode (or long int/ID, if you wish)

Best of luck on your project

Jeff Boyce
<Access MVP>

 

[Tom \T\]

Jeff,

I am trying to correct my database design and wanted to know how to create a
query to extract the data into the 3rd table. Using your example below here
is how the current data would look like:

tblPerson.PersonID, StateCode1, StateCode2, StateCode3
A, 1
B, 2, 3, 4
C, 4, 5, 6
D, 3
E, 9, 10, 13

How would I create the query to make it look like:

A, 1
B, 2
B, 3
B, 4
C, 4
C, 5
C, 6
D, 3
E, 9
E, 10
E, 13
etc.

- Tom "T"

 

[Tom \T\]

Jeff,

Never mind I had a brain cramp and forgot all about Union Queries. I got the
data.

- Tom "T"

 

[Jeff Boyce]

Tom

I was recommending AGAINST the State1, State2, State3 table design, so you
wouldn't need a UNION query. I was recommending a new table design that
puts VisitedStates in a table by itself, only holding PersonID and StateID,
for each valid pair.

If you visited Arizona, Washington and Delaware, you'd have three rows in
that table. All three would have your PersonID, and each would have the
StateID for its respective state.

Good luck

Jeff Boyce
<Access MVP>

 

[Tom \T\]

Jeff,

I have restructured the table design so that there is tblVisitedStates with
PersonID, StateID, and an Autonumbered Primary Key. I had to add a Primary
Key to tblVisitedStates instead of using the combined PK of tblPerson and
tblStates because of business rules which would allow a Person to visit the
same states on different dates.

Now here is my problem and I just cannot see past it. I have a Main Form
with a subform which is in Datasheet View. Across the top of the Subform's
Datasheet view you can see State#1, State#2, and State#3 which used to be
linked to tblPerson.State1, tblPerson.State2, and tblPerson.State3 and all 3
fields are ComboBoxes to facilitate correct data.

Originally each Field(State#1, State#2, and State#3):

ControlSource = State1
RowSourceType = "Table/Query"
RowSource = "SELECT tblState.StateID, tblState.Description FROM tblState;"
ColumnWidth = 0";1"
BoundColumn = 1

ControlSource = State2
RowSourceType = "Table/Query"
RowSource = "SELECT tblState.StateID, tblState.Description FROM tblState;"
ColumnWidth = 0";1"
BoundColumn = 1

ControlSource = State3
RowSourceType = "Table/Query"
RowSource = "SELECT tblState.StateID, tblState.Description FROM tblState;"
ColumnWidth = 0";1"
BoundColumn = 1

Now there may be 1 State, 2 States, or 3 States entered for that Time Frame.
How do I enumerate those fields with the current Data in the DataSheet View
in the NEW Configuration so that as I read John Smith's Record I see State#1
= Arizona, State#2 = Washington, State#3 = Delaware or Amber Smith's Record
State#1 = New Hampshire.

Smith, John's PersonID = 5
Wells, Amber's PersonID = 6

tblVisitedStates.PK, tblVisitedStates.PersonID, tblVisitedStates.StateID
1,5,AZ
2,5,WA
3,5,DE
4,6,NH

I have used the BAD table Design for so long I'm suffering a severe mental
block on how to get my desired result with the NEW table Design. Any
suggestions would be greatly appreciated.

- Tom