Union query

[Damo10]

Hi,

I am trying to write a query that will look first in tblSealStripData to see if there is a date in the NextD field and if there is give the results from that record but if that field is empty then look in tblPEMAC in the DateStart field and give the results from that record in the table.

I have tried a union query and this is the first time I have tried to use one of these.

I have 2 queries that independently give the result for each table but would like to combine them into one so that I can use this to populate a subform.

 SELECT DISTINCTROW tblSealStripData.PEMAC, tblSealStripData.Description, tblSealStripData.Interval, Max(tblSealStripData.NextD) AS MaxOfNextD, tblSealStripData.Location
FROM tblSealStripData
GROUP BY tblSealStripData.PEMAC, tblSealStripData.Description, tblSealStripData.Interval, tblSealStripData.Location
HAVING (((Max(tblSealStripData.NextD)) Between Date() And Date()+20))
UNION
SELECT tblPEMAC.PEMAC, tblPEMAC.Description, tblPEMAC.Interval, tblPEMAC.DateStart, tblPEMAC.Location
FROM tblPEMAC

Any help would be great.

Regards,

 

[llkhoutx]

The columns of the 2 queries unioned are different; they must be the same number (of columns) and same data type.

 

[Damo10]

Hi,

They both have 5 columns and the data types are the same.

Regards,

 

[MarkK]

Help with what? You haven't described a problem.

How come PEMAC and SealStrip data are not in the same table? It seems like a design flaw that you have to UNION them together. If they were in the same table you could just use a WHERE clause, which would be simpler, faster, clearer, etc...

 

[llkhoutx]

Column names must be the same too.

 

[spikepl]

No they do not. Let's see what the actual issue is.

 

[llkhoutx]

That is the issue.

 

[pbaldy]

Column names must be the same too.

In my experience they don't need to be the same. Number and data type yes, names no.

 

[MarkK]

Doesn't it honor the field names in the first SELECT clause? After that if just scoops the data.

 

[pbaldy]

Yes, the result returned will use the field names or aliases from the first SELECT clause.

 

[sneuberg]

Hi,

I am trying to write a query that will look first in tblSealStripData to see if there is a date in the NextD field and if there is give the results from that record but if that field is empty then look in tblPEMAC in the DateStart field and give the results from that record in the table.

How is "that record" identified? What is in common between these tables?