Union Queries

[tarhim47]

SOLVED!



Hi all,

I need to combine results from two different forms. So I created a union query but it only returns the results of one of the tables.

Here's what I did:

I made a query for lead discussions with COIs
- form: Lead Activity Report
- fields: date of last contact, COI and comments - COI

I then made a qury for general discussions with COIs
- form: COI Discussion Form
- fields: date, COI and general comments

Fianlly, I created the union query in SQL view. See below.

SELECT [Lead Activity Report].[Date of last contact], [Lead Activity Report].COI, [Lead Activity Report].[Comments - COI]
FROM [Lead Activity Report]
UNION ALL
SELECT [COI Discussion Form].Date, [COI Discussion Form].COI, [COI Discussion Form].[Discussion points]
FROM [COI Discussion Form];

What did I do wrong? Your help is appreciated!

Thanks.

P.S. my dbase is attached

[alansidman]

To create a Union Query, your fields must be named exactly the same and be in the same sequence. You will need to create an alias for the fields in one of your tables to make them match.

Alan

[kennejd]

I respectfully disagree with Alan. The fields don't have to be named exactly the same, but each query has to have the same number of fields....and the same data types in the matching fields.

Your union query is working fine. You have 13 records in the lead activity report table and 1 record in the COI discussion form table.

The overall design of your database could use some work, though.

[ajetrumpet]

The fields don't have to be named exactly the same, but each query has to have the same number of fields....and the same data types in the matching fields.

CORRECT. But then, you already knew that didn't you kenny?

[alansidman]

Kenny;

The fields don't have to be named exactly the same,

Thanks. The great thing about forums is that if you are wrong, someone will correct you and that is a great learning experience. This is something I did not know. If you don't have the fields named the same, then what field name appears in the query result? The first one, second, third, etc. or some combination or something else?

Alan

[kennejd]

The field name of the first query appears in the result.

[tarhim47]

Thanks for your help!

I figured out that the query is working just fine. As far as the overall dbase is concerned, it is very much in the first stage. Having said that, if you have any tips, I would welcome them.

Thanks again.