Union Query

[kloot1rr]

Anyone good at union queries? I have a union query where I am trying to get the "Left in Estock" and the "Left in Estock Line Items" to show only once, not multiple times like they had before. How does this look? When I go to run the query it says syntax error in FROM clause.

 

[vbaInet]

Anyone good at union queries?

I'm a beginner but I can try

What are the data types for those two fields? I would imagine they are not Text.

But with that said, I don't see the benefit of creating a union query from the same source. Can you explain why?

 

[pbaldy]

The objects with inadvisable spaces need to be bracketed for starters.

 

[Ranman256]

is it because the 1st Q has 5 fields
and the 2nd has 6?

Id save each to its own query (not sql)
then union the queries

select * from qsQ1
union
select * from qsQ2

but it works both ways.

 

[vbaInet]

is it because the 1st Q has 5 fields
and the 2nd has 6?

I thought I counted that right, it's actually 6 fields on both sides

 

[kloot1rr]

The fields are both non-text. I'm pretty new to access - a colleague said a union query would work best to prevent the left in estock and line items data from showing up multiple times on my reports. Thanks for the replies, so making two seperate queries and then connecting those in a union query would be my best bet?

 

[MSAccessRookie]

As pbaldy said, because you have used spaces in the name. the reference to your master table needs brackets ( [master estock report] ).

In addition, to prevent any issues regarding Field contents and intent, I have each Union subquery use the same Field Names (via Aliases when required) for each field in all of my Union Queries. This also prevents accidentally leaving a field out of a subquery.

 

[kloot1rr]

It now says syntax error in JOIN operation - what is wrong with this? pic34 shows my table and pic6 is the union query.

 

[pbaldy]

Your brackets and parentheses aren't matched (don't need the parentheses anyway). You don't want an ORDER BY clause in any but the last clause, and you've got a stray semi-colon in the first clause.