Using Access to compare and validate data

[Hector]

I have some inventory data (names, quantities etc) in an Access database and
also in another application. I need to compare the data in the other
application to make sure it is the same as what I have in Access (Yep, intend
using both sets).

Can I use Access as a tool to somehow compare the two sets of data (either
by importing the data or a new dbase) rather than manually sifting through it
all.

Just some outlines/ ideas at this stage should be enough to start me
thinking/ reading, at the moment I cant think how i can use it to do such a
task, so please put me on track.


Thanks
CM

 

[Tom van Stiphout]

On Sun, 18 Jan 2009 15:56:28 -0800, Hector

You could link the other table, and then write queries like this to
find missing data:
select * from table1
where ProductName not in (select ProductName from table2)

-Tom.
Microsoft Access MVP

 

[John W. Vinson]

I have some inventory data (names, quantities etc) in an Access database and
also in another application. I need to compare the data in the other
application to make sure it is the same as what I have in Access (Yep, intend
using both sets).

Can I use Access as a tool to somehow compare the two sets of data (either
by importing the data or a new dbase) rather than manually sifting through it
all.

Just some outlines/ ideas at this stage should be enough to start me
thinking/ reading, at the moment I cant think how i can use it to do such a
task, so please put me on track.

It depends on how the data is stored in the other application. If it can be
read by Access - or exported to a form which Access can read, such as a tab or
comma delimited of fixed-width text file, an Excel spreadsheet, a dBase file,
or the like - then you can use File... Get External Data... Link to link to it
and then run a query or queries to compare them.

If you intend to store the "same" data redundantly in two different
applications on a long term basis, you'll have a long term ongoing headache
keeping them in synch. Any chance that your application can read Access
databases, or that your Access database can link to whatever data store the
other app uses? I.e. is the other app storing its data in an ODBC compliant
database?

 

[Hector]

Yes, the 2nd application data can be read by access (i can export it to excel
etc), and yes I will be using the two applicaitons for different purposes
however. Initially i need to check that all data is correct (it is in my
access program I know).
You mentioned writing queries to compare- would you be able to expand on
this (ie what type of queries) or what is an outline of the query that I
should use.

Thanks for you information so far.

 

[John W. Vinson]

You mentioned writing queries to compare- would you be able to expand on
this (ie what type of queries) or what is an outline of the query that I
should use.

That will depend totally on the nature of the tables and of the data, and the
type of comparison that you want. More details please?

 

[Hector]

Ok, the data in access is correct. I wish to compare to data in the second
table (ie names and quantities) to make sure the data i.e. names and quant
are equal to those in the original access table.
I dont want missing names, or extra names in the second table.

Im thinking of creating a new database, copying the existing access table
into it and perhaps importing the external programs data into a second table.
The data would look like this:
Existing Access table:
Chemical 1 Quantity
Chemical 2 Quantity
Chemical 3 Quantity

External programs data:
Chemical 1 Quantity
Chemical 2?? Quantity
Chemical 3?? Quantity

ie. existing access data is correct, but want to make sure external data is
the same (no missing and no extra items). Its mainly the names im interested
in, if I can be sure they are ok i can sort quantities etc out later.
External data can be imported etc but im not too sure what sort of query I
need to actually compare the two sets of data and list whats missing / extra
in the second table.
Hope this gives you more background to go on.
Thanks

 

[John W. Vinson]

External data can be imported etc but im not too sure what sort of query I
need to actually compare the two sets of data and list whats missing / extra
in the second table.

The "Unmatched Query Wizard" will find records in one table which have no
match in the other. You may want to do it twice - once starting with the
Access table, once with the linked table.

You can easily check for quantities by creating a query joining the two tables
on [Chemical] and putting a criterion

<> [othertable].[quantity]

on the quantity field. Do include the brackets or it will try to compare to
the text string "quantity" and won't find ANY matches.

 

[Hector]

Great stuff, exactly what I was after- thanks for setting me on the correct
path.

External data can be imported etc but im not too sure what sort of query I
need to actually compare the two sets of data and list whats missing / extra
in the second table.

The "Unmatched Query Wizard" will find records in one table which have no
match in the other. You may want to do it twice - once starting with the
Access table, once with the linked table.

You can easily check for quantities by creating a query joining the two tables
on [Chemical] and putting a criterion

<> [othertable].[quantity]

on the quantity field. Do include the brackets or it will try to compare to
the text string "quantity" and won't find ANY matches.