Really basic MS Access question

September 9, 2007 5:50 PM

Forgive me for asking such an embarrassingly basic MS Access question. I have two voter lists. Each one has a list of voters, and each voter has a unique identifying number as well as some other fields. There are some voters who appear on both lists (having identical voter numbers on both lists) and some voters who appear on only one. I am only concerned with the voters who appear on both lists: how do I import some fields from one list to the other so that the unique identifying number for each voter is matched by that voter's data from the other list?
posted by Mr. Justice to computers & internet (7 comments total)

Not quite clear what your lists look like yet. Are you saying that for voter number N, both lists have data fields X, Y and Z, though these may be missing on one of the lists, and you want to fill in whatever you can by merging the two lists? Or is it that list A has data fields X, Y and Z concerning voter N, and list B has data fields P, Q and R concerning voter N, and you need a list that has N, P, Q, R, X, Y and Z in each row?
posted by flabdablet at 6:09 PM on September 9, 2007

Sorry: ... data fields X, Y and Z, though data for these may be missing on one of the lists ...
posted by flabdablet at 6:11 PM on September 9, 2007

Create a relationship between the two tables by linking the fields that are the same in each table. Then when you create a query you can include the fields you want from each table.
posted by who squared at 6:53 PM on September 9, 2007

flabdablet: it's more like "list A has data fields X, Y and Z concerning voter N, and list B has data fields P, Q and R concerning voter N, and you need a list that has N, P, Q, R, X, Y and Z in each row." The two lists of voters are not identical, but I only need the data for when the voters appear on both lists.
posted by Mr. Justice at 7:11 PM on September 9, 2007

Which party are you working for?

Ah, nevermind.

Mr. Justice posted "I am only concerned with the voters who appear on both lists:"

This means you want to do an inner join:

select a.*, b.* from tableA a join tableB b on (a.voter_number = b.voter_number);

To do this in Access, make a new um, whatever Access calls it, and select edit SQL; paste exactly what I typed, then replace the table names with the real table names, and voter_number with the real column name,.
posted by orthogonality at 7:21 PM on September 9, 2007

Access calls it a query.

You can also build this with the GUI; it's been long enough since I used Access that I've forgotten exactly how that's done, but if you search the inbuilt help for "inner join" it should get you there. But using the edit SQL method is probably less trouble.
posted by flabdablet at 4:09 AM on September 10, 2007

Sounds like you might not have the SQL skills (it's easy to screw up the syntax), so the steps to create a Query in MSAccess using the gui are basically:

- Go to View > Queries, click New
- choose Design View, OK
- choose the two tables in question from the table list (Crtl-Click to select the second one), click Add
- link the common data field by dragging field name from one to the other matching field
- then drag whichever fields you want to see from either/both tables down into the field list (bottom half of Design view) in whatever order you want them to appear in spreadsheet view
- go to Spreadsheet view to see if you got what you wanted

(For fun you can now look at it in SQL view to see syntax similar to what orthogonality suggested.)
posted by aught at 8:55 AM on September 10, 2007