Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 4 votes, 5.00 average. Display Modes
Old 06-05-2007, 12:39 PM   #1
jereece
Newly Registered User
 
Join Date: Dec 2001
Posts: 300
Thanks: 5
Thanked 0 Times in 0 Posts
jereece
Eliminate Duplicate Records in a Query

I know there a many posts about eliminating duplicate records because I did search, but I did not see anything that really answers my question.

I have a query in a local Access database that is using a Linked table from an SQL database managed by our corporate IT department. I have read access to only certain tables / fields in the SQL database. My query is based on 3 tables from the database and I have them linked together in my query.

When managers move from one site to another, they are given a different TeamID number. However, until they are replaced, the manager will have more than one TeamID assigned to him in the SQL database. Evidentally, the way our IT has the SQL database structured, when I query open items it duplicates the record for that manager because he has 2 TeamID numbers. So the only field that comes up differnt in the duplicate records is the manager's TeamID number.

I would like for my Select Query to ignore the duplicate record. I know I can use an Append Query to copy the data temporarily to a local table and set the proper fields as Primary Keys to do this. However, it would be nice if I did not have to go to all that trouble.

Is there a way to eliminate duplicate in my Select Query?

As always, I appreciate the help.

Jim

jereece is offline   Reply With Quote
Old 06-05-2007, 12:44 PM   #2
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,827 Times in 1,578 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
There are different ways to do this. One way that I've used in the past is to first create a query that pulls the manager with their current id for the team they currently are assigned to. Not sure how you would determine that without something like a date stamp of when they got assigned, but if you can...

Then I nest that query inside another that pulls the appropriate data.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 06-06-2007, 05:32 AM   #3
Len Boorman
Back in gainfull employme
 
Join Date: Mar 2000
Location: Coventry West Midlands UK
Posts: 1,930
Thanks: 1
Thanked 1 Time in 1 Post
Len Boorman will become famous soon enough
If you do not actually need the ID then leave it out of your query. Add the word distinct to the sql statement immediately after the word SELECT

SELECT distinct etc
L

Len Boorman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pulling records from a query in VBA WSC Modules & VBA 1 11-21-2006 11:31 AM
Union Query - create additional field / clear records tobypsl Queries 2 07-20-2006 11:52 PM
filtering duplicate records query problem LVN Queries 3 07-20-2006 04:50 AM
empty fields causing problems Christos Queries 2 04-27-2003 06:31 PM
Can this even be done? - Sorting Query Results a strange way Happen609 Queries 1 07-27-2001 08:35 AM




All times are GMT -8. The time now is 08:52 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World