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
Access 2003 Handling Data
-
- 2StarLounger
- Posts: 125
- Joined: 17 Feb 2010, 16:25
Access 2003 Handling Data
PaulW
Lost Wages, NV USA
(former Cobol Programmer)
Lost Wages, NV USA
(former Cobol Programmer)
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: Access 2003 Handling Data
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.
Wendell
You can't see the view if you don't climb the mountain!
You can't see the view if you don't climb the mountain!
-
- Administrator
- Posts: 79435
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access 2003 Handling Data
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.
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.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 125
- Joined: 17 Feb 2010, 16:25
Re: Access 2003 Handling Data
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.
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.
PaulW
Lost Wages, NV USA
(former Cobol Programmer)
Lost Wages, NV USA
(former Cobol Programmer)
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: Access 2003 Handling Data
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.
Wendell
You can't see the view if you don't climb the mountain!
You can't see the view if you don't climb the mountain!
-
- Administrator
- Posts: 79435
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access 2003 Handling Data
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.
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.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 125
- Joined: 17 Feb 2010, 16:25
Re: Access 2003 Handling Data
Thank you both again. I don't know how I would do this project without your help.
PaulW
Lost Wages, NV USA
(former Cobol Programmer)
Lost Wages, NV USA
(former Cobol Programmer)