UNION Queries

[Scott_Withey]

Union Queries

Hi everybody, thanks for your help,

I've writtena union query, which combnes fields from two tables and merges them into one.

The SQL is:-

select tblApplication.[ApplicantID], tblApplication.[Application Pack Sent], tblApplication.[Candidate Withdrew], tblApplication.[Application Pack Received], tblApplication.[NFUM - rejected], tblApplication.[Invited to Assessment Centre], tblApplication.[Offer]
from tblapplication
UNION select tblArchiveNFA.[ApplicantID], tblArchiveNFA.[Application Pack Sent], tblArchiveNFA.[Candidate Withdrew], tblArchiveNFA.[Application Pack Received], tblArchiveNFA.[NFUM - rejected], tblArchiveNFA.[Invited to Assessment Centre], tblArchiveNFA.[Offer]
from tblArchiveNFA;

Does anyone know how I could merge a further field into this union query. This field [Date Info Received] would be merged from a separate table (one not used above 'tblApplicant') with data from a table used above'tblArchiveNFA)

What do you think? Can this be done

Thanks

Scott

 

[JMG]

If I understand your question--you can add a field from one table that is not in the other table. In your SQL, the tables that do not have the field, use "null as [date info received]".

 

[Scott_Withey]

Ok then...

Are you saying that the SQL should be :-

select tblApplication.[ApplicantID], tblApplication.[Application Pack Sent], tblApplication.[Candidate Withdrew], tblApplication.[Application Pack Received], tblApplication.[NFUM - rejected], tblApplication.[Invited to Assessment Centre], tblApplication.[Offer], NULL
from tblapplication
UNION select tblArchiveNFA.[ApplicantID], tblArchiveNFA.[Application Pack Sent], tblArchiveNFA.[Candidate Withdrew], tblArchiveNFA.[Application Pack Received], tblArchiveNFA.[NFUM - rejected], tblArchiveNFA.[Invited to Assessment Centre], tblArchiveNFA.[Offer], tblArchiveNFA.[Date Info Received]
from tblArchiveNFA;
Union select NULL, NULL, NULL, NULL, NULL, NULL, NULL, tblApplicant.[Date Info Received]
from tblApplicant

Scott

 

[Scott_Withey]

A five minute job?

This is the code I've entered for my Union query

select tblApplication.[ApplicantID], tblApplication.[Application Pack Sent], tblApplication.[Candidate Withdrew], tblApplication.[Application Pack Received], tblApplication.[NFUM - rejected], tblApplication.[Invited to Assessment Centre], tblApplication.[Offer]
from tblapplication
UNION select tblArchiveNFA.[ApplicantID], tblArchiveNFA.[Application Pack Sent], tblArchiveNFA.[Candidate Withdrew], tblArchiveNFA.[Application Pack Received], tblArchiveNFA.[NFUM - rejected], tblArchiveNFA.[Invited to Assessment Centre], tblArchiveNFA.[Offer]
from tblArchiveNFA;

This works fine, merging data from the two tables. What I need to do is add an extra field. This field 'Date Info Received' will be merged from tblArchiveNFA (as above) with the same field from tblApplicant (a third table, not used above)

Is there any way this can be achieved?

 

[JMG]

union queries must end up with the same field names in each table select statement. Instead of just the NULL at the end of your first select statement, you should put 'Null as Date Info Received' (you might have to use brackets around your field name because of the spaces in the field name 'Null as [Date Infor Received]') This gives you a field of 'Date Info Received' in the tblApplication query result with a null value as the result because the tblApplication does not have this field in the table.

In your tblArchiveNFA union select statement you must also put the Null as [your field name] for any field that is not in the tblArchiveNFA table that you've requested in select from tblApplication.

In the last union select tblApplicant Null as ApplicantID, Null as [Application Pack Sent], blah blah

Hope I didn't confuse you.

 

[namliam]

If the table tblApplicant can be joined to the table tblapplication

Then yes...

Otherwise No...

Greetz

 

[JMG]

Correct, Namliam, my above post assumes joins have been made. thanks for this correction.
edit:
Scott, do you understand about the joins?
Since I'm not so good with SQL, I start out by using the query wizard with a standard select query, getting the joins and field names as close as possible; then go to SQL view and changing the select statements to union statements. This approach is easier for me.

I learned how to do a union query on this forum. try doing a search on union queries. there are quite a few posts on this subject.