DatabaseFilter: Linked tables

September 2, 2005 8:38 AM

Database 101... I have two tables, with one common field, an ID number. One table has many more records than the other. How can I tell which records in the 'big' table are also in the 'small' table?

I'm using Excel. I have Access, but not much experience there. I am making a mail merge with address info from the 'big' table for only the people listed in the 'small' table. I tried =VLOOKUP() but it seems unusually complex for what must be a common database query. Specific instructions would be best! I know how to do the mail merge once the data is sorted out.

posted by clgregor to technology (5 comments total)

i never use access or excel, but assuming access supports sql:
select * from table1
inner join table2 on table1.ID = table2.ID

this will tell you the ones in both.
posted by alkupe at 8:47 AM on September 2, 2005

If you pull the tables into Access, set up a new query.

Pull both tables in using the "Show Table" dialog box.

In the query design window, both tables will be represented as boxes. Click in the ID field of one of the table boxes, and drag over to the ID field on the other table box. This should give you a join that will only display records that appear on both tables (you want the line between them not to have an arrow on either end).

Pull the fields you want to display down into the bottom of the query design grid, and run/save the query. Voila!

(I'd save it as a select query-- which is the default query type-- that displays akll of your merge fields, and use that query as the source for your merge)
posted by COBRA! at 9:00 AM on September 2, 2005

COBRA!'s exactly right - this is a trivial operation in Access. You can then use the result of your select query as the data source in Word's mailmerge tool.

To get the excel tables into access, use File > Get External Data > Import. Then look for "files of type" .xls until you find your excel tables. Import each of them (the wizard will walk you through the steps), and follow COBRA!'s instructions.

Oh, by the way, but a row at the top of the excel tables for column labels (e.g., first name, last name, etc.). The access wizard will give you a check box option that allows you to indicate that excel column headings are to be used as access field names.
posted by jasper411 at 9:07 AM on September 2, 2005

That did it. I had tried making the query earlier, but didn't really know what i was doing and was stuck looking at the edit version. Thanks!
posted by clgregor at 9:21 AM on September 2, 2005

I know you've already solved this using Access, but for completeness:

If you wanted to do this in Excel, all you'd need to do is drop each table on a different worksheet, use "=vlookup([First ID from big table], [ID column for small table],1,false)" and then just drag-it/fill-it down for the the rest of the values on the big table, and sort by the vlookup column. If vlookup matches it will return the ID and float to the top after the sort, if it doesn't it will return "#N/A" and drop to the bottom.

Not really elegant, but probably easier then exporting it to Access.
posted by cosmonaught at 3:32 PM on September 2, 2005