Union Query and ID numbers!

[Ella1981]

Hi All,

I have just designed my first union query using help from various web sources. However, I seem to have run into a problem caused by my lack of database design experience.

I have used two different lookup tables which in the single queries, work perfectly. However, when I run the union query I get the ID numbers rather than the corresponding text.

I have since read up on this and appreciate that it is due to the use of lookup fields. I have also read that rather than getting rid of the lookup, I can change the SQL code to incorporate the relevant tables.

How do I do this and what is the syntax?? One table is called 'Assessors List' and has two fields 'ID' and 'AssessorID' (which is a person's name), the other is called 'Placement List' which has two fields 'ID' and 'Placement' (The name of a place of work). I want the person's name and the place of work to appear in the union query as they do in the original queries that form the union query.

Can someone please give me a step by step guide in how I bring in these tables??

Thanks in advance

Ella

 

[Pat Hartman]

Before you can create queries that will work as you expect, you MUST get rid of the lookup in the original table. Change the "combo" back to "text" and that will do it. To do the lookup yourself, join the main table to the lookup table and select the ID column from the main table (because that's the one that may change on your form) and the text value from the lookup table. Instead of your query containing a value you can't depend on (sometimes Accesss will treat it as numeric and sometimes as text and sometimes it will show the ID and sometimes it will show the text), it will contain both the ID and the text and you can work with either or both as your requirements dictate.

You will still use lookups on forms. You'll just have to create them there using the same technique you used to create them in the table. I know it seems like this makes more work but unfortunately, the implementation of table level lookups simply causes more problems than it solves.

 

[ariansman]

Many ones here disapprove loock-up. I have no idea as i don't have much experience about access.
However, i think you can run a select query on the Union query to extract and show the names instead of the IDs.

 

[Pat Hartman]

We disapprove because THEY DON'T WORK so rather than coding around them - just get rid of them!!!! This isn't a personal opinion. I tell you when I'm giving my opinion. If something just doesn't work and you know it, why spend your time trying to get around it when there is a viable alternative that people "in the Know" use anyway?