0

I have a query (query_clients) based on a table (Person). One of the columns I am pulling is a StateAbbr column, which is a lookup to the StateAbbr table. The primary key in the StateAbbr table is the abbreviation itself instead of a numeric key. However, when I run the query, the StateAbbr column shows up as a numeric value instead of the actual value from the column.

I have tried joining on the StateAbbr table to pull the actual column from there, but I must be doing it wrong, because it causes the whole query results to go blank. I have also tried making sure the bound column is correct when I create a form off the table, but changing that value just made the column show up blank instead.

Any suggestions? Thank you.

1

A lookup table with a single value (StateAbbr) should contain two fields: StateId (Primary Key) as an autonumber type, and StateAbbr as a text type. Your Persons table should contain a number type field that will store the value held by the StateId field in the StateAbbr table. So,

tables

The query used would be something like:

SELECT Persons.StateAbbr, States.StateAbbr FROM Persons, States WHERE Persons.StateAbbr = StatesAbbr.StateId;

Paste this statement into the SQL view of your query builder, then switch back to the design view to see the graphical representation.

This will return the actual state abbreviation code that you have assigned in the StateAbbr table.

Edit: I have included a screenshot of a quick sample I threw together based on what you asked, showing the two tables (design view), the relationship between them, and the SQL query that delivers the results I believe you are seeking. Modify these as you need to in order to make it work for you. This screenshot changes the query I provided above to better meet naming conventions for tables and queries.

NOTE: In the Persons table, you will create the State field using the Lookup wizard. Point it to the States table as its source, and only include the StateAbbr field. The PK in the States table will not be shown at all. When filling the data into the Persons table, you will notice that it is a drop-down box where you simply select the state you want.

Person-States Relationship and Query

Please let me know if you need me to clarify anything else.

2
  • My State table is a little different than you have here. Instead of having the autonumber field, the primary key is the state abbreviation. The second column is the state name. Example: StateAbbr(PK) StateName AK Alaska AL Alabama AR Arkansas I have the lookup pulling the primary key (which should be the state abbreviation), however, it is giving me a number instead. – kgrafton Jul 18 '16 at 21:44
  • Please see the edit to my answer above.. – rattler418 Jul 19 '16 at 21:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.