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

KitaYama

Active member
Local time
Tomorrow, 05:45
Joined
Jan 6, 2022
Messages
573
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:

Code:
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 :
Code:
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.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:45
Joined
May 7, 2009
Messages
16,881
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

Active member
Local time
Tomorrow, 05:45
Joined
Jan 6, 2022
Messages
573
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.
 

Users who are viewing this thread

Top Bottom