Ms-access – Query showing a number instead of the column value

ms accessms-access-2016

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.

Best Answer

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,

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.

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

Related Solutions

Ms-access – Automatic creation of unique Alphanumeric ID

This will work. There may be something more elegant but this should get you started.

 Public Function GetUID() As String
      GetUID = "G" & Right("000" + CStr((CInt(Right(DMax("[YourIDField]", "TableContainingField"), 3)) + 1)), 3)
 End Function

Ms-access – MS Access: How to automatically fill in ID field based on value of another field

Assuming that you know how to edit and run a SQL query in Access, I think you need to execute a SQL Query similar to the following:

    UPDATE FoodPurchases
    INNER JOIN Vendors ON FoodPurchases.VendorName = Vendors.VendorName
    SET FoodPurchases.VendorId  = Vendors.VendorId

I have not verified the above, so let me know if it does not work.

Best Answer

Related Solutions

Ms-access – Automatic creation of unique Alphanumeric ID

Ms-access – MS Access: How to automatically fill in ID field based on value of another field

Related Question