Inventory database - auto importing a CSV file and then tracking changes in that file

[Horse Pop]

Good morning.



The situation basically is this:

A colleague got a .csv file that's coming over from a supplier with stock info in.

I want to set him up a tool for a) seeing what it says and b) having the changes highlighted.

This might be numbers or it might be a string or date that changes.

I know from a bit of basic playing with Access 2010 it's got macro functionality for importing a table and/or he could just stick it in a folder and have it linked.

Is there a relatively straight forward way of having it highlight the changes in figures though?

Possibly store some sort of log and generate a report?

I can probably do this in Excel but having it automated with one-click macros would make his life much easier.

Unsure what the keywords I should be searching for to find articles about this would be and you often get a better approach suggested by asking people in the know than just raw Googling so here I am

[June7]

There is no 'relatively straight forward' method to accomplish this.

Why is this necessary? Why does this new data have to be checked against existing records? Why is there a dependency?

With a relational database, ideally raw data is input and then manipulated with queries and calculations. New data shouldn't 'care' what's already input.

[Horse Pop]

The data is a stock feed which is coming over daily as a CSV file.

The need to compare is basically to see what they suddenly have in stock vs what they had in stock yesterday.

If an item is now available where it wasn't (or vica versa) there's actions that then need to happen.

It only needs comparing to yesterdays apparently, if that makes it easier.

If it's complicated to do, give me some pointers. I might do it as a learning project or might give up and shuffle back to Excel. Would prefer to use Access because I am sure it's model of reports is going to be easier to hand off to somebody as something to work with than Excel.

[June7]

A Find Unmatched can compare common fields in two tables. Such as: showing that an account number in one table is not found in other table. The query wizard can help you build Find Unmatched query. Do two queries to check both directions.

But if you want to see if yesterday's value for each account is different from today's - do a query that joins the two datasets on account number and apply filter criteria: table1.value <> table2.value. If you want to check for changes in several fields, apply the <> operator to each pair and use OR operator.

So far that is 3 queries to do the analysis you want.