sierra7 446
Recognized Expert Contributor
I have found that I have duplicates in my Products table. I am using the DLookup() function to find a ProductID by a product 'criteria' and only create a new one if can not find a match e.g. - varX=dlookup("[ProductID]","ProductsTableName", strCriteria)
-
if isnull(varX) then
-
'Proceed to add new record and create new ProductID (autonumber)
-
else
-
'Use ProductID currently held in varX
-
endif
There are about 14,000 rows in the Products table, but I have found 40 are duplicates. The table started off with about half the number of records 7 years ago and there are 5 to 10 users accessing it.
The 'criteria' is a compound of 9 fields, seven of which are foreign keys populated by selecting from combo-boxes. The other two fields are text selected from combo boxes but not limited to the list. These don't seem to be the cause of the problem i.e. the duplicate records match each other perfectly on the 'criteria' fields. (They would not be duplicates if they were different !!)
With hindsight, I should perhaps have created a unique index on theses nine fields but that seemed rather cumbersome; the code should work; it's desireable but not critical to prevent duplication of products.
I've searched previous postings for a clue as to what is going wrong but most comment is about about finding duplicates and removing them. But why do they occur in the first place?
3 2188 nico5038 3,080
Recognized Expert Specialist
When you have no unique index defined on the unique combination of fields, then this can happen.
It's almost impossible to trace where this does occur without first defining the unique index and get an Access warning when trying to insert such a duplicate.
Best to start with a groupby query on the unique combination of fields and add a Count for another field. By testing for >1 the count will return the 40 dupes and will allow you to join with the original table to see all dupes. Remove the ones leaving the correct ones and add the unique index on the unique combination of fields!
Nic;o)
sierra7 446
Recognized Expert Contributor
Thanks for your reply Nico.
Yes, I found the dups much as you suggested but by using the Wizard! then I converted the query to 'make table' so I could favour one value over another, and replace the unwanted values in all the other tables that refered to ProductID.
It's years since I built the system, but I imaging that I didn't use a unique index because it must manage both Standard parts and Miscellaneous parts (or 'specials') on Purchase Orders and Sales Orders. The Standard parts should be unique and are easy to structure but the miscellaneous parts, no so.
No, I relied on the code and was now wondering if there are any issues with the dLookup() function being unreliable. The present system is in '97' and I am going through the code upgrading to '2003',(having to explicity state DAO etc). We had already hit the problem of charts not displaying correct data (See KB 317155) and I was partly wondering if this may have been another issue with running '97' under XL.
An alternative is that I may be seeing a difference between Me.Fieldname and Me![FieldName], which would have consequences in the 'criteria' of my dLookup(). Generally, the names of all my controls are the same as the bound data within them . . . but this is probably a topic for another thread.
nico5038 3,080
Recognized Expert Specialist
I haven't experienced DLOOKUP() errors yet, but perhaps using fields with leading or trailing spaces could have an undesired side effect like this...
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: ms |
last post by:
Access 2000:
I am trying to delete duplicate records imported to a staging table leaving one
of the duplicates to be imported into the live table. A unique record is based
on a composite key of 3 fields (vehicleID, BattID, and ChgHrs). VehicleID and
BattID are a TEXT datatype and ChrHrs are a number(long int.) datatype. Since
records to be...
|
by: KT |
last post by:
Is there any one click solution that would do the trick? I would like
to create a button, so the person who maintains the database can
perform clean up work to delete duplicate records which contain same
information in the ID field and the account number field once a week.
Thanks in advance!
KT
|
by: Carroll |
last post by:
I'm looking for a way in SQL to find duplicate records in a single
table, that are the same based on 3 columns, regardless of what is in
the other columns in the duplicate records. I would like to keep both
records (or it could be more than 2 as well) where duplicate records
are found. Also, I am interested in selecting all columns from the...
|
by: B.N.Prabhu |
last post by:
Hi,
I have a DataTable with several rows. Its having 20 Columns. when i click
the Insert button then i have to check the Database Rows. Whether these new
rows are already available in the Database. If its there, then i need to
seperate the Duplicate Records
Based upon 4 columns(EmployeeID, ProjectName, ProjectType, StartTime --
should be...
|
by: nethravathy |
last post by:
Hi,
The following table namely elcbtripselect contains 5147 records.I want to know wether this table contains duplicate records or not.
I tried with following query
1)SELECT elcbtripselect.ELCBTRIP_voltsMIN, elcbtripselect.ELCBTRIP_voltsMAX, elcbtripselect.ELCBTrip_is_partwinding, elcbtripselect.ELCBTrip_is_ydelta,...
| |
by: Thomas Arthur Seidel |
last post by:
Hello to all,
I have a small or big problem with a customer data base, where during a change of
system we might have created duplicate records. This should be easy to find, you
might think, but, we are talking about roughly 10000 records or less in a total
volume of 1 MIO records or more.
I have considered a strategy: The station ID and a...
|
by: nomvula |
last post by:
hi guys
i need some help to duplicate records on my form datasheet:
here's the example of my form results:
ClientLookup DateCaptured ForecastDate Description ForecastQuantity Forecast Actual
UJ 18-Apr-08 01-Mar-08 Fees: Asset 1 R 31,200.00 R 31,200.00
NMBM 22-Apr-08 23-Mar-08 P-MI (E) 07/2006 3 R 47,485 R 38,849
i have 200 records deplayed...
|
by: Dilip1983 |
last post by:
Hi All,
I want to delete duplicate records from a large table.
There is one index(INDEX_U1) on 4 columns(col1,col2,col3,col4) which is in unusable state.
First of all when i tried to rebuild index it showed error as unique key violation.
So i want to delete duplicate records for col1,col2,col3,col4 combination.
How can i delete the...
|
by: xraive |
last post by:
I have a problem with this. Currently I am trying Allen's code and i am not successful.
Current Design
Table1 (Main Form)
TravelID (PK)
ApprovedBY
EntreredBy
BudgetCode
ExpenseCode
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |