Look UP Wizard and Union Query

  • Thread starter Thread starter Mercy
  • Start date Start date
M

Mercy

Hello,

I have a Union Query, "ALLVendors" that looks like this:

(SELECT ClientPO.Name , POColor.Vendor
From ClientPO, POColor)
UNION
(SELECT ClientPO.Name , POMaterial.Vendor
From ClientPO, POMaterial)
UNION (SELECT ClientPO.Name, Trim.Vendor
From ClientPO, Trim)
ORDER BY Client.Name;

The query works fine!

I want to be able to choose Vendors depending on which
Name is chosen. But when I put the Union Query as the
data source for the Vendor field... Look Up Wizard says
the syntax is wrong in the From field.

So then I tried to just write a seperate query that would
pick the Vendor based on the chosen Name.
This 2nd qry looks like this:

SELECT Vendor
FROM ALLVendors
Where Name = X;

This qry doesn't work. If I don't add in the "where
constraint" it returns all of the Vendors. If I try to
search according to Name, it returns nothing.

Maybe I don't know how unions work? Any ideas on why this
is happening? And how I can fix it?

Thanks!
Mercy
 
Hi,


Trim is a VBA function. Try using [Trim] , or simply change that table name
for something else.



Hoping it may help,
Vanderghast, Access MVP
 
I changed "Trim" to "TrimUsage" but it has not helped.

Thank you for replying,
Mercy
-----Original Message-----
Hi,


Trim is a VBA function. Try using [Trim] , or simply change that table name
for something else.



Hoping it may help,
Vanderghast, Access MVP



Mercy said:
Hello,

I have a Union Query, "ALLVendors" that looks like this:

(SELECT ClientPO.Name , POColor.Vendor
From ClientPO, POColor)
UNION
(SELECT ClientPO.Name , POMaterial.Vendor
From ClientPO, POMaterial)
UNION (SELECT ClientPO.Name, Trim.Vendor
From ClientPO, Trim)
ORDER BY Client.Name;

The query works fine!

I want to be able to choose Vendors depending on which
Name is chosen. But when I put the Union Query as the
data source for the Vendor field... Look Up Wizard says
the syntax is wrong in the From field.

So then I tried to just write a seperate query that would
pick the Vendor based on the chosen Name.
This 2nd qry looks like this:

SELECT Vendor
FROM ALLVendors
Where Name = X;

This qry doesn't work. If I don't add in the "where
constraint" it returns all of the Vendors. If I try to
search according to Name, it returns nothing.

Maybe I don't know how unions work? Any ideas on why this
is happening? And how I can fix it?

Thanks!
Mercy
Click to expand...


.
Click to expand...
 
Hi,



Have you added single quote around the name?

SELECT Vendor
FROM ALLVendors
Where [Name] = 'Mercy';




Vanderghast, Access MVP
 
Hi Michael,

Thanks for your help. :-)
Yes, I have tried adding a single quote around the name.
And no, it doesn't make a difference. I can successfully
qry the other way around
ie:
Select name
From ALLVendors
Where Vendor = 'Jen';

But that doesn't really help me ... :-/

Is it possible that the problem lies with the QRY
ALLVendors? I can't see a problem ... but then again I'm
still a newbie :-D

(SELECT ClientPO.ManufacturerName AS name , POColor.Vendor
From ClientPO, POColor)
UNION
(SELECT ClientPO.ManufacturerName AS name,
POMaterial.Vendor
From ClientPO, POMaterial)
UNION
(SELECT ClientPO.ManufacturerName AS name, TrimUsage.Vendor
From ClientPO, TrimUsage);

Thanks again for your help!
Mercy
 
Hi,


Your first post mentions an ORDER BY clause on Clients.Name. If you have
such a clause, since there is no Client table neither Client.Name; I would
remove the ORDER BY clause (or, if it is needed, use ORDER BY [name] ).

Also note that you produce a monster:

SELECT *
FROM ClientPO, POColor

may produce up to m times n records, if there are m records in
ClientPO and n records in POColor.



Otherwise, I don't see any grammatical problem with your statement...


Vanderghast, Access MVP
 
Mercy...
Just wondering if you found a solution - I am encountering
a similar problem...
Thanks!
 
Back
Top