Union Query (1 Viewer)

kloot1rr · Jun 22, 2015

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 · Jun 22, 2015

kloot1rr said:
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 · Jun 22, 2015

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

Ranman256 · Jun 22, 2015

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 · Jun 22, 2015

Ranman256 said:
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 · Jun 22, 2015

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 · Jun 22, 2015

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 · Jun 22, 2015

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

pbaldy · Jun 22, 2015

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.

Union Query (1 Viewer)

kloot1rr

Registered User.

Attachments

vbaInet

AWF VIP

pbaldy

Wino Moderator

Ranman256

Well-known member

vbaInet

AWF VIP

kloot1rr

Registered User.

MSAccessRookie

AWF VIP

kloot1rr

Registered User.

Attachments

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread