numeric values

  • Thread starter Thread starter Gerry
  • Start date Start date
G

Gerry

Hello guys

I have 2 tables with exactly the same fields and the same number of
records.
Every day those 2 tables (updated) have to match. How can I proceed to
ensure myself that there is no difference?
When defining a numeric key, it appears that there are some
differences, even as both records appear to be equal.
Can someone tell how to proceed when I want to maintain this numeric
value as a valuable criteria to investigate if there are any
differences?

Kind regards?
 
There is seldom a good reason to have the same data in two different tables.
But assuming that you have one, here goes.

First create a query that has both tables in the grid. Make sure that each
field in one table is linked to its matching field in the other table. In the
first column in the grid put something like below:

CountOfMatches: Count([Asa].[AsaID])

Asa is the name of one of the tables and AsaID is the primary key field of
that table. It really doesn't matter which table you use. This will give you
the count of all the records that match exactly.

In the second column put something like below which will count all the
records in the Asa table:
CountInAsa: DCount("AsaID","Asa")

In the third column put something like below which will count all the
records in the Asa_dupe table:
CountInAsa: DCount("AsaID","Asa_dupe")

Now when you run the query, all three columns should have the same number
returned. If not, something is not the same between the tables such as a
different number of records in each table, or something doesn't match up if
the first column is less than either of the other two.

If there are a lot of records, this query could take a long time to run.
Also if there are memo fields, I'm not 100% sure that it will run correctly
or return the proper number of records.
 
Hello guys

I have 2 tables with exactly the same fields and the same number of
records.
Every day those 2 tables (updated) have to match. How can I proceed to
ensure myself that there is no difference?
Click to expand...

WHY!!?

The whole POINT of a relational database is to remove the necessity to store
data redundantly. Here you're setting up two tables and requiring that they
ARE redundant. That makes little or no sense to me - what business need is
served by doing this?

WHY!!?

The whole POINT of a relational database is to remove the necessity to store
data redundantly. Here you're setting up two tables and requiring that they
ARE redundant. That makes little or no sense to me - what business need is
served by doing this?

(sorry for rubbing it in).
When defining a numeric key, it appears that there are some
differences, even as both records appear to be equal.
Click to expand...

What datatype of Number? This could happen if you're using a number with
decimals and there are differences beyond what's visible on the screen - e.g.
a Currency field has four decimals but you might have the format set to
display only two, or a Double field might have differences in the 14th decimal
place but you might only see one or two.
Can someone tell how to proceed when I want to maintain this numeric
value as a valuable criteria to investigate if there are any
differences?
Click to expand...

By storing each value only once and removing the need to do so, I'd suggest.


John W. Vinson [MVP]
 
Back
Top