Union query duplicates...

[Jesterhoz]

Hello All,

I have a union query that queries two tables. These two tables are similar
in a lot of ways, however, some fields in one of the tables have null values
where these same fields in the other table are populated. The following is
my SQL statement:

SELECT InvNo, CustCode, InvDate, InvAmt, Route, FileName FROM Invoices UNION
SELECT InvNo, CustCode, InvDate, InvAmt, NULL as Route, NULL as FileName FROM
SalesAll;

It returns something like the following:

InvNo CustCode InvDate InvAmt Route FileName
12345 56789 2/15/05 $12.34 100 c:\x115.txt
12345 56789 2/15/05 $12.34

This is because this record is in both tables. However, I only want one of
these lines to show on the form, the one with the Route and FileName. Any
ideas?

Any help is much appreciated.

Thanks,

Trevor

 

[Duane Hookom]

Create a totals query based on your union query that selects the Max from
some fields and groups by others.

 

[GB]

Or, if you know that you only want the data on the right that has a Route and
Filename, you can add to the query the
WHERE ROUTE is Not NULL and Filename is NOT NULL

That is if you know that you only have one record that has all the data, and
can not get multiple records with all of the data. Additionally in all of
the incomplete records, both Route and Filename have to be empty, otherwise
you can use an OR instead of an AND above.

 

[Jesterhoz]

Thank you for your replies. I like where you are going with this, however, I
should have added a third record. Take the following:

InvNo CustCode InvDate InvAmt Route FileName
12345 56789 2/15/05 $12.34 100 c:\x115.txt
12345 56789 2/15/05 $12.34
12345 129872 8/13/04 $1234.56

I also want this last record to show. It may not have data in the Route of
FileName field, but it is definietely a different record. I only want to
eliminate records that have the same data in multiple fields. For instance,
if CustCode and InvDate are the same, like in the first two lines above, give
me only the one with data in the Route and FileName fields.

Thanks for your help.

Trevor

 

[Duane Hookom]

My suggestion should still work.

 

[GB]

Don't know if you resolved your problem or not.

To resolve the situation you have, then you may need to do some additional
querying and "merge" the results of the queries to get your desired response.

Take a look at the Thread (How to retrieve values that match certain
criteria) in which I and Ngan were writing back and forth about meeting
certain logic requirements. More specifically look at my response 3/23/2005,
where I describe the logic I used to create the necessary query.

Essentially, what you may want to do, if you don't go with Duane Hookon's
response, would be to first, figure out which records with Route and Filename
you want to keep, then figure out which records of [InvNo], [CustCode],
[InvDate] (and [InvAmt????] are not already already in your list. Then use
the results from that and the first query as your final result. You would be
doing a series of nested queries (as it has been defined to me). Each nested
query can be named, and the results subsequently used within the single total
query. One query inner or outer joined to the other. and that inner or outer
joined, etc, etc, etc.

Dunno' if that helps.