Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-30-2017, 02:30 PM   #1
stell
Newly Registered User
 
Join Date: Jun 2017
Posts: 15
Thanks: 3
Thanked 0 Times in 0 Posts
stell is on a distinguished road
Need a way to filter out records that are duplicate in all fields but one.

My apologies if the title isn't clear, i'm having a hard time articulating what my issue is.

Basically, I'm running a query that brings back a list of part numbers and info that corresponds to each part number. The issue is, there is one field, "Special Stock", that is causing duplicate entries. There are certain part numbers that can fall into more than one special stock category. So when I run a report, the sums and averages are all off because of the duplicate records (duplicate in every field except Special Stock). I need the special stock number to be included in the report, but I can only have each part number appear once. I need the part numbers with multiple special stock numbers to just pick one, and disregard the duplicate entries. This doesn't seem like a very logical thing to do in access, but I'm hoping someone can maybe point me in the right direction.

Thank yo

stell is offline   Reply With Quote
Old 07-30-2017, 02:35 PM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,333
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Need a way to filter out records that are duplicate in all fields but one.

Quote:
i'm having a hard time articulating what my issue is.
rather than articulating, suggest provide some example data and the outcome required from that sample data
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-30-2017, 07:05 PM   #3
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Need a way to filter out records that are duplicate in all fields but one.

we can do what you want by including a Function on your query that will only pick one special part number. but we need more info of your table.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-31-2017, 12:54 AM   #4
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,370
Thanks: 0
Thanked 742 Times in 727 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Need a way to filter out records that are duplicate in all fields but one.

if your table has keys,
make Q2, using the table , to get TOP 1 of : part#, key
make Q3 using Q2 and the table outer joined on the part, and ask for all items in Table NOT in Q2.

Ranman256 is online now   Reply With Quote
Reply

Tags
duplicate , field , query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to filter duplicate records Weebleman Queries 1 12-20-2011 09:10 AM
Filter out one instance of duplicate records hamrthroer Queries 2 12-04-2009 07:49 AM
[SOLVED] Filter/ Sort Query to eliminate Duplicate Fields accessgiz Queries 2 04-06-2006 03:54 AM
Filter out duplicate records using address in Query BukHix Queries 2 12-16-2004 07:21 AM
How to filter out duplicate records mr_metrics Queries 4 08-02-2001 07:08 AM




All times are GMT -8. The time now is 05:01 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