trouble with Unmatched queries (Access 2000)

[ssampier]

I am comparing one table, tblUsernames, to a query, All Customers. The query combines information from two tables, our main customer table and the table storing their email address (tblCustomer and tblEmail, respectively).

When I use the unmatched query comparing the query All Customers to tblServer, it has forty entries. Fair enough. Now if I do the reverse compare tblUsernames to All Customers it has 2300 entries.

Here is my SQL for both queries:

All Customers to tblServer

SELECT [All Active Customers w/ Email Address].[Phone Number], [All Active Customers w/ Email Address].[Billing Number], [All Active Customers w/ Email Address].Status, [All Active Customers w/ Email Address].Service, [All Active Customers w/ Email Address].[Sign-up date], [All Active Customers w/ Email Address].Comment, [All Active Customers w/ Email Address].[First Name], [All Active Customers w/ Email Address].[Last Name], [All Active Customers w/ Email Address].EmailAddress
FROM [All Active Customers w/ Email Address] LEFT JOIN tblServer ON [All Active Customers w/ Email Address].EmailAddress = tblServer.EmailAddress
WHERE (((tblServer.EmailAddress) Is Null))
ORDER BY [All Active Customers w/ Email Address].[Phone Number];

tblServer to All Customers

SELECT tblServer.EmailAddress
FROM tblServer LEFT JOIN [All Active Customers w/ Email Address] ON tblServer.EmailAddress = [All Active Customers w/ Email Address].EmailAddress
WHERE ((([All Active Customers w/ Email Address].EmailAddress) Is Null));

Thank you for your help.

 

[PHV]

And the problem is ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ssampier]

The problem is the amount of entries comparing tblUsernames to All Customers.

Now if I do the reverse compare tblUsernames to All Customers it has 2300 entries.

I previously had the email information stored in the main customer table. I used a SQL Union query to combine these into one table. When I did this, the MOST results I have is around 300.

Now my tblEmail is a separate junction table containing these email addresses. Two thousand, three-hundred unmatched entries is MUCH too high.

Again, the query is working correctly way, but not the other. It doesn't make much sense to me.

 

[PHV]

And this ?
SELECT DISTINCT tblServer.EmailAddress
FROM tblServer LEFT JOIN [All Active Customers w/ Email Address] ON tblServer.EmailAddress = [All Active Customers w/ Email Address].EmailAddress
WHERE [All Active Customers w/ Email Address].EmailAddress Is Null;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ssampier]

We're getting close to the goal. When I run the query with your suggested changes it has 1101 entries.

I think there may be a problem with my query that combines the email and customer information (the All Customer's...).

Here is the SQL for the query All Customer's:

SELECT tblAll.[Phone Number], tblAll.[Billing Number], tblAll.Status, tblAll.Service, tblAll.[Sign-up date], tblAll.Comment, tblAll.[First Name], tblAll.[Last Name], tblEmail.EmailAddress
FROM tblAll INNER JOIN tblEmail ON tblAll.[Phone Number] = tblEmail.PhoneNumber
WHERE (((tblAll.Status)="Active") AND ((tblAll.[Sign-up date])<=#2/1/2005# Or (tblAll.[Sign-up date]) Is Null));

Let me know if you see problems. Thank you for your help.