Union of two tables' results, no dupes...

[Jesterhoz]

Hello All,

I have a table, "All", that gets updated with data from two sources, "Some"
and "Other". I am currently querying "All" to get invoice data for a form.
However, if "All" is not updated on a regular basis, then valid invoices are
not in "All" but are in "Some". I hope this makes sense. What I would like
to essentially do is query "All" as well as "Some" but only return unique
records. The problem is that most invoices are in both tables. Also, there
can be multiple valid occurrences of the same invoice number for different
customers or dates. I know this complicates things but it's out of my
control.

Here is an example of some valid info:

InvNo 123456
CustNo 456
InvDate 1/1/05
InvAmt $23.56

InvNo 123456
CustNo 893
InvDate 8/4/04
InvAmt $2,563.45

The first one, for instance, came in through "Some" and has not yet been
added to "All". I want to still have it come up as a result of the query.
However, if it had been added to "All", I don't want it to show up twice on
the form. Here is the SQL statement that I have so far. It does, however,
return data twice if it appears in both tables.

SELECT InvNo, InvDate, CustCode, InvAmt, Route FROM Invoice1 UNION SELECT
InvNo, InvDate, CustCode, InvAmt, Route
FROM GetAllData;

Any help would be much appreciated.

Thanks,

Trevor

 

[Jeff Boyce]

Trevor

It isn't clear to me how many tables you are using. Does everything end up
in a single table, with each record categorized as "All", "Some", or
"Other"? If not, then are you using more than one table with essentially
identical structures? If so, why?!

In your example, you had two identical InvNo's -- how do you tell if you
have "duplicate" records?

Good luck

Jeff Boyce
<Access MVP>

 

[Jesterhoz]

I appreciate you responding.

Here is the situation...We have invoice data coming from two sources, "Some"
and "Other". To get total sales data, these two tables are combined into one
table, "All". My problem would be solved if this update was done every day,
every hour, whatever. I would then only have to query one table. However,
this update is out of my control and seems to be done at random intervals.

So, I have a form that looks for a certain invoice number. It looks in the
"All" table and finds what it does. I then want it to look in the "Some"
table to see if there are any occurrences of the invoice there that have not
yet been added to "All". Theoretically, if invoice 123 is in "All" it will
be in "Some" as well because that is where it originated. But an invoice
from yesterday, let's say, will only be in "Some" because the update has not
yet been run.

I want the form to return only records with unique customer number or
invoice date because we have repeated invoice numbers from past dates.

Thanks for your help.

Trevor