How to write a Distinct query based on a cross tab? (1 Viewer)

[KitaYama]

I create a cross tab sql based on a lot of situations in vba.
Now before I open a report based on this sql, I need another sql statement that shows distinct values of one of the fields.

My union query:

UnionSql =
SELECT SupplierFK,QuotePK,
    ... other fields from first table .....
    FROM tblQuotes
    WHERE
    .... where clause here .....
Union ALL SELECT
    SupplierFK,
    ... other fields from Second table .....
    FROM tblRequests

How can I write a SELECT query that shows Distinct SupplierFK from above sql?

If I save the union query as a query def, then I can use :

SELECT DISTINCT qryUnion.SupplierFK
   FROM qryUnion;

My problem is that I don't want to save the union query. It should be done on the fly in vba.
something like :
sql = "SELECT DISTINCT SupplierFK FROM " & UnionSql

I hope it makes sense.
Any kind of advice is much appreciated.

 

[arnelgp]

you first create the union in design view.
then copy the SQL string.
then you can plug it to your sql variable:

sql = "select distinct supplierFK from (" &
"theUnionQuerySQL here" & ");"

 

[KitaYama]

In my tests I had used the union Sql in the Criteria of the design gird.
It seems that it should be used in front of table.

Million thanks.
You're a star. As always.
problem solved.