How to Break up Multiple Entries - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

How to Break up Multiple Entries

Tom Juric

38 days ago

I have three tables: NewScanCode, tblProducts, tblProductsAlternateCodes

Each table has a NewItemCode and a ProductName and a UPC field
tblProducts can have only ONE UPC for each ProductName
tblProductsAlternateCodes can have multiple UPCs for the same ProductName for each item
NewScanCode-Has multiple ScanCodes for every ProductName (can have up to 4 ScanCodes each). Table is released once a month

I need to place the UPCs from NewScanCode into either of the tblProducts or tblProductsAlternateCodes by:
1. Take 1st  UPC from NewItemCode record for each Product name
     a. if UPC in tblProducts is empty, then add UPC from NewScanCode to tblProducts
     b. if UPC in tblProducts is not empty check to see if UPC exists in tblProductsAlternateCodes
         1.If UPC not exists in  tblProductsAlternateCodes, ADD to UPC to tblProductsAlternateCodes
2.If UPC  exists, disregard UPC and go to next record

2. Take REMAINING UPCs from NewItemCode record for each Product name from NewScanCode
     a. Check if UPC exists in tblProductsAlternateCodes
         1.If UPC does not exists in   tblProductsAlternateCodes, ADD to UPC to tblProductsAlternateCodes
2.If yes exists, disregard UPC and go to next record

3.  Do this for the remaining NewItemCodes

Example shows 6xDifferent NewItemCodes

	Tom Juric OP @Reply 38 days ago

	Juan Rivera @Reply 38 days ago
That a lot of code and no spae to put it all in this. is there another way to send you the code?

	Tom Juric OP @Reply 38 days ago
Not sure if we are allowed to send our email so I'll wait to see what the moderator says

Richard Rost @Reply

35 days ago

That's definitely doable, but you're overthinking it a bit trying to handle everything row-by-row. This is really a set-based problem, and you can handle most of it with a couple of queries instead of a ton of VBA.

First, conceptually:
You've got one "primary" UPC (tblProducts) and then a one-to-many list of alternates (tblProductsAlternateCodes). Your NewScanCode table is basically a staging table that you process once a month.

The general approach I'd take is:
1. Identify the FIRST UPC per NewItemCode (you'll need some way to define "first" - maybe lowest UPC, or an autonumber/import order field).
2. Use an UPDATE query to populate tblProducts.UPC where it's currently null.
3. Then use an APPEND query to push all remaining UPCs into tblProductsAlternateCodes, excluding anything that already exists (use a NOT EXISTS or LEFT JOIN IS NULL).
4. Also exclude anything that matches the primary UPC in tblProducts so you don't duplicate it there.

So instead of looping records, think:
Update for the primary UPC
Append for the alternates
Both filtered to avoid duplicates

Also, just a quick note: we try to keep all help here in the forums, so please don't post email addresses or personal contact info. If you're looking to hire someone for custom work, feel free to post in the Developer Network section.

If you get stuck on the "first UPC per group" part, that's usually the trickiest piece.

	Tom Juric OP @Reply 35 days ago
Thans to everyone. I'll work on this today

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View