Query Results Display Primary Key Value, Not Text Value

[Rik_StHelens]

Hi

I have a query which pulls vehicle inspection data, and invoice data resulting from any faults found.

I have several query fields which should display a value which, at point of data entry, was selected from a combo box pointing at a table.

On the input form in the combo box the text field from the table is shown correctly.

However, when I run the query, it gives me the primary key value, and not the text value associated with it.

I have tried using the value tables as lookups, with a link between the value shown in the query, and the primary key of the field in the table. This has given me a type mismatch, but i don't understand how this can be?

I didn't build this DB, and I've never encountered this before.

Any help/ideas would be appreciated.

Many Thanks

 

[DCrake]

From what you say the data type foreign key in your main table and the primary key in your lookup table are different. Make sure they are both the same. Not Text v Number or Integer v Long. When you have sorted that you will be able to create the join and use the description from the lookup table.

 

[Rik_StHelens]

Thats what I don't understand though.

I have checked the data types in both tables, and they match.

If I am using the table from which the original value derived, as a lookup, then their should be no type mismatch.

Which poses the question, why does the table which the query selects from display the value, whereas the query only displays the fields index number? The index number field is not even in the table itself.

 

[DCrake]

Can you send a copy of the offending tables

 

[Rik_StHelens]

Please find attached the 2 tables and the form which lies over the top of the inspections table

I really appreciate your help!

 

[DCrake]

You have fallen into a big trap that is common for users not versed in NOT using table level lookups. My recommendation is to remove the combo box lookups at table level then change the data types to long interger.

As users do not, or should not be using datasheets to view data it is not necessary to employ the table level lookups.

I have changed Recommendation1 to how it should be. Look at query1 to view the reationship/description

If you repeat for all recommendations you will have to add the recommedations table for each one in the inspection table.

I should say however this strucure is very denormalised.

 

[Rik_StHelens]

Thanks again for your reply

Like I said, I never built this particular DB, but I do use it from time to time to compare work actioned to invoices etc.

I was thrown by the missing relationships at table level.

Still, i'll make the changes to it and get everyone updated.... mad

many thanks again

 

[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?

 

[Pat Hartman]

Please don't post the same question in multiple places. Just wait until someone answers the first question. If you think a minute isn't enough time to get a response, you should probably chill. Everyone who posts here is a volunteer. NO ONE gets paid to help you. I answered your question in the other thread.

Query Results show Primary key

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?

www.access-programmers.co.uk