Unique Values + UNION = ERROR!Q

[Jeff Boyce]

I've searched here and on-line and not found any mention of this.

I'm using Access 2002.

I create Query1 that selects DISTINCT (Unique Values = Yes), returns two
fields, uses date range values in controls on a form as selection
parameters, and uses three values ("Green" or "Red" or "Blue") as another
selection criterion.

I build a Query2 that does something similar.

I try (keyword) to create a UNION query that returns all the rows found in
Q1 and Q2 (e.g.,:
SELECT * FROM Q1
UNION
SELECT * FROM Q2;

but Access folds and I get to send an error report.

(and if I remove the DISTINCT setting, it runs without issue).

Any ideas why the Unique Values would be killing the UNION query?

Thanks

Jeff Boyce

 

[John Spencer]

Since UNION does a Distinct on the recordset I can see no reason for using
DISTINCT in Q1 or Q2. That does not explain what the problem is.

Care to post the SQL of queries 1 and 2?

Now if you wanted to get the distinct records in query one and combine that
with the distinct records in query two, then you could use UNION ALL instead
of UNION.



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jeff Boyce]

Thanks, John.

I'll try the UNION ALL variant.

(I had been using the DISTINCT/Unique Values=Yes setting in Q1 and Q2 to
ensure that these two separate selections only returned ... well ...
distinct values.)

Regards

Jeff Boyce

 

[Jeff Boyce]

John

If I use DISTINCT in the two underlying queries, UNION ALL fails the same
way ... no error message, just the dump to ?Send an error report? and Access
tries to re-launch the app.

Here's an example of one of the query's SQL statement (copied from the SQL
window in query design):

PARAMETERS [Forms]![zfrmTEST]![txtFromDate] DateTime,
[Forms]![zfrmTEST]![txtToDate] DateTime;
SELECT DISTINCT dbo_CON_tblContract.ContractID, 0 AS AmendmentNo
FROM tblContract
WHERE (((tblContract.ContractBeginDate) Between
[Forms]![zfrmTEST]![txtFromDate] And [Forms]![zfrmTEST]![txtToDate]) AND
((tblContract.ContractStatus)="Executed" Or
(tblContract.ContractStatus)="Active" Or
(tblContract.ContractStatus)="Closed"));

I've also tried variations using a single field (it didn't matter which
one), and using SELECT * (also didn't matter), but so far all variations
result in the dump-to-error-report when I attempt to run the UNION query.

Thanks for taking a look.

Jeff

 

[John W. Vinson]

(I had been using the DISTINCT/Unique Values=Yes setting in Q1 and Q2 to
ensure that these two separate selections only returned ... well ...
distinct values.)

Huh?

UNION will *itself* return only distinct values. Unless you say UNION ALL, it
strips out the duplicates all by itself! What's more, it will return only one
row even if there are duplicates in the two different SELECTS of the UNION,
whereas your individual DISTINCT queries will only remove those duplicates
which are within one of the individual SELECTS, not across the two of them.

Why it's crashing I don't know, but I do know that (for the problem as stated)
the DISTINCTs are unnecessary.

 

[Jeff Boyce]

Thanks, John.

I've got a suitable solution, but incessant curiosity <g>

Jeff