[AccessD] Query => values not in...

Sander

If you create two queries: the first lists all the records that are 
in Staging (you will probably need a concatenated field for street 
and no - maybe called StreetAddress). The second uses the first query 
and the Customer table and includes in the criteria of a concatenated 
field for street and number the following code "Not In (select 
StreetAddress from qryStagingAddresses)

If I understand you correctly, this should work.

David

At 10:03 PM 20/12/2006, you wrote:
>Stuart,
>
>I believe this will return all records from staging and the records 
>from Customer where the fields match and Customer.Street = NULL.
>
>I need all records from staging that are not in Customer.
>Eg:
>records in Staging:
>street    no
>A           1
>B            2
>
>records in Customer:
>street    no
>A           1
>B           3
>
>I need the query to return:
>B,2
>Because this record isn't in Customer.
>Hope this clarifies things.
>
>Regards,
>
>Sander
>
>
>----- Original Message ----
>From: Stuart McLachlan <stuart at lexacorp.com.pg>
>To: Access Developers discussion and problem solving 
><accessd at databaseadvisors.com>
>Sent: Wednesday, December 20, 2006 11:37:07 AM
>Subject: Re: [AccessD] Query => values not in...
>
>
>Do a left join on the four fields and select  where the right field is
>null.
>
>Select *
>from staging s
>left join customers c on
>s.street = c.street,
>s.no = c.no,
>s.city = c.city,
>s.zipcode = c.zipcode
>where c.street is null
>
>On 20 Dec 2006 at 2:01, Sad Der wrote:
>
> > Hi group,
> >
> > i've got 2 tables
> > Customers
> > Staging
> >
> > Both have fields:
> > street
> > no
> > city
> > zipcode
> >
> > I need the following results:
> > Get all records from table Staging where (street, no, city, zipcode) not in
> > (select street, no, city, zipcode from Customers)
> >
> > So if the combination of "street, no, city, zipcode" in the table 
> staging is
> > not in the table Customers I want to see that record/ those 
> records How do I
> > do this in Access SQL???
> >
> > TIA
> >
> > Sander
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam?  Yahoo! Mail has the best spam protection around
> > http://mail.yahoo.com
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>
>--
>Stuart
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>__________________________________________________
>Do You Yahoo!?
>Tired of spam?  Yahoo! Mail has the be
>t spam protection around
>http://mail.yahoo.com
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com