MS Access question!

[darrent]

Guys,

I have the exact same problem as Graham had in his post today;
www.pistonheads.com/gassing/topic.asp?t=51625&f=95&h=0

Basically have two lists and want to identify values that are the same in both lists.

Only difference is that because my 2 lists are over the maximum rows allowed in Excess I need to do it in Access - any ideas how to do it.

Cheers
D

[judas]

Can't you just link the two tables together using the ID fields as keys?

[pdV6]

select list1.[ID] as List1ID, list2.[ID] as List2ID from List1 inner join List2 on List1.[ID]=List2.[ID]

[ben789]

SELECT DISTINCT Table1.val
FROM Table1, Table2
WHERE Table1.val=Table2.val;




>> Edited by ben789 on Thursday 7th August 16:21

[darrent]

Guys,

Thank you all - one small point I forgot to mention was that I know sod all about access to your replies mean nothing to me!

Anyone able to talk English and explain how to do it??

[ben789]

Darren,

have you got your database setup with all the tables and stuff?

If so, click on "Queries" in access, then "Create query in design view", then Close (if it asks you which tables to add). Then use the View menu to change into SQL mode and stick in the code, and amend it so it correctly reflects your table names/fields.

Then you can goto the Query menu and click on run


>> Edited by ben789 on Thursday 7th August 16:43

[darrent]

Cheers Ben, think I can manage that!

[smeagol]

I wouldn't bither with the SQL stuff, do it the simple way:

Start access have the two seperate tables.
Go to queries
New, design view.
Add the two tables (by selecting and clicking add). Then click button close.
You'll see two lists (ie both tables)
Drag the field you want to be equal from one table to the next table. A line will appear between the two fields and they will be linked so that only fields in both tables wil be shown.
Finally drag the fields you want into the main part at the bottom. (Quick way of you want all the fields from a table is to double click on the title and they will all be highlighted and can be dragged down)
Run query (by clicking on the ! button) and there you go.

Hope that helps.