Table Foreign Key. Access 2003, Windows XP.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Datasheet view returns autonumber instead of field value.
I have the following tables:
1) a table for rank codes which only has two fields, autonumber (primary
key) and duty position rank.
2) a table for personnel grade codes which has three fields, autonumber
(primary key), duty position grade, and a lookup field for the corresponding
rank since some duty position grades can have multiple duty position ranks.
3) a table for inbound personnel.

On the third table I created a field called grade foreign key which is a
lookup field that shows all three fields in the second table above. When I
open this table in datasheet view and select the individual's grade I expect
to see, for example, 1, Ms. or 1, Mr. but instead the value 1, 1 is
returned...it is showing me the correct grade but instead of the rank value
it is returning the rank primary key.

The lookup properties for grade foreign key are:
SELECT tblGrade.GradePK, tblGrade.Grade, tblGrade.RankFK FROM tblGrade ORDER
BY tblGrade.Grade;
Bound Column 1
Column Count 3
Column Widths 0";1";1"
 
Datasheet view returns autonumber instead of field value.
Click to expand...

That's because the table actually CONTAINS the autonumber value.
I have the following tables:
1) a table for rank codes which only has two fields, autonumber (primary
key) and duty position rank.
2) a table for personnel grade codes which has three fields, autonumber
(primary key), duty position grade, and a lookup field for the corresponding
rank since some duty position grades can have multiple duty position ranks.
Click to expand...

See

http://www.mvps.org/access/lookupfields.htm

for a critique of the Lookup field misfeature. What you're experiencing is
just one of the many, many problems with Lookup fields in tables. The table
actually *contains* a Long Integer foreign key; this basic fact is concealed
from your view by the Combo Box created by the lookup wizard.
3) a table for inbound personnel.

On the third table I created a field called grade foreign key which is a
lookup field that shows all three fields in the second table above. When I
open this table in datasheet view and select the individual's grade I expect
to see, for example, 1, Ms. or 1, Mr. but instead the value 1, 1 is
returned...it is showing me the correct grade but instead of the rank value
it is returning the rank primary key.
Click to expand...

Exactly. Because *that is what is in the table*.
The lookup properties for grade foreign key are:
SELECT tblGrade.GradePK, tblGrade.Grade, tblGrade.RankFK FROM tblGrade ORDER
BY tblGrade.Grade;
Bound Column 1
Column Count 3
Column Widths 0";1";1"
Click to expand...

Junk your lookup fields (change the Lookup tab from Combo Box to Text Box); if
you want to see the grade in conjunction with the name, use a Query joining
the two tables, or use a Form with a combo box on it, bound to the RankFK but
displaying the Grade.

John W. Vinson [MVP]
 
Mr. Vinson, Thank you!

Need more help now :0)

I've read through the article, junked the lookup fields, created a query
linking all the required tables, created a form that displays the data
perfectly and records just the primary keys, and then...created a report to
view the data and on the report I get the primary keys vice the field values.
What do I do to correct this issue?
 
Disregard: Residual Operator Headspace!

I had read through the article you referenced many times before posting the
question but until I tried executing your advice I couldn't grasp how to
begin...thank you for the post, you really helped me out and it is much
appreciated!
 
I've read through the article, junked the lookup fields, created a query
linking all the required tables, created a form that displays the data
perfectly and records just the primary keys, and then...created a report to
view the data and on the report I get the primary keys vice the field values.
What do I do to correct this issue?
Click to expand...

Base the Report *on the query*, not on the table.

Generally, though, you should *not* create a Big Master Query and then base a
Form on that query for data entry! Instead put combo boxes ("lookups" if you
will) on the Form to allow the user to see and select the human-meaningful
data, while storing the numeric ID. For one-to-many relationships you would
use a Subform - a Form for the "one" side table, with a Subform (or more than
one) for the Many; again, the form and subform will make liberal use of combo
boxes, listboxes, and the other tools Access provides.

A Report, on the other hand, may involve Reports and Subreports, and can even
use combo boxes; but in general it's better and simpler to base a Report on a
query containing all of the tables that you want, and use the report's Sorting
and Grouping dialog to lay the data out as desired. A multitable query very
likely won't be updateable; this is a problem for a data entry form, but of
course is not an issue for a display-only report.

John W. Vinson [MVP]
 
Are the relationships created then in the relationships toolbar and therefore
not required to be created in a query, which gives the ID values meaning?

This is the first I've been presented with not using a master query for
creating forms but may very well explain some of the great difficulty I've
had.
 
Are the relationships created then in the relationships toolbar and therefore
not required to be created in a query, which gives the ID values meaning?
Click to expand...

The relationships window does two things:

- It sets up referential integrity between tables (so that you cannot enter a
"child" record if there is no corresponding "parent" record)
- It sets the automatic default join for when you create a query

You can create Queries joining tables using the joins specified in the
relationships window; or joining on different fields; or not joined at all; or
you can create a query joining two tables which weren't joined in the
relationships window at all. Queries are *almost* completely independent of
relationships, in other words!
This is the first I've been presented with not using a master query for
creating forms but may very well explain some of the great difficulty I've
had.
Click to expand...

Well... you're fairly far down the wrong track then.

You may want to take a look at how the sample Northwind database builds forms,
or check out some of the tutorials at

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson [MVP]
 
Back
Top