Linked table trying to eliminate multiple records

Posted on 2007-10-03
Last Modified: 2013-11-27
I was looking at the following article, and it looks as if it would solve my problem easily:

My scenario would be I have a table called Main, and each employee has a different User_ID where I simply want to show one of each User_ID in a query called Employee_Form.  The biggest difference between their problem and mine is that I don't have a date range that I care about.  I simply want one record shown for each User_ID.  The code I've used is:

FROM Main AS s INNER JOIN [select Emp_ID from Main group by Emp_ID]. AS q
ON s.Emp_ID = q.Emp_ID;

Please forgive my ignorance on SQL, and if there is a better way, I'm open to suggestions, but the reason i'm going through this is because I had to link tables together and by doing so have multiple entries for the same information.  Any thoughts would be greatly appreciated.  Thanks.
Question by:lstraw
    LVL 16

    Expert Comment

    SELECT s.*
    FROM Main AS s INNER JOIN [Main group] q ON s.Emp_ID = q.EMP_ID;
    LVL 16

    Accepted Solution

    SELECT distinct s.Emp_ID
    FROM Main AS s INNER JOIN [Main group] q ON s.Emp_ID = q.EMP_ID;

    Author Comment

    Neither of those worked, but I'm not sure if I'm doing it correctly.  Were there any variables that should be changed in either of those solutions?

    Author Comment

    I found the problem.  You put the group statement ([Main group]) into Main.  When I took out group, I got the following:

    SELECT distinct s.Emp_ID
    FROM Main AS s INNER JOIN [Main] q ON s.Emp_ID = q.EMP_ID;

    Which allows me to then add fields from design view. Thanks for all of your help!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now