Unnion query runs two not three queries

[dscudder]

Greetings:

I have a union query to pull witnesses to an incident from three main tables for a report. Consumers, employees, and non-staff. Two of the tables are further linked to others. All queries work alone, and any two will form a union, but I get an error message saying the object could not be found when I try to run the union will all three.

I do not see anything wrong with the code. I am beginning to wonder if my installation of Access 2007 running on XP is corrupted.

Here is my Code:


SELECT NonStaffDetail.IncidentID, NonStaffDetail.ID, NonStaffDetail.Witness, NonStaffDetail.Phone, NonStaffDetail.CofirmOtherStatus, NonStaffDetail.FullName
FROM NonStaffDetail
WHERE (((NonStaffDetail.Witness)=-1))
UNION ALL
SELECT ConsumerDetail.IncidentNum, ConsumerDetail.ConsumerID, ConsumerDetail.Witness, Consumer.Phone, ConsumerDetail.ConfirmConsStatus, ([FirstName] & " " & [LastName]) AS Fullname
FROM Consumer RIGHT JOIN ConsumerDetail ON Consumer.[Client ID] = ConsumerDetail.ConsumerID
WHERE (((ConsumerDetail.Witness)=-1))
UNION ALL
SELECT EmployeeDetail.IncidentID, EmployeeDetail.EmployeeID, EmployeeDetail.Witness, MasterEmployeee.PhoneNum, EmployeeDetail.ConfirmEmpStautus, MasterEmployeee.EmployeeName
FROM MasterEmployeee RIGHT JOIN EmployeeDetail ON MasterEmployeee.EmployeeID = EmployeeDetail.EmployeeID
WHERE (((EmployeeDetail.Witness)=-1));

Even if I simplify this to just one field it behaves the same. What am I missing here?

 

[plog]

All the output field names of your UNION query must be the same. For example these are the first fields in each of your queries:

IncidentID, IncidentNum and IncidentID

That middle query is hosing things up because its called 'IncidentNum' and not 'IncidentID'. You would fix this by using AS after it and calling it 'IncidentID'. Thus the second query should start off like this:

SELECT ConsumerDetail.IncidentNum AS IncidentID, ...

You have errors similar in nature to that throughout. Be sure that all 3 portions of your query return the same field names, forcing them to be named correctly by using AS.

 

[dscudder]

I thought the fields had to be the same type but not the identical name. I double checked this. If I run any two of the queries using the slightly different field names, the union works fine. It only fails when the third query is added. That is what has me stumped. Is it the case that slightly different names are allowed with two queries but not more?

