+ (1 Viewer)

[Bobp3114]

I wish to find and track miss spelt products in the Products table. I guess ProductID, PartNumber, and Product (the description) are the items in discussion. I wish to find products that have been mis-entered EG spelling or spaces that should not be there. a sample would be 100B470JT500XT and 100B470JT 500XT or 100B470JT500ZT.
I would then seek to find records that use the misspelt product and correct the entry. I can't cascade delete as I still need the entry (say a sale using that product).

I have a query that finds duplicates
Bob

 

[The_Doc_Man]

The problem with automated error finding is that you need to approach it by exception. That is, you need to find all the things that are spelled correctly and ignore them, looking at everything else.

Part 1 might require two tables - Products and ValidProducts. The latter is a table that contains ONLY valid product ID values. You would then find "bad" records with a query SIMILAR TO

SELECT P.ProductID, <other related product info> FROM Products As P WHERE P.ProductID NOT (IN SELECT V.ProductID FROM ValidProducts As V) ;

That will give you the list of things that are wrong. From there, your method of correction will be more complex since you several ways that the bad data could be entered. You can try writing a query to identify records with incorrect spaces or invalid characters - but if someone just flat out entered the wrong number, how you resolve that will depend on a lot of factors.

The REAL question is how did the bad items get into the database anyway? The REAL solution is to have code during data entry that would validate the ProductID, OR if you have this ability, make the data entry form use a drop-down list of valid Product IDs rather than allowing someone to freely enter bad numbers, you would not have the problem in the first place. This is basic "Murphy's Law" stuff: If something CAN go wrong, it WILL go wrong... so make it impossible for it to go wrong.

 

[arnelgp]

if you already have a Master product table (with ProductID, PartNumber and Product (description), you don't need to
create these same fields on the Sales table (this is called Normalization of your table). what you only need to save is the ProductID. therefore, I strongly suggest you delete the PartNumber and Product on the Sales table and just create a Query that will join your Sales table to Product table (Using Left Join)

SELECT SalesTable.*, Product.PartNumber, Product.Product FROM SalesTable
LEFT JOIN Product ON SalesTable.ProductID = Product.ProductID;

then you use Form with the Recordsource from this query.

 

[MadPiet]

I wish to find products that have been mis-entered EG spelling or spaces that should not be there.

I could be wrong, but it sounds like you should be using a combobox with LimitToList set to True and getting the available values from a table. Your solution seems to be to correct the problem after it's happened instead of preventing it from happening in the first place.

"make the data entry form use a drop-down list of valid Product ID" ... LOL... yeah, "Reading is Fundamental!" Exactly.

I'm not Kent Brockman, but that's My Two Cents.

 

[GaP42]

As you indicate the issue is with data quality in the product table: names and part number and description.
I assume say sales data which uses the PartID and so you do not want these altered.
Issues:
How to prevent ongoing issues with users selecting invalid/"bad" PartIDs - those deemed to be mispellings (and needing to repeat a cleanup process)
How to clean up / or at least report the "correct" PART ID

1. You need to create or at least be able to filter the Parts table to show only those Parts that are "Master" Parts. Add a flag to the Parts table for this purpose. Mark those Parts records that you determine as being the Master. (This may be a process that you need to go through progressively as you identify those Parts where the data is just a mis-spellign and you determine which one stands as the Master
2. You also need a mapping - which says for this Part record look at this Master Part record: ie the PartID of the master it corresponds to, as column on the Part table
3. You might also want the date the mapping was made as another column on the Parts table, a reason for the mapping etc
4. You will need to fix up the data entry process for parts so that only the Master Parts can be used - going forward
5. Then you need to consider the changes needed for consolidated reporting of historical data.

The process of identifying the misspellings generally requires eyeballing the records and making the entries to your master copy of the parts table. (but then are you the only one who can change that list?)