Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-13-2018, 07:40 PM   #1
bsk13
Newly Registered User
 
Join Date: Sep 2018
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
bsk13 is on a distinguished road
How to get rid of double records (SQL) - HELP!

Hi,
I (really urgent) need to get rid of double records in the result of a query.
Any help would be highly appreciated!

My database looks like this:

orders.partno
orders.date
orders.data1
orders.data2
orders.data3

I need to write an SQL-query that gives me all the fields from the table above as result, but every partno should only exist ONCE (means "removing" any double partno records). Important is that when there are double partno records, it should keep the record with the most recent orders.date (if there are several records with the same orders.date for one orders.partno, it doesn't matter which of them is kept / removed).

I am unfortunately not experienced at all in SQL (I can read easy code but not write).

An employee working with SQL, but not for access, sent me this. He just hade time to sent me a general answer (below). I have not been able to transfer this proposal into a working SQL (where t would be my table orders above I assume):

His quick generic proposal:
select
distinct
t.a,
t.b,
(select MAX(c) from #test where a = t.a) as c
from #test t

I tried to write an SQL like this (with my table and fields):
select
distinct
orders.orderno,
orders.date,
orders.data1,
orders.data2,
orders.data3,
(select MAX(c) from #test where partno = orders.partno) as c
from #test orders

I get a syntax error on the select statement (I don't know if I correctly understood how the field c should be handled)

I hope you can help me out. Many thanks in advance!

bsk13 is offline   Reply With Quote
Old 09-13-2018, 07:47 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,850
Thanks: 9
Thanked 3,827 Times in 3,770 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: How to get rid of double records (SQL) - HELP!

Is this applicable?

http://www.baldyweb.com/LastValue.htm
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-13-2018, 07:56 PM   #3
bsk13
Newly Registered User
 
Join Date: Sep 2018
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
bsk13 is on a distinguished road
Re: How to get rid of double records (SQL) - HELP!

Many thanks for a quick reply!

Unfortunately I don't think this solution will work in the situation where I have two identical dates for a double record. Then I think I will get both of those records with the highest date (for that specific partno), not only one of them.

bsk13 is offline   Reply With Quote
Old 09-13-2018, 08:04 PM   #4
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,603
Thanks: 55
Thanked 2,098 Times in 2,009 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How to get rid of double records (SQL) - HELP!

do you want to delete the dup with early date or just ignore them from the result?
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 09-13-2018, 08:15 PM   #5
bsk13
Newly Registered User
 
Join Date: Sep 2018
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
bsk13 is on a distinguished road
Re: How to get rid of double records (SQL) - HELP!

Thanks for your interest in my problem!

I just want to ignore them in the result (the database itself should not be changed).

Example:

Partno...Date............Data1 (and Data2 etc.)
abc...24th of Dec...999....(keep this record OR...
abc...24th of Dec....888 ... keep this, but only one of them!)
abc....21st of Dec....777
def....19th of Dec....666...keep this
def....7th of Dec....555
ghi.....1st of January...444...Of course keep this

Last edited by bsk13; 09-13-2018 at 08:24 PM. Reason: adding "thanks..."
bsk13 is offline   Reply With Quote
Old 09-13-2018, 08:58 PM   #6
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,603
Thanks: 55
Thanked 2,098 Times in 2,009 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How to get rid of double records (SQL) - HELP!

SELECT Orders.PartNo, Orders.Date, Orders.Data1, Orders.Data2, Orders.Data3
FROM Orders
GROUP BY Orders.PartNo, Orders.Date, Orders.Data1, Orders.Data2, Orders.Data3
HAVING (((Orders.Date) In (select max(t1.[date]) from Orders As t1 Where t1.PartNo=Orders.[PartNo])));
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 09-13-2018, 09:48 PM   #7
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,603
Thanks: 55
Thanked 2,098 Times in 2,009 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How to get rid of double records (SQL) - HELP!

my previous post will not work.

i resort to using a user-define function.
copy and paste in a Standard Module.

note however that for this to work you
need to put a sorting order in the query
by:

Orders.PartNo ASC, Orders.Date DESC

your query:

SELECT Orders.PartNo, Orders.Date, Orders.Data1, Orders.Data2, Orders.Data3
FROM Orders WHERE fncInclude([PartNo])
ORDER BY Orders.PartNo, Orders.Date DESC;

Code:
Public Function fncInclude(pPart As Variant) As Boolean
    
    Static thisPart As String
    
    If thisPart = "" Or thisPart <> pPart & "" Then
        thisPart = pPart & ""
        fncInclude = True
        Exit Function
        
    End If
End Function

__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
bsk13 (09-14-2018)
Old 09-13-2018, 11:28 PM   #8
bsk13
Newly Registered User
 
Join Date: Sep 2018
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
bsk13 is on a distinguished road
Re: How to get rid of double records (SQL) - HELP!

Thanks again for your second reply!
Update: I realized that I probably have done a thinking error. Your solution might work. I will check again!

I tried this and I got an answer. It was however too few lines.

In totalt I have 22.712 records in the table "orders".
I know that 87 are double => 22.712 - 87 = 22.625 lines should be in the output.
Your query however only give 22.223 lines in the output (402 lines "too few").
Do you think you can find the problem?

Extra info:
This short query gives 22.625 lines as output:
SELECT DISTINCT
orders.partno, orders.date
FROM orders;

Last edited by bsk13; 09-13-2018 at 11:54 PM.
bsk13 is offline   Reply With Quote
Old 09-14-2018, 12:14 AM   #9
bsk13
Newly Registered User
 
Join Date: Sep 2018
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
bsk13 is on a distinguished road
Re: How to get rid of double records (SQL) - HELP!

Hi again,
close to the goal! I have found out that your query does the correct job. Many thanks!! But....

I however found out a problem with my data. Some of the fields orders.partno contains some trailing blanks. Blanks at the end are not actually valid. The field should always be 15 positions. Any positions after this are not valid.

Therefore, some records look different, but only differs because one "partno" is 15 positions with some blanks after (like 19 positions in total) and some are "just 15 positions" (without any blanks).

If this somehow could be implemented in your solution it would work: "adjusted PartNo" = LEFT (PartNo;15) (but I still would like to call the field partno if possible).

Or even better:
Get rid of these annoying blanks when creating the source "table" orders.

The "table" orders is "created" using this query:
SELECT Orders.PartNo, Orders.Date, Orders.Data1, Orders.Data2, Orders.Data3 FROM Excelfile1
UNION SELECT Orders.PartNo, Orders.Date, Orders.Data1, Orders.Data2, Orders.Data3 FROM Excelfile2
UNION SELECT Orders.PartNo, Orders.Date, Orders.Data1, Orders.Data2, Orders.Data3 FROM Excelfile3
(and another 4 similar Excelfiles in the same way)
If I could truncate the field PartNo in the Union-query above, only saving 15 leftmost positions of PartNo, the problem would be fully solved and I would even have a better “order table” than before!
Many thanks in advance!
bsk13 is offline   Reply With Quote
Old 09-14-2018, 12:37 AM   #10
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,603
Thanks: 55
Thanked 2,098 Times in 2,009 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How to get rid of double records (SQL) - HELP!

if you include the date field you will get more. you only need 1 record per partno, am i correct?

select distinct partno from orders

give me ur feedback.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 09-14-2018, 12:47 AM   #11
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,603
Thanks: 55
Thanked 2,098 Times in 2,009 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How to get rid of double records (SQL) - HELP!

ooops my mistake, you are right, 1 record per partno+date:

Code:
Public Function fncInclude(pPart As Variant, pDate as Date) As Boolean
    
    Static thisPart As String
    
    If thisPart = "" Or thisPart <> pPart & pDate & "" Then
        thisPart = pPart & pDate &  ""
        fncInclude = True
        Exit Function
        
    End If
End Function
change the criteria of the query and add the date field:



... WHERE fncInclude([PartNo], [Date])
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 09-14-2018, 01:11 AM   #12
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,212
Thanks: 83
Thanked 1,533 Times in 1,428 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: How to get rid of double records (SQL) - HELP!

Post 9 was moderated for some reason. Posting to trigger email notifications
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 09-14-2018, 02:20 AM   #13
bsk13
Newly Registered User
 
Join Date: Sep 2018
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
bsk13 is on a distinguished road
Re: How to get rid of double records (SQL) - HELP!

Hi, there were some confusions in the conversation above, because one of my answers wasn't published (somehow "blocked" / delayed by the system).

However, you are not wrong, you are right!
I only need ONE instance per PartNo (and the additional data is selected from the record with the highest date). This means your first query (post #7) does the job correctly and 22.223 lines is correct (i was wrong). Thank you!!!

I have one remaining problem, described in post #9 above (especially the part below "Or even better:"). Can you please help out with this last thing?
bsk13 is offline   Reply With Quote
Old 09-14-2018, 08:49 AM   #14
bsk13
Newly Registered User
 
Join Date: Sep 2018
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
bsk13 is on a distinguished road
Re: How to get rid of double records (SQL) - HELP!

Hi, I have bad news. Your query and macro runs perfect within MS Access. We are however calling an Access Query from within Excel, and there it crashes with an error message saying that the function doesn't exist. I guess macros can't be access this way.

Therefore I wonder if there might be a plain SQL-solution without a macro?
bsk13 is offline   Reply With Quote
Old 09-14-2018, 10:35 AM   #15
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,603
Thanks: 55
Thanked 2,098 Times in 2,009 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How to get rid of double records (SQL) - HELP!

here is a pure SQL without the function:

1.)
SELECT T1.PartNo, T1.tDate As [Date],
(SELECT TOP 1 [Data1] FROM Orders AS T2 WHERE T2.PartNo=T1.PartNo And T2.[Date]=T1.tDate) As [Data1],
(SELECT TOP 1 [Data2] FROM Orders AS T2 WHERE T2.PartNo=T1.PartNo And T2.[Date]=T1.tDate) As [Data2],
(SELECT TOP 1 [Data3] FROM Orders AS T2 WHERE T2.PartNo=T1.PartNo And T2.[Date]=T1.tDate) As [Data3]
FROM (SELECT PartNo, Max([Date]) As [TDate] FROM Orders GROUP BY PartNo) AS T1


regarding the union query you can use the Left function:

2.)
SELECT LEFT(PartNo, 15) As TPart, .... From excel1
Union
SELECT LEFT(PartNo, 15) As TPart, ... From excel2


now since PartNo has change to TPart in the Union, you also change it in query #1:

SELECT T1.TPart As PartNo, T1.tDate As [Date],
(SELECT TOP 1 [Data1] FROM Orders AS T2 WHERE T2.PartNo=T1.TPart And T2.[Date]=T1.tDate) As [Data1],
(SELECT TOP 1 [Data2] FROM Orders AS T2 WHERE T2.PartNo=T1.TPart And T2.[Date]=T1.tDate) As [Data2],
(SELECT TOP 1 [Data3] FROM Orders AS T2 WHERE T2.PartNo=T1.TPart And T2.[Date]=T1.tDate) As [Data3]
FROM (SELECT TPart, Max([Date]) As [TDate] FROM Orders GROUP BY PartNo) AS T1

__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
bsk13 (09-14-2018)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
help with preventing double records Rusty1969 General 1 12-02-2012 08:15 PM
queries are returning double records Jason1 Queries 4 05-29-2012 03:19 PM
Adding Records with double click honor401 Forms 2 09-10-2004 12:03 PM
Double Records jamesT General 1 04-08-2003 01:26 PM
combobox with no double records RobJ Forms 2 03-07-2003 06:52 AM




All times are GMT -8. The time now is 04:53 AM.


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

Sponsored Links

How to advertise

Media Kit


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