Query and Table Problem

[Bob]

Hi Everybody

I have a table called tblListingInbox which is connected to my MS
Outlook InboxFolder

This is the underlying table for a query called:
qryListingConfirmed

In qryListingConfirmed there is a field called
InvoiceNumber: ([Subject])
(which obviously does not appear in the table). This is an Invoice
Number and is composed from the first 7 figures from the subject field
and is a part of the subject field.

This works fine so far.

I have another table tblInvoice, that has existing 7 figure invoice
numbers in it, in a field called InvoiceNumber.

My question is;
Without making tblInvoice part of the query, how can I get
qryListingConfirmed to Lookup tblInvoice and:

1. Show InvoiceNumber's in qryListingConfirmed that are NOT ALREADY
in tblInvoice.

2. Not Show InvoiceNumber's in qryListingConfirmed if they are
ALREADY in tblInvoice.

Thanks

Smiley Bob

[Wayne Morgan]

> Without making tblInvoice part of the query, how can I get

qryListingConfirmed to Lookup tblInvoice and:
I don't know that you can. To do what you're trying to do, go to the query
tab in the main database window and click on the New Query button, not one
of the options in the window, and choose the Unmatched Query wizard. This
will walk you through creating a query that will show items in one table
that aren't in the other.

--
Wayne Morgan
MS Access MVP
"Bob" <sm*******@hotmail.com> wrote in message
news:je********************************@4ax.com... Hi Everybody

I have a table called tblListingInbox which is connected to my MS
Outlook InboxFolder

This is the underlying table for a query called:
qryListingConfirmed

In qryListingConfirmed there is a field called
InvoiceNumber: ([Subject])
(which obviously does not appear in the table). This is an Invoice
Number and is composed from the first 7 figures from the subject field
and is a part of the subject field.

This works fine so far.

I have another table tblInvoice, that has existing 7 figure invoice
numbers in it, in a field called InvoiceNumber.

My question is;
Without making tblInvoice part of the query, how can I get
qryListingConfirmed to Lookup tblInvoice and:

1. Show InvoiceNumber's in qryListingConfirmed that are NOT ALREADY
in tblInvoice.

2. Not Show InvoiceNumber's in qryListingConfirmed if they are
ALREADY in tblInvoice.

Thanks

Smiley Bob

[Bob]

On Tue, 01 Jun 2004 12:39:25 GMT, "Wayne Morgan"
<co***************************@hotmail.com> wrote:

Without making tblInvoice part of the query, how can I get
qryListingConfirmed to Lookup tblInvoice and:

I don't know that you can. To do what you're trying to do, go to the query
tab in the main database window and click on the New Query button, not one
of the options in the window, and choose the Unmatched Query wizard. This
will walk you through creating a query that will show items in one table
that aren't in the other.

Hi Wayne

You have started me on a completely new train of thought.

I made an unmatched records query and then copied it and altered the
"Is Not Null" to "Is Null" in One of them.

Between the 2 Queries, I think I will be able to juggle them into
doing what I want.

Basic stuff I know, but it's easy to forget.

Thanks for the pointer.

Regards Smiley Bob