The image size limit has been raised to 1mb! Anything larger than that should be linked to. This is a HARD limit, please do not abuse it.
Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

MS Access Query help

CorvusCorvus .VancouverRegistered User regular
Hi, I need a little help with an access query I'm trying to get working. I'm not a frequent Access user by any means.

What I have is a db with two tables, one with a list of names and ID numbers, and other with another list of names. What I want to do is run a query that will find the duplicate names between each table, and output that list with the ID #s from the first table.

I've tried using the "find duplicates" pre-made query, but it seems to be outputting duplicates from within the first table, which is not what I want. I only want names that are duplicates between both tables.

Halp?

:so_raven:
Corvus on

Posts

  • DietarySupplementDietarySupplement Registered User regular
    edited August 2009
    Right idea, wrong query wizard. Use the "Find Unmatched Query Wizard." Make sure you select the table you want the results from in the first box, then your other table in the second. Find the field to match on, and viola.

    Now, if you have records in table A that are not in table B, do you have records in table B that are not in table A? In other words, to find the true "delta" you might want to do it both ways.

    DietarySupplement on
    Skull2185 wrote: »
    Basically, (PlayStation) Home is Second Life Ultra Light? Most of the cool stuff, none of the creepy blimp on blimp fucking.
  • ueanuean Registered User regular
    edited August 2009
    Corvus wrote: »
    I've tried using the "find duplicates" pre-made query, but it seems to be outputting duplicates from within the first table, which is not what I want. I only want names that are duplicates between both tables.

    Halp?

    Easiest way:

    Create new query.

    Add table1 and table2

    Join table1.name to table2.name

    Return table1.name in the results. These are the names that are identical in the name field of table1 and table2. (You can also return table2.name, it doesn't matter as you are only going to be retrieving records with identical names)

    .......... or, enter SQL view and just enter this (change to suit your table and field names obviously)

    SELECT table1.ID, table1.name
    FROM table1 INNER JOIN table2 ON table1.name = table2.name;

    Presto :) This is based on my understanding that you want to know which names reside in both tables, and not which names are duplicates within each table. If you want to know which names are duplicates within each table, create a new query, add a table (ie table1), group the results and do a count on name, then return the count for everything over 1.

    which would be

    SELECT table1.ID, table1.name, Count(table1.name) AS countofname
    FROM table1
    GROUP BY table1.ID, table1.name
    HAVING ((Count(Table1.name))>1);

    uean on
    Guys? Hay guys?
    PSN - sumowot
  • CorvusCorvus . VancouverRegistered User regular
    edited August 2009
    Thanks, I appreciate it, this was driving me nuts.

    Corvus on
    :so_raven:
  • DietarySupplementDietarySupplement Registered User regular
    edited August 2009
    Sorry, I misread your post... but my solution is for when you don't have duplicates, but data in tables that might not be in the other. Still, a handy thing to know.

    DietarySupplement on
    Skull2185 wrote: »
    Basically, (PlayStation) Home is Second Life Ultra Light? Most of the cool stuff, none of the creepy blimp on blimp fucking.
Sign In or Register to comment.