(The select queries that you combine in a union query must have the same number of output fields, in the same order, and with the same or compatible data types. From: http://office.microsoft.com/en-us/a...s-by-using-a-union-query-HA010206109.aspx#BM1)

I see what you are saying though. Give the field an alias in the query so the names are identical.

I am not so far into developing this database that I cannot change the field names.

But I do not think that is the problem, because the union query works fine with two queries and IncidentNum and IncidentID or any of the other slight differences in field name. It is when you add the third query, with IncidentID, or any other field that the error is returned. It does not matter what field or fields I use it only performs the union with two queries not three.

 

[dscudder]

For example, this union query runs fine:

SELECT NonStaffDetail.IncidentID, NonStaffDetail.ID, NonStaffDetail.Witness, NonStaffDetail.Phone, NonStaffDetail.CofirmOtherStatus, NonStaffDetail.FullName
FROM NonStaffDetail
WHERE (((NonStaffDetail.Witness)=-1))
UNION ALL
SELECT ConsumerDetail.IncidentNum, ConsumerDetail.ConsumerID, ConsumerDetail.Witness, Consumer.Phone, ConsumerDetail.ConfirmConsStatus, ([FirstName] & " " & [LastName]) AS Fullname
FROM Consumer RIGHT JOIN ConsumerDetail ON Consumer.[Client ID] = ConsumerDetail.ConsumerID
WHERE (((ConsumerDetail.Witness)=-1));

 

[boblarson]

All the output field names of your UNION query must be the same.

Plog - that is incorrect.

Union queries in Access only need to have the same NUMBER of fields in each. In fact, they can be different datatypes so if they are they will end up as TEXT. But, it is best to make sure to use the same datatypes so that, if you ever move to SQL Server, it will be possible to do so as that does require the same datatypes (as well as the same number of fields).

 

[boblarson]

Dscudder -

Does this table actually have 3 e's in the name:

MasterEmployeee

because that's what you have used

MasterEmployeee.EmployeeName (and the other fields too).

 

[plog]

Thanks bob, and sorry dscudder for giving you wrong info.

 

[dscudder]

Yes; the table is called MasterEmployeee since the very first day I created it. I did notice the typo for some time.

The MasterEmployeee table is a stand in table It is in the database application so I can create a demo. It has only fictitious data and only the fields required for the demo. It will be replaced by the organization's own master employee table when I install the app.

I have found that many of my clients do not have a master employee table. This is because they have different kinds of apps and different kinds of employees. In order to get everything horizontally integrated I create a command station (there are other names for this like enterprise bus) for them which at the very least includes a master employee table with all the various IDs from payroll, state employees in Peoplesoft, external hires, and their EMR system cross referenced. It also includes all the ODBC connections they need to address each app from the command station. In this way they can bypass most of the delay and cost associated with IT vendors and consultants, including me by writing their own queries and reports. Most young IT people know Access and they can quickly be trained to use the command center.

Amazing that an org with 250 employees has no single table with all of their employees and only spread sheets for some critical HR data. One client organization had to call Health Care Services on the phone to find out who worked for them. Of course, any employee can be involved in an a critical incident so it is crucial to have a table with all employees and all the various IDs they may have in various applications.

Yes; this will run on SQL server. When I am done I will upload it to my own server running SQL server express.

So what am I doing wrong with my Union query. Why do any two of them join right but not the third?

 

[boblarson]

Not sure why it isn't working. Have you tried using saved queries instead so you can use

Select * From qry1
Union All
Select * From qry2
Union All
Select * From qry3

Don't know if that will help but it might be worth a shot.

 

[dscudder]

Greetings:

I have been running a few tests and here is what I have seen. It is odd.
(I changed the table name so employee is spelled correct.)

In this example I used a field with an identical name in all three tables. The following code does not run with three queries but it does with any two of the three.

SELECT Consumer.DOB
FROM Consumer
UNION ALL
SELECT MasterEmployee.DOB
FROM MasterEmployee
UNION ALL
SELECT NonStaffDetail.DOB
FROM NonStaffDetail;

It makes no difference whether any or all semi-colons prior to the last are deleted.

But the following code does run correct.

SELECT *
FROM WitnessUnionConsumer
UNION ALL
SELECT *
FROM WitnessUnionEmployee
UNION ALL
SELECT *
FROM WitnessUnionNonStaff;

Each of the named queries above contains the identical code I included in my first post. I do not get it.

Could there be something wrong with my installation of Access? Maybe a *.dll file is corrupted or something? I would think it was my database that is corrupted somehow if the queries did not run alone.

Here is the full text of the error message I get when I run any three queries in a union.

The Microsoft Access database engine could not find the object <name>. Make sure the object exists and that you spell its name and the path name correctly. (Error 3011)

Possible causes:


The specified object does not exist.
You misspelled the object name. Check for missing underscores ( _ ) or other punctuation, and make sure you did not enter leading spaces.
If you are trying to open a Btrieve table, the paths indicated in the data definition file File.ddf may be incorrect. Be sure your data (.dat) files are in the correct directories.

 

[dscudder]

I would like to be able to use the actual code rather than named queries. Having a lot of named queries clutters up the app.

If my install of Access is corrupted I can repair that. But it would be nice if I am still missing something that will make my union queries work.

David

 

[boblarson]

The only thing I can think of is that it is the JOINS and/or the aliasing that is causing the problem. But I know you don't want to use named queries but sometimes there just isn't a choice.

 

[dscudder]

Greetings Bob:

Thanks for your help.

There are no joins or aliases in the following code. It is bread and butter, and all field names are identical:

SELECT Consumer.DOB
FROM Consumer
UNION ALL
SELECT MasterEmployee.DOB
FROM MasterEmployee
UNION ALL
SELECT NonStaffDetail.DOB
FROM NonStaffDetail;

...but it behaves the same. Any two will form a UNION fine, but when you add the third, you get the same error message. I think my instance of Access is corrupted in some way, because there is no reason why the above code should not work.

I am still testing and practicing. I have created a new test db to see if I can do a UNION query there with 3 queries. If it works there, then I have somehow corrupted this db.

If not, then this instance of Access is corrupted. I had some malware sometime back. I got it stopped and quarantined but it did some damage to XT before I got it stopped; about a minute. My desktop is gone, my start up menu is gone and the MyDocuments folder is gone. Fortunately, being an old DOS person I never put anything of value in MyDocuments, except photographs, which I had copies of on an external drive. Since then, I have not noticed anything wrong with any of my applications, which I have to run from the program folders or through another doc, but there may have been some damage to some files I have not noticed until now.

For the time being, though, I especially thank you so much for suggesting that I call saved queries. That did work though why is beyond me.

One related thing I have done, while pursuing the issue of a 3-way full outer join, is to link a UNION query to another query and then use that query in a further UNION query to achieve a 3-way full outer join.

Since you have been so helpful, I want to share this. It is pretty cool. It goes like this: First I created 3 practice tables A, B, and C. They have overlapping data. All three tables are shown below as the result of the final 3-way full outer join.


FullOuterUnion3way
ID DataA DataB DataC
1 DataA1 DataC1
2 DataA2 DataC2
3 DataA3
4 DataA4
5 DataA5
6 DataA6 DataB6
7 DataA7 DataB7 DataC7
8 DataA8 DataB8
9 DataA9 DataB9 DataC9
10 DataA10 DataB10
11 DataB11
12 DataB12
13 DataB13
14 DataB14 DataC14
15 DataB15 DataC15
16 DataC16
17 DataC17
18 DataC18
19 DataC19

You first create a left and right outer join query between tables A and B, then create a UNION between the two queries. Not using the ALL statement eliminates the duplicates and creates the full outer join.

Then you create a left and right query between the first UNION query and TableC. Finally, create a UNION query between those two.

I used an example from the internet for a two way full outer join, which was good because it was simple and labeled the data by the table it came from. It was wrong though because it never included the ID from the second table. The code below corrects that.

The code steps are:
1.SELECT TableA.ID, TableA.DataA, TableB.DataB
FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.ID;

2.SELECT TableB.ID, TableA.DataA, TableB.DataB
FROM TableA RIGHT JOIN TableB ON TableA.ID = TableB.ID;

Notice that the table B ID is in the second query.

Full outer join UNION query:

SELECT TableA.ID, TableA.DataA, TableB.DataB
FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.ID
UNION
SELECT TableB.ID, TableA.DataA, TableB.DataB
FROM TableA RIGHT JOIN TableB ON TableA.ID = TableB.ID;

Notice no ALL statement. This eliminates the duplicates and creates the full outer join. Apparently some applications of SQL permit the statement FULL OUTER JOIN, but not Access.

I had to call this query to complete the last three steps because I am not yet proficient at complex nested queries. But it should not necessary to do it the way I have done it below:

4.SELECT FullOuterUnion.ID, FullOuterUnion.DataA, FullOuterUnion.DataB, TableC.DataC
FROM FullOuterUnion LEFT JOIN TableC ON FullOuterUnion.ID = TableC.ID;

5.SELECT TableC.ID, FullOuterUnion.DataA, FullOuterUnion.DataB, TableC.DataC
FROM FullOuterUnion RIGHT JOIN TableC ON FullOuterUnion.ID = TableC.ID

Notice the ID from Table C is included in query 5. This includes the ID from all three tables.

And the final Union query for the 3-way full outer join is:

6.SELECT FullOuterUnion.ID, FullOuterUnion.DataA, FullOuterUnion.DataB, TableC.DataC
FROM FullOuterUnion LEFT JOIN TableC ON FullOuterUnion.ID = TableC.ID
UNION
SELECT TableC.ID, FullOuterUnion.DataA, FullOuterUnion.DataB, TableC.DataC
FROM FullOuterUnion RIGHT JOIN TableC ON FullOuterUnion.ID = TableC.ID;

The result is the output I started with. It shows all records from all three tables with no duplicates.

Enjoy,

David

PS: During a thunder storm here when the internet was down I ran a test in a new db. The UNION query with 3 queries worked just fine. So my db is corrupted somehow. Any ideas how I should proceed? Should I export the tables, the forms, the reports to a new db? I have backup databases but no forms or reports in them. What do you think?

 

[boblarson]

Import everything into a brand new database shell. (make sure you IMPORT and don't just copy and paste or drag and drop)

 

[dscudder]

Thanks Bob!

Does it sound to you like it is a corrupted db? I do not see any other explanation. But I still do not understand why it would have that symptom when all the other forms and reports are working fine.

It has 23 tables and 7 forms, 6 of which are nested subforms. Why would it balk at a simple query like this? Go figure.

David

 

[boblarson]

Thanks Bob!

Does it sound to you like it is a corrupted db? I do not see any other explanation. But I still do not understand why it would have that symptom when all the other forms and reports are working fine.

It has 23 tables and 7 forms, 6 of which are nested subforms. Why would it balk at a simple query like this? Go figure.

David

yes, it does sound like corruption. When you've ruled out all other possible causes and the problem still remains, it is usually corruption. If a Decompile (not compile - there is a difference) doesn't help then the only remaining hope is to import to a new database and if that doesn't work then you will have to recreate the affected objects.

 

[dscudder]

Thanks. I will work on this but I will no doubt have more Qs.