UPDATE/INSERT/MOVE 2

[Swi]

I have a table that has car dealer information. I am sent transactional files on a bi-weekly basis that are in a variety of Excel formats.

On import into the database table I would like to do the following:

1. If there is a match by VIN# and dealer code that record to another table and update the information in the current table with the data from the Excel file

2. If there is not a match by VIN# and dealer code then just add that record to the table from the Excel file

I am assuming that I should import the Excel file to a temp table and then do my updates/inserts from that temp table.

Any help and/or tips would be greatly appreciated. Thanks.

Swi

 

[PHV]

Instead of a temp table you may use the spreadsheet as a linked table.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Swi]

PHV,

Would this still be the way to go in your opinion if I would be getting 80+ Excel files, some of which are in different formats?

Swi

 

[Swi]

It may be better to lay out the entire purpose of this application. Right now it is set up in a proprietary software application that I have utilized.

1. Receive data (multiple Excel formats) and add to master database file (updating information when appropriate while keeping a history of what was sent originally and adding new records to the master table when a match is not found).
If a match is found by VIN# and dealer code and the name and address is different then flag the record as changing owner.

2. Scrubbing the data, removing company records, records without first names, invalid postal addresses, removing addresses/VIN#'s appearing more than 2X's, etc....

3. Run several postal hygiene methods on the master database to ensure postal integrity (CASS, NCOA)

4. Estimate when the record should mail by calculating when a record is mailable by mileage and date of vehicle. Also, determine if the record is over age, under warranty or miles, over miles for warranty, etc..... and flag as such but keep due to client request

5. Code mailable records with mailing information and flag for remail and then calculate how many times a record could be remailed.

6. When responses come back match response data back to file (High Interest, Low Interest, Irate Customer, Do Not Contact, etc...) and suppression any negative responder data

There are several other processes I have omitted but this may give you a better idea of what I want to accomphish.

Also, in your experience how long would you say an application like this may take to write for one mid-level programmer?

The client has also hinted around this being web enable at which point I said that they would have to look into an option such as SQL Server, MySQL or Oracle.

Swi

 

[Swi]

I should say that I do not have much experience with SQL Server, MySQL or Oracle just so you may be able to determine a timeframe better. Most of my experience is in VB6 and I am mid-level at best in Access.

Swi

 

[JoeAtWork]

The best solution of course would be if all files would be in the same format. Even better would be to define an XML format enforced by a schema (xsd) file.

But if you cannot force your suppliers to use a standard, the only possibility I see is setting up some sort of configuration table. For each possible format, the configuration table would hold data such as the column position for different fields.

Example:
[tt]
FileFormat CityColumn ZipColumn
A 8 9
B 12 15
C 89 90
[/tt]

You would need to figure out the common denominator for all the file formats. For the example above, all file formats would have to have their City and Zip Code in separate columns.

Besides column positions, there all sorts of other variances that may need to be put in the configuration table, such as on what line the data starts. The possibilities are endless, and will be determined by how much the different file formats vary.

There is no way to quote hours to do this without seeing the 80+ file formats, how many fields need to be imported, etc.

 

[Swi]

They want all data to be kept and all history of the data to be kept as well when updates are made. They also now say that there will only be two formats but from past experiences I have found that to be untrue.

Swi

 

[JoeAtWork]

They also now say that there will only be two formats but from past experiences I have found that to be untrue.

Include that in your Requirements document, and be explicit in your quote that the work is based on this document. That protects you if/when there is a change, so that you are not expected to do free work to keep the application up to date.

 

[Swi]

Thanks. I wish I could have a requirements document but this a project that my boss has me working on for one of his buddies. They have already stated that they do not want to be nailed down to a specific document as this application will evolve indefinitely. I am in a rather difficult position here.

Does anyone have an idea on how long a project of this type may take? As stated above I am mid-level at best in Access.

Swi

 

[JoeAtWork]

Since you are not allowed to define a goal (i.e. the requirements document), I can't see how any accurate estimate can be hoped for.

If it were me, I think I would design it starting with what the common data structure should be. Then for each file format, design the procedures and functions needed to convert the data to the common structure.

Once all the data can be collected in the common table, you can apply your scrubbing procedures.

Keep in mind that this will "evolve indefinitely", so right from the get-go, try to keep things as organized as possible, as it sounds like you will be working with this system for some time.

Without knowing the structure of the source data, and the desired results, it's impossible to say how long this should take. Could be a day or two, or weeks.

 

[Swi]

JoeAtWork,

That is what I have been telling them but it goes in one ear and out the other. Now I found out that we may be getting other software that places like Equifax and Experian use to house their data and I may need to convert the application to that canned software.

Swi