Prev: asp : compare records in two access databases
Next: Erection pack! save 50%! All for Love! Nature product!
From: McKirahan on 10 Feb 2008 19:00
"Charlotte" <charlotte.deleeuw(a)SPAMtelenet.be> wrote in message
news:%PKrj.28196$9Z2.22687(a)newsfet14.ams...
>
> Hi,
>
> i've googeled to find a asp-script that can compare all the records in two
> different access databases
> the mdb's have exactly the same tables
> what i want is that (the output) all the differences comes in a html-table
> in a webpage
> can anybody help me, are give me a example ?

Is a primary key defined?
What do you want the output to look like?

Given this layout: "key,field1,field2"
where table 1 contains
1,a,b
2,c,d
and table 2 contains::
1,a,b
3,e,f
How would you want the differences reported?

Perhaps just identifying the "key" differences?
table1 : 1=,2+,3-
table2 : 1=,2-,3+


From: Evertjan. on 11 Feb 2008 03:10
McKirahan wrote on 11 feb 2008 in
> "Charlotte" <charlotte.deleeuw(a)SPAMtelenet.be> wrote in message
>> i've googeled to find a asp-script that can compare all the records
>> in two different access databases
>> the mdb's have exactly the same tables
>> what i want is that (the output) all the differences comes in a
>> html-table in a webpage
>> can anybody help me, are give me a example ?
>
> Is a primary key defined?
> What do you want the output to look like?
>
> Given this layout: "key,field1,field2"
> where table 1 contains
> 1,a,b
> 2,c,d
> and table 2 contains::
> 1,a,b
> 3,e,f
> How would you want the differences reported?
>
> Perhaps just identifying the "key" differences?
> table1 : 1=,2+,3-
> table2 : 1=,2-,3+

Interesting thought.

That however, wouldn't be really usefull for the OP, methinks,
as the goal would probably be to be able to correct inter table mistakes.

=========

Say you have two tables t1 and t2, each containing these fields:

id,lastname,firstname,street,postcode,haircolour

and each 100 records:

if there are no more than single equivalent records,
and even if you ignore the id-field differences
each record of t1 has 99 or 100 non-aequal records in t2 to report,
so there would be 9900 or more lines in the report plus 9900 seen from t2
to tt1, totalling 19800 to 20000 reportlines.

Not very useful imho.

==========

Better report a subset, [a view in relational database parlance]:
-- ignore the id field as above
-- only test the records with the same last names
-- order by last name, firstname, haircolour

Now you get a reasonable amount of report lines, perhaps 5 or 60,
but If you have a lot of records of one extrended family, the members
will report on eachothers differences and the report lines could swell to
2000 or more easily, if all lastnames are alike, perhaps to the 19800 of
above.

Also you will then loose writing mistakes of one of the corresponding
lastname fields.

This cannot be helped unless you develop an algorithm to define
'nearequal' or 'sounds-like' for the last name or define another field
that is assumed correct as key field, like a social security number in
this person-table example, or assume two fields to be correct, like
lastname plus postcode, but I would not think that is reasonable.

===========

So Charlotte, even before you touch the sql and the niceties of inner and
outerjoin, please reconsider what you really want.

Read about view in relational databases:
<http://en.wikipedia.org/wiki/View_(database)>

and oh yes, define what database engine you are using,
otherwize only a general sql advice can be given.

<http://en.wikipedia.org/wiki/SQL> [ook in Nederlands]

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
From: Bob Barrows [MVP] on 11 Feb 2008 05:46
Charlotte wrote:
> Hi,
>
> i've googeled to find a asp-script that can compare all the records
> in two different access databases
> the mdb's have exactly the same tables
> what i want is that (the output) all the differences comes in a
> html-table in a webpage
> can anybody help me, are give me a example ?
>
I submit that the reason you have not found a generic asp script to do this
is that this is not a task that is typically performed using asp. You have
not defined what constitutes a "difference" between two records (do all the
fields need to contain the same data, or just a subset of the fields), how
to identify the records that are supposed to be the same (is there a primary
key on the tables to identify the corresponding records?), or what the
likely differences may be (could there be records in one table that do not
exist in another?). And the biggest question of all: why are you duplicating
data in two databases? This sounds like a replication process is needed.
Access has the ability to perform replication, built into the program. You
really should investigate this.

Anyways, comparing the two tables is likely to involve looping through all
the records and comparing them field-by-field. This is likely to be a
time-consuming process that does not lend itself to the asp paradigm. You
should consider creating some sort of scheduled off-line process that
generates an exception table that can be easily queried and viewed by users
via asp.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


From: Evertjan. on 11 Feb 2008 06:20
Bob Barrows [MVP] wrote on 11 feb 2008 in
microsoft.public.inetserver.asp.general:

> Charlotte wrote:
>> Hi,
>>
>> i've googeled to find a asp-script that can compare all the records
>> in two different access databases
>> the mdb's have exactly the same tables
>> what i want is that (the output) all the differences comes in a
>> html-table in a webpage
>> can anybody help me, are give me a example ?
>>
> I submit that the reason you have not found a generic asp script to do
> this is that this is not a task that is typically performed using asp.
> You have not defined what constitutes a "difference" between two
> records (do all the fields need to contain the same data, or just a
> subset of the fields), how to identify the records that are supposed
> to be the same (is there a primary key on the tables to identify the
> corresponding records?), or what the likely differences may be (could
> there be records in one table that do not exist in another?). And the
> biggest question of all: why are you duplicating data in two
> databases? This sounds like a replication process is needed. Access
> has the ability to perform replication, built into the program. You
> really should investigate this.
>
> Anyways, comparing the two tables is likely to involve looping through
> all the records and comparing them field-by-field. This is likely to
> be a time-consuming process that does not lend itself to the asp
> paradigm. You should consider creating some sort of scheduled off-line
> process that generates an exception table that can be easily queried
> and viewed by users via asp.

I don't think, in the case of the OP, Bob, this is about users and about
large databases, but about the owner wanting to do some houeshold jobs on
her database on a semi regular basis, say once a year.

Setting a process in motion that takes 10 minutes or perhaps an hour in
ASP is not that bad. Being able to do that from different locations is a
nice plus of the ASP method.



--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
From: Bob Barrows [MVP] on 11 Feb 2008 06:42
Evertjan. wrote:
> Bob Barrows [MVP] wrote on 11 feb 2008 in
> microsoft.public.inetserver.asp.general:
>
> I don't think, in the case of the OP, Bob, this is about users and
> about large databases, but about the owner wanting to do some
> houeshold jobs on her database on a semi regular basis, say once a
> year.


I'm not sure how you came to that conclusion.

>
> Setting a process in motion that takes 10 minutes or perhaps an hour
> in ASP is not that bad.

It is if a busy web server is involved.

> Being able to do that from different
> locations is a nice plus of the ASP method.

Along with various other remote procedure techniques.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 |  Next  |  Last
Pages: 1 2 3 4 5
Prev: asp : compare records in two access databases
Next: Erection pack! save 50%! All for Love! Nature product!