Access 2003 Handling Data

[PaulW]

I have a design question. I have a table and would like to have up to three possible entries to the data in a combo box to select the data:

Combo Box Data:
LastName,FirstName,RecordKey
LastName,AlternateFirstName,RecordKey
CompanyName," ",RecordKey

The record key points to the entire record of 30+ fields.

I currently have a maketable query, two append queries and a sort query.

Is there a better way to accomplish this? In Cobol, I would use one program with a Sort-In procedure and a passed dataset output.

TIA

[Wendell]

I assume the data is coming from three different tables, or at least three different kinds of records in a single table. If that is the case, you could use a union query to pull all of those into a combo box as the row source. We often use that trick when we let users filter data based on a year, the current year, or all years.

[HansV]

You can create a union query, save it and use its name as row source of the combo box. A union query has to be designed in SQL view. It would look like this:

SELECT LastName, FirstName, RecordKey FROM tblThis
UNION SELECT LastName, AlternateFirstName, RecordKey FROM tblThat
UNION SELECT CompanyName, "", RecordKey FROM tblOther

The table names may be the same or different, as applicable.

[PaulW]

Firstly, thank you both for the replies. I have three possible entries ( zero to three) from each row in one table. I must parse the AlternateFirstName from data in a field and I will not output a CompanyName record if the company name field is blank.

So, I can make three plain select querys and union them as per instructions? Will I be able to sort the output of the union query on the first field? Will the first three queries be automatically executed by referring to the union query as the source for the combo box as is the case with a plain vanilla query?

TIA Again.

[Wendell]

Yes, you can use three separate queries as the data source and then return the results in a UNION query as we suggested. And UNION queries automatically sort on the first column, and the select queries are automatically run by the UNION query. On issue you might think about is duplicate values - if you somehow get duplicate records, how do you distinguish between them? I assume that you are storing the RecordKey value in the table where the results of the combo box are stored.

[HansV]

You can add a WHERE clause to each of the SELECT parts, and you can add an ORDER BY clause to sort the records at the end:

SELECT LastName, FirstName, RecordKey FROM tblThis
UNION SELECT LastName, AlternateFirstName, RecordKey FROM tblThat WHERE FirstName Is Null
UNION SELECT CompanyName, "", RecordKey FROM tblOther WHERE CompanyName Is Not Null
ORDER BY LastName, FirstName

If you open a union query or use it as row source for a combo box, each of the SELECT statements will be executed, and the results merged into one set of records.

[PaulW]

Thank you both again. I don't know how I would do this project without your help.