Union qry

  • Thread starter Thread starter Anna
  • Start date Start date
A

Anna

Hi,

I am trying to run an unmatched query to find records that
are on a union query that aren't on a "find duplicates"
query based on the union query (ie the unmatched records
on the union query). I can get that data, but when I try
and use the "count" function on the results, I get
the "illegal operation" message.
Is there any other way to achieve this?

Thanks,

Anna
 
Hi,

I am trying to run an unmatched query to find records that
are on a union query that aren't on a "find duplicates"
query based on the union query (ie the unmatched records
on the union query). I can get that data, but when I try
and use the "count" function on the results, I get
the "illegal operation" message.
Is there any other way to achieve this?
Click to expand...

A UNION query eliminates all duplicates all by itself; there is no
need, usually, to do a find dups query on a UNION query!

Could you post the SQL of your query that's giving the error message?
 
Thanks for your reply.

My union qry is an "all" qry - part of the purpose of the
databse is to look at both "matched" and "unmatched"
records. (For a bit of background, it compares data from
two sources which should be similar (although not
necessarily entirely identical).)

Here is the SQL that brings the error message:
SELECT DISTINCTROW qryUnionHESAndDisch.[Record ID],
qryUnionHESAndDisch.DOB, (etc...a few more columns here)
FROM qryUnionHESAndDisch LEFT JOIN qryDupFromUnion ON
qryUnionHESAndDisch.[Record ID] = qryDupFromUnion.[Record
ID]
WHERE (((qryDupFromUnion.[Record ID]) Is Null));

Do you know of an easier way to get the required records
to show? I used to make an appended table and that worked
but took a while longer to sort than I would like, and I
want to keep the number of tables to a minimum. However,
if that's what it takes... ;-)

Thanks for your help and time,
Anna
 
Here is the SQL that brings the error message:
SELECT DISTINCTROW qryUnionHESAndDisch.[Record ID],
qryUnionHESAndDisch.DOB, (etc...a few more columns here)
FROM qryUnionHESAndDisch LEFT JOIN qryDupFromUnion ON
qryUnionHESAndDisch.[Record ID] = qryDupFromUnion.[Record
ID]
WHERE (((qryDupFromUnion.[Record ID]) Is Null));
Click to expand...

This query (unless it's buried in the ...) doesn't have a Count -
could you post the SQL which is actually generating the error message?
There doesn't seem to be anything untoward in the query itself.
 
Hi, thanks for your reply.

The SQL I posted is what brings up the error message - I
do a 'find unmatched' qry and when I press "finish" it
shuts Access down. When I reopen it and look at the query
(without running it) the SQL is what I have already
posted. I don't get the opportunity to put a "count" in.
Is this just a bug?

Thanks,

Anna
-----Original Message-----
Here is the SQL that brings the error message:
SELECT DISTINCTROW qryUnionHESAndDisch.[Record ID],
qryUnionHESAndDisch.DOB, (etc...a few more columns here)
FROM qryUnionHESAndDisch LEFT JOIN qryDupFromUnion ON
qryUnionHESAndDisch.[Record ID] = qryDupFromUnion. [Record
ID]
WHERE (((qryDupFromUnion.[Record ID]) Is Null));
Click to expand...

This query (unless it's buried in the ...) doesn't have a Count -
could you post the SQL which is actually generating the error message?
There doesn't seem to be anything untoward in the query itself.


.
Click to expand...
 
Hi, thanks for your reply.

The SQL I posted is what brings up the error message - I
do a 'find unmatched' qry and when I press "finish" it
shuts Access down. When I reopen it and look at the query
(without running it) the SQL is what I have already
posted. I don't get the opportunity to put a "count" in.
Is this just a bug?
Click to expand...

It sounds like a problem with the Wizard. If you create the query
manually, do you get the same error?
 
Back
Top