Query Results show Primary key (1 Viewer)

[Teri Bridges]

I have 2 tables, each had a Status ID both are set to number. When I run my query I am getting the primary key from my status table (Column 0) I would like my query to show the value (Column 1). Can anyone help?

 

[plog]

First there are no columns in tables. There are fields and when you want them to show in a query you must explicitly do so by putting the field name in the SELECT clause of the query. Do that and it will show.

If you need further help with this, post the SQL of your query.

 

[Pat Hartman]

If you are using a table level lookup, you have run into one of the situations where they don't work as you expected. Experts write all the time about why you should not use them starting with - there is NO REASON to use them since users NEVER, EVER see naked queries. Users interact with forms and reports. YOU are the only one who cares about what you see when you open a table or a query and YOU are in complete control over what is presented. YOU just need to join the main table to the lookup table if you want to see the text instead of the ID. This is exactly what Access is doing behind the scenes.

Using a table level lookup or a format on a column in a table just obfuscates the data and caused unexpected problems. I just fixed one a few days ago for a client where he had put a Short Date format on a date field. All that did was to hide the time values. So in his queries, he wasn't getting the results he expected. He hid the times so he forgot they were there. If he hadn't hidden them, he would have seen the error immediately and would not have had to pay me to tell him what was wrong and then spend a half hour searching for places where he was using Now() when he should have been using Date() and running update queries to get rid of the times in fields where they were not relevant.

Bottom line - remove the table level lookup. Use combos on forms. Use joins in queries if you need to see the text value in a query.

 

[Teri Bridges]

My apologies for clearly displaying my ignorance. I created a query from my Table and included the StatusID field. Based on that query I created a form. In the form I needed the StatusID field to be a dropdown from the StatusTBL. So I deleted the existing StatusID field and added a COBStatus ID field. The user can now select the statusID from a list. The form reflects the Status value and not the Primary key, which is what I want. But the query and the report reflect the primary key.

If you are using a table level lookup, you have run into one of the situations where they don't work as you expected. Experts write all the time about why you should not use them starting with - there is NO REASON to use them since users NEVER, EVER see naked queries. Users interact with forms and reports. YOU are the only one who cares about what you see when you open a table or a query and YOU are in complete control over what is presented. YOU just need to join the main table to the lookup table if you want to see the text instead of the ID. This is exactly what Access is doing behind the scenes.

Using a table level lookup or a format on a column in a table just obfuscates the data and caused unexpected problems. I just fixed one a few days ago for a client where he had put a Short Date format on a date field. All that did was to hide the time values. So in his queries, he wasn't getting the results he expected. He hid the times so he forgot they were there. If he hadn't hidden them, he would have seen the error immediately and would not have had to pay me to tell him what was wrong and then spend a half hour searching for places where he was using Now() when he should have been using Date() and running update queries to get rid of the times in fields where they were not relevant.

Bottom line - remove the table level lookup. Use combos on forms. Use joins in queries if you need to see the text value in a query.

No I am not using a table look up. I added a combo box to my form to pull the list from the status table and store in the status ID field of my table. I get the correct value in the form for example "In process" but in the query and main table I get "1".

I am new and learning more everyday, so please excuse me if my questions are too basic. I do apologize.

 

[Teri Bridges]

Well I am stumped, every time I think I get it figured out something else goes wrong. Now when I make an entry on the form it is changing the data in the reference table no a random number value.

 

[Pat Hartman]

I get the correct value in the form for example "In process" but in the query and main table I get "1".

The table will ALWAYS show ONLY the FK value. It will never show the text value unless you use a table level lookup which I strongly recommend against. Same thing with a query. If you want a query to also show the text value, you add a LEFT join to the lookup table and select the text field from the lookup table. Just to be clear, The FK is selected from the main table because that is the field you want to bind to the combo and update if something changes.

There does not appear to be anything wrong with the query. You will have to post data samples (preferably the database with any sensitive data obscured) if you want us to figure out how random numbers are being generated.

 

[Pat Hartman]

@Teri Bridges You "liked" my reply. Does that mean that your problem is resolved? If so please tell us what the problem was and mark the thread solved. If not, then please post the database if you can or if not, then some data, the form and the queries so we can examine all the properties.

 

[Teri Bridges]

@Teri Bridges You "liked" my reply. Does that mean that your problem is resolved? If so please tell us what the problem was and mark the thread solved. If not, then please post the database if you can or if not, then some data, the form and the queries so we can examine all the properties.

Sorry, no Not resolved, I am trying to work out the left joins and right joins so I clearly understand. I liked your post because it gave me a direction. Right now I am basically only using right joins by the looks of it. When I add a left join I get no results in my query.

 

[plog]

Mixing LEFT and RIGHT JOINS is unkosher. In fact, I suggest you throw RIGHT JOINs away and never use them. You'll rarely see them in the wild.

...FROM A LEFT JOIN B RIGHT JOIN C LEFT JOIN D... gives---no idea

That may not even be valid SQL, but the point is even if it is valid, where do you start trying to figure out the main data source? Discarding RIGHT JOINs makes SQL easier to understand--the main datasource of the query is always right after the FROM. And you lose nothing from never using RIGHT JOINS:

...FROM A RIGHT JOIN B... gives all results from B and if anything in A matches it gives them
...FROM B LEFT JOIN A... gives all results from B and if anything in A matches it gives them

Those are equivalent, so no need for both.

 

[Pat Hartman]

Access chooses a left or a right join based on the position of the tables in the grid. That's all it means. Logically, it is much easier for people to put the driving table on the left and that makes the join to the optional table a left join. So. start again with a new query and add the table where you want ALL the rows first. Then add the table where you want matching rows IF THEY EXIST. Draw the join lines and you will see in the dialog that the left join is the one you want.