UNION Query Issue - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

UNION Query Issue

David Semon

3 years ago

I have a form with an unbound combobox that I use to filter the records based on the name selected in the combobox. I am trying to add an "ALL" selection to the combobox so the user can easliy select all the records in the table. After much trial and error, I got everything working EXCEPT the UNION I used adds two ALL items to the combobox instead of just one. Here is the SQL statement I am using for the combobox row source:

SELECT qryMedicalPatients.PersonID, qryMedicalPatients.FullName
FROM qryMedicalPatients
ORDER BY qryMedicalPatients.FullName
UNION ALL
SELECT 99 AS PersonID, 'ALL'
FROM qryMedicalPatients;

Anyone know why this adds two items instead of one?

Thanks.

	Kevin Robertson @Reply 3 years ago
Use `UNION` instead of `UNION ALL`.

	David Semon OP @Reply 3 years ago
Thanks, Kevin. Works great.

	Ricky Houtris @Reply 3 years ago
You should make the union FROM another table, not the table that you are making a union with. It is not going to use the other table, you just need a name.

	David Semon OP @Reply 3 years ago
Ricky, Do you mean the name of the FROM table doesn't matter? This union is not with another table. I'm just adding another item to the combobox - ALL. Even though I got it to work, I'm still trying to understand how this works. UNIONs between two real tables, I think I understand. But, this is different. Thanks for your input.

Ricky Houtris @Reply

3 years ago

David,
If it worked I guess that it is okay, but I was taught that the FROM statement is where the data is coming from. The first FROM statement in a union is the first source and the destination table and any following ones are just source tables. So, pulling data from the table you are putting it in, would be bad form. With this kind of union, it's not important where the data is coming from as you are creating the all data for the union with your 99, and 'ALL'. You are not pulling it from the table. I guess that I miss-spoke.
Ricky

Ricky Houtris @Reply

3 years ago

Just for clarity, David,
The ALL in the union statement allows the addition of what appears to be a duplicate. Either with the entries that already exist in the first table, or in any of the tables making the union. You should only use it when you are using that the data you are adding has no duplicates. I use it when I am making weekly updates to a table with newly recorded data that may be on the same day, with the same tasks, for the same amount of time, but that are not duplicates. Without the ALL, these apparent duplicates would be removed.
Ricky

Kevin Robertson @Reply

3 years ago

Ricky,
I think you are mis-understanding what David is doing. He is simply adding the word 'All' to the query results which will be used for feeding a combo box for filtering purposes. I use this same technique myself except I do it in VBA code using a recordset.

David,
I would set the ID for your 'All' option to 0 instead of 99. If you have more than 99 actual records you will end up with 2 IDs of 99 and there will be conflicts.

	David Semon OP @Reply 3 years ago
Kevin and Ricky, Thanks for the explanations. They were very helpful. I actually thought about the problem 99 could create and changed it to 0 myself. Part of my "try it" and see what happens technique (as suggested by Richard). Thanks again.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View