Whats the best way to do this? (1 Viewer)

[Jakboi]

Hello,

I have a database which every week I have to import an excel spreadsheet. Now this spread sheet will always have rows that repeat and will have somewhere between 700-1500 rows.

What is my best option to delete the rows that repeat? Meaning if a rows 1-10 have the same data then delete 9 of them. It will vary but I only need the unique record and the others can be deleted.

Would this be best way using a query? VB or something else?

There are 6 columns that will always be in the spreadsheet with the same column names:

ACCT_NUMBER
SHORT_ACCOUNT_TITLE
CONTACT_COMMENTS
CONTACT_TYPE_TEXT
ENTERED_BY
INITIAL_CONTACT_DATE
DATE_ENTERED

There will be rows that will repeat on every spreadsheet everytime. The only column that will not repeat will be CONTACT_COMMENTS. This column is needs to be deleted everytime.

I guess I didnt know if when I import the spreadsheet if something was possible that would:

Delete the CONTACT_COMMENTS then delete the duplicate rows upon importing or even after since this will have to be done everytime.

Thanks.

 

[dcobau]

Link the spread sheet as a table, use a select query to group the data. Ensure that the data is 'grouped' and not summed.

However, linking spreadsheet can cause strange things happening to your data. For example, if there is no data in the first row Access will translate that cell as being alphabetical regardless of what that cell should contain.

Another example of problems is that somewhere in your spreadsheet data could have a couple of spaces inserted before values.

In other words, test carefully before using your data.

cheers

Dave

 

[dcobau]

I have just discovered this link:

http://office.microsoft.com/en-us/excel/HA102218401033.aspx

Dave

 

[gemma-the-husky]

i tend to import the excel sheet into a temporary access table rather than link it.

Then tidy the local table as necessary before finally appending it to the main table. (do you have a main table) If you know which columns you want you can use a unique values query as the append source.

 

[Jakboi]

Ok so if I have this right. I can import the spreadsheet say into table A. Then using an append query add the data I need to table B. Which would be everything but CONTACT_COMMENTS column.

Now where should I delete the duplicate rows? After or before the Append or does it not matter?

So its:

Import->Append-->delete query?-->using the data from table B for the rest of my items.