Do Loop -- Still Stuck on this one

[roxannep]

Okay, I've tried several things, but I'll list the steps this should go through:<br><br>I have a daily Excel Spreadsheet with 20 fields, varying rows. One of the fields is &quot;emailname.&quot; Steps I need to complete in Access:<br><br>Pull in this spreadsheet as its own table (Sheet1).<br>Have it compare row by row &quot;emailname&quot; from Sheet1 with &quot;emailname&quot; in the &quot;Contacts&quot; table.<br>&nbsp;&nbsp;&nbsp;&nbsp;If there is a match in tbContacts, do nothing and move on to the next row in tbSheet1 and compare next &quot;emailname&quot; from Sheet1 to tbContacts again.<br>&nbsp;&nbsp;&nbsp;&nbsp;If there is no match, copy the &quot;emailname&quot; from tblSheet1 and paste append to a new record in tblContacts. <br><br>I'm trying to avoid having &quot;emailname&quot; duplicates in the tbContacts. The Excel spreadsheet is a daily record of Ebay transactions, which do contain some repeating customers for which I already have a Contacts record. The balance of the information in the remaining 19 fields goes into a transaction table (all of this I have handled in a macro ... it's getting those emailnames into Contacts without duplicating them.<br>

 

[DougP]

Why is the E-bay info in Excel?<br>I would import the information into a new Table from Excel like you said Sheet1 and then create a query that is a &quot;Un-Matched&quot; to find the E-mails that are not in the other table.<br>Then change the Un-Matched query to an Append query to add them and the other information to the other 2 tables.<br>This has to be done one table at a time of course.<br>Also if the Un_matched field is an AutoNumber it won't work let you append it.<br><br>But if you want to learn VBA you can do the whole thing clicking just one button.<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[roxannep]

The Ebay info coming in indicates the high bid winner on products we have listed. Some high bid winners purchase numerous items, which means their email is returned numerous times with the items being the individual identifiers.<br><br>The reason I cannot simply do an un-matched query (which I have tried) is because while it returns the no-match on the contacts email, it also returns them several times on a new customer who has purchased three or four items. If the paste append is handled after that, the new record for that customer (whom we only know by email until payment is received) is duplicated for EVERY purchase (this is the table information in the Excel spreadsheet).<br><br>Therefore, I need to have it look at each row/field of Emailname to compare. Once it creates a new record, if the following row in the Excel sheet is the same customer purchasing an additional item, it will NOT duplicate the Contact information, but will skip it until it's gone through the table.<br><br>Once that happens, an action query adds the contactids to the Excel import table based on every record now having a match in the emailname field. This entire table is then paste appended to an Transaction table awaiting payment/balance of contact information which is data entered after receipt from customer.<br><br>The transaction table REQUIRES the emailname and ContactID. Both of those fields in the Transaction table can have duplicates (TransID is the primary key there). In the Contact table, we want only one record per emailname, generated from the daily Excel Import. <br><br>If there is an easier way to download the winners from Ebay directly into the Access tables and NOT have duplicate contact information for each transaction completed, I am all ears ... eyes .... whatever.<br><br>Again, thanks in advance for any further help anyone can provide on this. I thought a Do Until Loop on the Excel table would be the best method. Perhaps not.<br><br>

 

[nightrider]

why not just paste all the new records in to the contactid table then clean it up using a find duplicates query

 

[roxannep]

Because I'm not going to be the end user and the end users are ..... uhm ..... well, not able to grasp the basic concepts of whether they are looking at a record or a table let alone do any kind of query and clean-up. It's not just a matter of deleting any duplicated Contacts records. Those records are then tied to transactional records by the ContactID and Email. I then have to go in and switch the contactIDs in the transactional records on any and all duplicates with the matching e-mail names in the Contacts Records, then go back, find the duplicates in the Contacts Records, verify all Transactional Data is now tied to only one Contact record, then delete all the other contact records ...... way too many steps .....<br><br>We tried this, however, I ended up spending a great deal of time doing it this way (clean-up on 200 plus records daily), so I know there has to be a better way .... They want it so that they click one button called &quot;PULL IN THE EXCEL SPREADSHEET AND MAKE IT DO IT'S THING.&quot; Period.<br><br>Beyond that, they will be doing basic data entry. Getting the end users to understand a simple &quot;find&quot; has been quite the challenge, so I need to automate this other procedure as much as possible to ensure the data integrity and to avoid the clean-up nightmare I've handled doing it the query/duplicates and clean-up method. <br><br>Thanks for the suggestion. I'm keeping my fingers crossed for a better solution.

 

[Waynest]

This cant be that complicated.&nbsp;&nbsp;All you need to do is see if the contact already exists in the contacts table before you INSERT it.&nbsp;&nbsp;Like Doug says, scrap your macro and write some VB code.&nbsp;&nbsp;You might want to consider an approach where you validate then process the records one at a time.&nbsp;&nbsp;If you havent done this before you'll find it well worth your while in the long run.

 

[Chopper]

Have you considered doing an unmatched query on the two tables displaying all the e-mail addresses in the e-bay table that do not exist in the contacts table?&nbsp;&nbsp;Then, using this query as the source for your append query, you can append them to the contacts table.&nbsp;&nbsp; <p>-Chopper<br><a href=mailto: > </a><br><a href= > </a><br>

 

[elizabeth]

If I've followed you correctly, you can:<br><br>1. import the excel table to an Access table.<br>2. Run a 'unique' query on the appropriate fields.<br>3. Append the unique unmatcheds with your tblCustomers<br><br>Wrap them all up in a nice little module that you launch with a button and you will have happy users!

 

[roxannep]

Thanks ... I'll start with Elizabeth's suggestion and if that doesn't work, I'll move onto the more complicated suggestions ....<br><br>I know it shouldn't be this difficult ... isn't that always the way these things work. I'll post what I ended up doing to solve this with everyone's help!