?
Solved

Access:  show last instance of similar/duplicate records

Posted on 2012-09-13
18
Medium Priority
?
476 Views
Last Modified: 2012-09-21
i have a table called tblTest
within this table i have customer names and data that has been appended with new information as time goes on resulting in duplicate names with not duplicate information.

example:
Name                        companyname       idnumber
smith, john                acme inc                123456
page, greg                 berings co               1230545
smith, john                acme co                 123457

i need all the records to show up except for the duplicate NAME records.  for the duplicates, i want the LAST instance to show up.

example query result:
Name                        companyname       idnumber
page, greg                 berings co               1230545
smith, john                acme co                 123457

there is no autonumber or date or primary key so i don't think a MAX of any of those would work.

your help is greatly appreciated..

thanks!
0
Comment
Question by:intsup
  • 10
  • 5
  • 3
18 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38397316
Try this:


SELECT t.[Name], t.[companyname], t.IDNumber
FROM tblTest t
INNER JOIN 
(SELECT  [Name], MAX(IDNumber) AS MaxID
FROM tblTest
GROUP BY [Name]) q
ON t.IDNumber = q.MaxID

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38397319
Alternatively, create a seperate query called qryMax:

SELECT  [Name], MAX(IDNumber) AS MaxID
FROM tblTest
GROUP BY [Name]

Open in new window



And a second query based on that:

SELECT * 
FROM tblTest INNER JOIN qryMax ON tblTest.IDNumber = qryMax.MaxID

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38397338
try this query


select  t.name, t.companyname, t.idnumber
from tblnames AS t inner join
(select max(t1.[idnumber]) as maxid, t1.[name] from tblnames as t1
 group by t1.[name]
) as t2
on t.idnumber=t2.maxid
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:intsup
ID: 38398665
i'm sorry for the confusion.  idnumber is just some data but not necessarily an actual number.  idnumber can't be used to get the max.  i need the last entry in the table for each NAME but not based on the idnumber or any other criteria.

sorry again for any confusion..
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38398683
Then in MS Access you can do this:

SELECT t.[Name], t.[companyname], t.IDNumber
FROM tblTest t
INNER JOIN 
(SELECT  [Name], LAST(IDNumber) AS LastEntry
FROM tblTest
GROUP BY [Name]) q
ON t.IDNumber = q.LastEntry

Open in new window


However, unless you can definitively identify how these records are entered in some way other than their order in the table, this is not as reliable.  Your results will change if someone manually goes into the table and changes the sort order.

You should have some field, whether it is an ID field or a "Date Entered" field that clearly defines the order in which these records are entered.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38398703
Does your table have an auto number ID?  Access practically begs you to add one when creating tables.  It is also easy to add one if you don't have one already.

If you have an Autonumber, this would be a reliable solution:

SELECT t.[Name], t.[companyname], t.IDNumber
FROM tblTest t
INNER JOIN 
(SELECT  [Name], MAX(YourAutoNumberField) AS MaxID
FROM tblTest
GROUP BY [Name]) q
ON t.YourAutoNumberField= q.MaxID

Open in new window



EDIT:

Re-read the question and saw this.

<< there is no autonumber or date or primary key so i don't think a MAX of any of those would work. >>


If you know that the records in the table are currently in the order they were entered, I would highly recommend adding an autonumber PK field now.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 38398739
based on the information you have given, there is no way you can determine which of the duplicate records is the last entry.
0
 

Author Comment

by:intsup
ID: 38400782
mbizup:  i tried your queries using another field, the ExpirationYear field but i get many many more results and not simply the Name showing one time.

capricorn, you appear to be correct.  i have a field called ExpirationYear that will hopefully serve as a max field.

unfortunately, using mbizup's query didn't work for me.

how can i group the Name field with the Max ExpirationYear field used?

thanks for both of your help so far!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38400803
try this query


select  t.name, t.companyname, t.ExpirationYear
from tblnames AS t inner join
(select max(t1.[ExpirationYear]) as maxExpirationYear, t1.[name] from tblnames as t1
 group by t1.[name]
) as t2
on t.ExpirationYear=t2.maxExpirationYear

just change tblnames and other field names appropriately
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38400814
Can you post how you implemented my post?  Capricorn1's solution was the same as my first post from what I can tell.

Also if you have duplicates of the same name with the same year you WILL see more records than you want.

If that is the case use LAST instead of MAX as in my second post.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38400822
However unless expiration year uniquely identifies the most recent ocurrence of a name you will still have the same problem in accurately identifying the last record entered.
0
 

Author Comment

by:intsup
ID: 38400835
thanks for the followups.

i tried the following queries and both gave me a result that repeated each and every name
the table itself has 4869 records but the results of the queries give 3051611

SELECT t.Name, t.ExpirationYear
FROM ltblAccessbooks_customer AS t INNER JOIN (SELECT  [Name], LAST(ExpirationYear) AS LastEntry
FROM ltblAccessbooks_customer
GROUP BY [Name])  AS q ON t.ExpirationYear = q.LastEntry;

select  t.name, t.companyname, t.ExpirationYear
from ltblAccessbooks_customer AS t inner join
(select max(t1.[ExpirationYear]) as maxExpirationYear, t1.[name] from ltblAccessbooks_customer as t1
 group by t1.[name]
) as t2
on t.ExpirationYear=t2.maxExpirationYear


i received a similar result when using the MAX
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38400840
The join needs to be on the ID field not the Year.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38400843
Did you try my second post using LAST idnumber?
0
 

Author Comment

by:intsup
ID: 38400856
unfortunately i was trying to be very simplistic in my original post since idnumber is actually the credit card number field for this database and therefore is not consecutive and wasn't meant to be used as a reference field for sorting.  so i used the Year since that is reasonably consecutive for using a MAX or LAST but i can't get it to work.

what's worse is this is a table that is part of another program so i can't modify it by adding an autonumber column or else it would break the program..
0
 

Author Comment

by:intsup
ID: 38400859
this query partially works.  it returns only one Name field.

SELECT [Name], MAX(ExpirationYear) AS MaxExpYr
FROM ltblAccessbooks_customer
GROUP BY [Name];


unfortunately, i can't link it to the main table without getting numerous duplicates..
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38400862
One more thought here ... if the records in your table are currently in a known order, why not add an autonumber PK?  That would make identifying the last record entered fairly straightforward.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 1600 total points
ID: 38400884
Missed your last remark about modifying the table.

Try including both the name and year as JOIN fields

Like this.....

SELECT t.Name, t.ExpirationYear, etcetera
FROM ltblAccessbooks_customer AS t INNER JOIN (SELECT  [Name], LAST(ExpirationYear) AS LastEntry
FROM ltblAccessbooks_customer
GROUP BY [Name])  AS q 
ON t.ExpirationYear = q.LastEntry  AND t.[Name] = q.[Name]

Open in new window

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

864 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