Union Query and returning the value instead of the key

[rich]

I've read this thread https://www.accessforums.net/queries...eys-18435.html and the associated external link http://access.mvps.org/access/lookupfields.htm several times - I'm having essentially the same issue. I'm not sure if I'm reading it wrong, but it looks like a bunch of statements without a solution. How am I supposed to deal with this situation?

I have a database that for all intents has four tables

1. key information (standard primary key, key number is alphanumeric and stored as a text object).


2. signout information - long term
3. signout information - short term
4. user information

I have two queries set up that return all keys currently signed out. These work fine, and return the correct results.

What I need to do next is create a query that puts together both the long term and short term keys signed out, and then filters so that it only includes results from the named individual input (the point being for us to query someone who leaves so we can get their keys back). I've created a union query from the two queries that return all keys signed out, but instead of returning the key number, it returns the primary key for the key number. It actually works fine when I create the query, but when I save, close and re-run it, it starts returning the primary key.

My SQL for this query looks like this -

SELECT Who, [Key Number]
FROM qry_allcurrentshorttermout
UNION ALL
SELECT Who, [Key Number]
FROM qry_allcurrentlongtermsignout;

I don't understand why my long and short term queries on their own are returning the correct values for key numbers, but another query based on them doesn't.

Can anyone help me see the light?

[June7]

Need to review db if you want to provide. Follow instructions at bottom of my post.

[rich]

Need to review db if you want to provide. Follow instructions at bottom of my post.

Thanks for the offer. The database is attached. It's already been split, so there are two parts to it. Note that when you go to connect the two parts, there is a third database that also works with this - you will not be able to connect the Contact Info table. Hopefully this won't mean nothing works.

The query I'm having trouble with is qry_allsignout. The point of this query at present is to connect the qry_allcurrentlongtermsignout and qry_allcurrentshorttermout queries.


Thanks,


Rich

[orange]

There is no attached database.

Can you post a jpg of your tables and relationships?

[rich]

Oops! Zip file of the database now attached and hopefully the image showing relationships and tables (not sure why its appearing so small). The keys to longtermsignout and the keys to short term sign out are one to many relationships.

[June7]

Yes, you have lookups set in table. I NEVER do this.

The field called Key Number in longtermsignout and shorttermsignout shows the Key Number but the actual value in the table is the ID. Remove the lookup RowSource SQL from the table and you will see this.

This means the UNION will show the actual value (ID), not the alias (Key Number). Also, any expressions in a query using the field will see the actual value, not the alias.

Either make the Key Number field the PK and save this value (eliminates the autonumber IDs) or the SELECT query must be a join of tables on the ID pk/fk fields so that the related info (Key Number) will be available.

This is a perfect demonstration of the 'evils' of lookups in tables.

[rich]

Thanks for the reply. I had thought about changing the PK so that its actually the key number. That would work since they are unique. I'm trying to get my head around the last half of the penultimate sentence. I'll work on this.

I am curious for future designs though, I'm hearing (and experiencing) what is bad about using lookups, but what is the alternative?

[orange]

The alternative is to create a separate Table, and include a field to be the FK(foreign key).
Then create a Relationship from your Main Table and set Referential Integrity. The value in the
PK and FK will be the same for related records.

see a free video example at https://www.youtube.com/watch?v=WqxzwF9GcQg

[rich]

Wow, that video really explained the concept nicely! Thanks for the link. Even though I need to work on this, I'm marking this as solved.

Thanks again for the help.

[orange]

Glad to help.
There is an excellent series by 599CD -- Search youtube for
599cd Microsoft Access 2010 Tutorial Part of 12

These go from 01 to 12 --Richard Rost does a great job and these ones are free.
see https://www.youtube.com/playlist?lis...E1E1F86126CBA0

[June7]

I do avoid lookups as much as possible but if needed I just don't set them in tables. I actually seldom use autonumber as PK. Memory is cheap these days. So instead of using autonumber field as PK, I designate the unique descriptor as PK. Instead of saving 1, 2, 3, as FK, I would save: Green, Blue, Orange.

[orange]

Really?
Good description of autonumbers here
http://www.utteraccess.com/wiki/index.php/Autonumbers

Discussion at http://www.utteraccess.com/forum/Uni...-t1995206.html

[June7]

My main db uses a custom generated unique ID (SampleNum) as primary/foreign key value. Also, instead of saving an autonum ID for short descriptors like: Preconstruction, Information, Acceptance, Assurance, I just save those descriptors - no lookups needed. Even with this bloating, the db should never exceed 2gb in 20 years (the life of the dBase legacy db Access replaced) - 4 years now and only 130mb of data. I did use autonumber for one relationship, otherwise would have been compound ID with 5 fields. I appreciate compound IDs even less than lookups.

I have another db that requires importing files from project site db at end of the season. Autonumbers really got in the way and was simpler just not to use them. Ironically, this meant I did accept one compound ID (2 fields) relationship.

I have another db where autonumber pk was the easiest solution.

Hey, I can be flexible!

[orange]

And that's the key -- there are many ways to achieve what you need/want. Use what works.
On a simple lookup table I wouldn't use autonumbers. But I have seen designs screwed up on a simple 10 prov 2 territory Canadian "Prov" table. People used a prov/territory abbreviation as pk -- when nunavut came along the scheme didn't work, and NF (Newfoundland_Labrador) switched to NL. So numeric, meaningless PK has its place (from experience).
On some relationships where you have compound fields for keys, I prefer to use a single autonumber, and create a multifield unique index to prevent duplicates.
I think flexibility is the answer.