either / or combo box

[SueBK]

I have a table of corporation names. This is three fields - CorpID; CorpName; CorpAbbr (ie Record #1 is "Environmental Protection Agency", EPA). The data is used in combination with other tables (eg against report titles as a the corp owner).

Where this data is available to users on forms for input I'd ideally like both name and abbreviation available as a single list. For example: if the user types "Environmental Protection Agency" it picks up record #1; if they type "EPA" it would also pick up record #1. I don't want two records in my corporations table. I don't want people typing "EPA" and thinking its not there so adding it as a new record.

Is this possible at all? Is this something I tackle at the table level with lookups? or is on the form only? If it's on the form, how do I get the data to transfer into my table for 'keeping'?

 

[MarkK]

You could UNION together two queries on the same table like...

SELECT CorpID, CorpName As Corp FROM tCorporation
UNION
SELECT CorpID, CorpAbbr As Corp FROM tCorporation

So if you use that SQL as a rowsource in a combo box the user could choose either the CorpName or the CorpAbbr and it would yield the same ID value.
Or maybe ...

SELECT CorpID, Corp 
FROM 
    ( 
    SELECT CorpID, CorpName As Corp FROM tCorporation
    UNION
    SELECT CorpID, CorpAbbr As Corp FROM tCorporation 
    )
ORDER BY Corp

Which just asserts a sort order on 'Corp'.

 

[ajetrumpet]

Another thing you could do sue is to use the table lookups whenever a record is added. It's slower, but it works. I think lagbolt is going the combo box route, which is fine too, and faster.

If they're typing the values in though, better to do a lookup i think...

 

[neileg]

Adam, I can't understand why you are advising table lookups. They are truely the devil's work.

 

[DCrake]

To add flavour to Lagbolts suggestion

SELECT CorpID, Corp 
FROM 
    ( 
    SELECT CorpID, CorpName As Corp FROM tCorporation
    UNION
    SELECT CorpID, CorpAbbr As Corp FROM tCorporation 
    )
ORDER BY Corp

I would be tempted to add to this as follows

SELECT CorpID, Corp 
FROM 
    ( 
    SELECT CorpID, CorpName As Corp, 1 As sOrder FROM tCorporation 
    UNION
    SELECT CorpID, CorpAbbr As Corp, 2 As sOrder FROM tCorporation 
    )
ORDER BY sOrder, Corp

This ensures that all the full names appear together and all the abbreviations appear together.

David

 

[MarkK]

Yeah, but if you want all the full names and all the abbrevs together you don't need the subquery...

SELECT CorpID, CorpName As Corp FROM tCorporation ORDER BY CorpName
UNION
SELECT CorpID, CorpAbbr As Corp FROM tCorporation ORDER BY CorpAbbr