Union Query Help

[Yoyo]

I am not an expert user of Access. I am trying to Union two Tables but I am looking to pull columns from one table that is not part of table 2. Is there a way to do this.
​

Table 1
Field 1 Field 2 Field 3 Field 4
data data data data
data data data data
data data data data
Table 2
Field 1 Field 2 Field 3
data data data
data data data
data data data
New Union Query with both tables
field 1 Field 2 Field 3 Field 4
data data data data
data data data
data data data
data data data data
data data data data
data data data

 

[Brianwarnock]

The queries used in a Union query must have the same number of output fields.

 

[Brianwarnock]

This might help

http://office.microsoft.com/en-gb/a...eries-by-using-a-union-query-HA010206109.aspx


You could create a dummy Null field

Brian

 

[Yoyo]

thanks. do you know if there is a way to add a null value for the fourth field of table 2? Basically when I run the union anything in table 2 will not have a value in field 4.

I am not sure if I am explaining this correctly so sorry in advance.

 

[Brianwarnock]

You posted whilst I was editing
Try

Field4: ""

This will create a zero length field

Perhaps

Field4:

Will create a null field, I've never done it.

Brian

 

[pr2-eugin]

Hello yoyo, Welcome to AWF..

How about something like..

SELECT tableA.field1, tableA.field2, tableA.field3, tableA.field4
FROM tableA
UNION
SELECT tableB.field1, tableB.field2, tableB.field3, "" AS emptyfield
FROM tableB;

EDIT: I need to stop doing this.. LOL.. Opening windows and then drifted to something else.. Ha ha.. But I think I am in line with Brian's thinking..

 

[Brianwarnock]

Paul I notice that you use " " ie a blank, isn't it better to use a zero length field. I hope that it is not a date field by the way.

Brian

 

[Yoyo]

technically that skips the field - however when I hit run it still looks for a parameter value. I have a feeling I can't do what I need on this one.

SELECT [EMPLID], [NAME], [JOBTITLE], [ML_LOC_CITY], [ML_DEPT_G_DESCR], [GRADE], [ML_REPORTS_TO_NAME], [ML_DEPT_E_DESCR], [ML_EMAILID], [EMPL_STATUS], [ML_DEPT_F_DESCR], [ML_DEPT_H_DESCR], [HIRE_DT]
FROM Roster
UNION SELECT [EMPLID], [NAME], [JOBTITLE], [ML_LOC_CITY], [ML_DEPT_G_DESCR], [GRADE], [ML_REPORTS_TO_NAME], [ML_DEPT_E_DESCR], [""], [EMPL_STATUS], [ML_DEPT_F_DESCR], [""], [HIRE_DT]
FROM Terms
WHERE [EMPL_STATUS] = 'T' or [EMPL_STATUS] ='D' or [EMPL_STATUS] = 'R';

 

[Yoyo]

No brackets win!!!! THANK YOU ALL!!!!!!!

SELECT [EMPLID], [NAME], [JOBTITLE], [ML_LOC_CITY], [ML_DEPT_G_DESCR], [GRADE], [ML_REPORTS_TO_NAME], [ML_DEPT_E_DESCR], [ML_EMAILID], [EMPL_STATUS], [ML_DEPT_F_DESCR], [ML_DEPT_H_DESCR], [HIRE_DT]
FROM Roster
UNION SELECT [EMPLID], [NAME], [JOBTITLE], [ML_LOC_CITY], [ML_DEPT_G_DESCR], [GRADE], [ML_REPORTS_TO_NAME], [ML_DEPT_E_DESCR], "", [EMPL_STATUS], [ML_DEPT_F_DESCR], "", [HIRE_DT]
FROM Terms
WHERE [EMPL_STATUS] = 'T' or [EMPL_STATUS] ='D' or [EMPL_STATUS] = 'R';

 

[Brianwarnock]

EDIT: I need to stop doing this.. LOL.. Opening windows and then drifted to something else.. Ha ha.. But I think I am in line with Brian's thinking..

No doubt dreaming of being on Bournmouth beach

Brian

 

[pr2-eugin]

Paul I notice that you use " " ie a blank, isn't it better to use a zero length field. I hope that it is not a date field by the way.

Brian

I think I used a ZLS there Brian..

No doubt dreaming of being on Bournmouth beach

Oh did I mention I live only 2 mins walk away from the amazing beach.. Summers here and the time is right..

 

[Brianwarnock]

Sorry Paul, thought that you had a space between the quotes.

To get my own back regarding the beach I thought that I would mention that I won't be on tomorrow as I am leading a group on a short(10 mile) stroll through the countryside, no windows to worry about.

Brian

PS. It's supposed to rain

Sorry Yoyo didn't intend to sidetrack your thread