How to do a lookup to show whether there is a match another table

[Mike C]

I need to create a table/query that shows whether there is a matching
record in another table in a new column).

So, if there is a match, the new cell will read "Client Y", whereas if
there is not a match, it will read "No" or be blank.

What is the best way to accomplish this in Access?

To illustrate what i am hoping to acheive in Table 2:

Table 1 - Client Y

NAME
Joe
Mike
Eric
Tim

Table 2 - Mailing List

Name Client
Doug (blank)
Joe Client Y
Tim Client Y
Seth (blank)

 

[Allen Browne]

All you need is an outer-join query:
1. Create a query using both tables.

2. In the upper pane of table design, drag the key field from Table1
(Name?), and drop it onto the matching field in Table2. Access draws a line
joining the 2 tables.

3. Double-click the join line.
Access pops up a dialog with 3 options.
Choose the one that says:
All records from Table1, and any matches from Table2.

Perhaps the structure you gave was just an example, but it might be a good
idea to set up the tables better first. Access has problems with a field
named Name, and you could have 2 clients with the same name so it cannot be
the key field. There might be a better way to set up the tables.

Over time, you might have several mailings, and you want to keep track of
who is on each one. You wil need 3 tables like this:
Client Table, with fields:
ClientID AutoNumber primary key
Surname Text
FirstName Text
...

MailType table, with fields:
MailTypeID Text (24 char) primary key
This table will contain records such as:
Newsletter
Christmas
Specials

Now to record which clients get which types of mailing, the 3rd table will
have these 2 fields:
ClientID Number who gets this
MailTypeID Text (24) what kind of mail they get.

 

[Mike C]

All you need is an outer-join query:
1. Create a query using both tables.

2. In the upper pane of table design, drag the key field from Table1
(Name?), and drop it onto the matching field in Table2. Access draws a line
joining the 2 tables.

3. Double-click the join line.
Access pops up a dialog with 3 options.
Choose the one that says:
    All records from Table1, and any matches from Table2.

Perhaps the structure you gave was just an example, but it might be a good
idea to set up the tables better first. Access has problems with a field
named Name, and you could have 2 clients with the same name so it cannot be
the key field. There might be a better way to set up the tables.

Over time, you might have several mailings, and you want to keep track of
who is on each one. You wil need 3 tables like this:
Client Table, with fields:
    ClientID        AutoNumber    primary key
    Surname      Text
    FirstName    Text
    ...

MailType table, with fields:
    MailTypeID    Text (24 char)  primary key
This table will contain records such as:
    Newsletter
    Christmas
    Specials

Now to record which clients get which types of mailing, the 3rd table will
have these 2 fields:
    ClientID        Number     who gets this
    MailTypeID   Text (24)    what kind of mail they get.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.











- Show quoted text -

thanks very much Allen