how to show the name instead of the ID?

[ariansman]

The field “costumer-ID” on the “order table” is a foreign key from “costumer table”, field “costumer-ID”. I managed the consumer name to be shown in the “order table” field “costumer-ID” instead of the number. But when I run a union query using field “costumer ID” from “order table, only the ID number of the customer is shown. How can make the costumer name to be shown instead?

 

[pr2-eugin]

I believe you can use a Join along with the Union Query to give you the desired result..
More on this can be found at:
http://www.tizag.com/sqlTutorial/sqlunion.php

 

[boblarson]

It would appear that you have used lookups at table level. I would HIGHLY SUGGEST that you do NOT do that (see here for more on why: http://www.mvps.org/access/lookupfields.htm )

Remove them and you should be good to go.

 

[ariansman]

I believe you can use a Join along with the Union Query to give you the desired result..
More on this can be found at:
http://www.tizag.com/sqlTutorial/sqlunion.php

sorry, in fact i did a full outer join between two tables. However, it seems to me that the problem is not about the type of the query, but it is about how to show the names back instead of the ID numbers.

 

[ariansman]

It would appear that you have used lookups at table level. I would HIGHLY SUGGEST that you do NOT do that (see here for more on why: http://www.mvps.org/access/lookupfields.htm )

Remove them and you should be good to go.

sorry i did not understand much on that page. As far as i know, Access suggests that tables be related by ID-field of the parent table. So you mean, we simply use the desired field instead of ID-field in queries?

 

[spikepl]

I managed the consumer name to be shown in the “order table” field “costumer-ID” instead of the number.

This is what Bob referred to as table-level lookup. Do not do this. One of the consequences you have already felt :confusion as to what really is in that field.

 

[Pat Hartman]

sorry i did not understand much on that page

The page listed some of the probems you will encounter if you have lookups defined in your tables. The problem you report, is not in the list and I'll send a note to Arvin to ask him to add it.
Once you remove the lookup from the table, joins will work and so you can modify your queries to include the lookup table. That will let you choose the ID from the main table and the descriptive name from the lookup table - this is the normal way. Then in your forms, you will use combo boxes which will let the user select by name but will store the ID.

 

[ariansman]

The page listed some of the probems you will encounter if you have lookups defined in your tables. The problem you report, is not in the list and I'll send a note to Arvin to ask him to add it.
Once you remove the lookup from the table, joins will work and so you can modify your queries to include the lookup table. That will let you choose the ID from the main table and the descriptive name from the lookup table - this is the normal way. Then in your forms, you will use combo boxes which will let the user select by name but will store the ID.

Thank you,
How can i perform this? Shall i delete the ID field from the query in "field properties"/look-up/row source? should i create a lookup table?

 

[Pat Hartman]

Do NOT delete the ID fields. Just get rid of the lookup by changing back to a text box.