Looking for Mismatched Addresses

[DanCole42]

I've figured out a convoluted solution to this problem, but I'm hoping
for something a bit more elegant.

I have a table of orders as they're received from customers (the
BillCust and Orders tables, related on BillCustID), and a query of how
those orders ultimately appear in our accounting software
(qrySummit_SBCLIENT_Prep).

I want to set up a query that will let me see those records where, for
example, the Address1 fields from those two tables do not match (i.e.
because the accountant can't type properly).

The accounting query and the Orders table are related on the OrderID
field.

My thought was to set up an OUTER JOIN query that shows all records
from the accounting query but only those records from the BillCust
table where the Address1 fields are equal. Then in the resulting
query, any record where there's an Address1 from the query but the
Address1 from BillCust is blank, I know there's a mismatch.

However, I want to be able to see the two records side by side in a
single query so I can see exactly what the mismatch is:

OrderID BillCustAddress1 AccountingAddress1
12345 123 Main St 1223 Main St

What's the best way to go about this? Thanks!

[Rich P]

Greetings,

Your problem is that your system allows the same data to be entered from
more than one location - entering the same data more than once. The
whole concept of an RDBMS system is that you enter data only once. Then
that data gets propagated programmatically. If your system allows the
same data to be entered more than once - it is time to create a new
system which does not allow this (use referenctial integrity to enforce
this). That is the solution. This way, even if data gets entered
incorrectly - it will at least be the same throughout the system and
thus, you will be able to query against it.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[DanCole42]

On Jul 7, 2:03*pm, Rich P <[e-mail]:

Greetings,

Your problem is that your system allows the same data to be entered from
more than one location - entering the same data more than once. *The
whole concept of an RDBMS system is that you enter data only once. *Then
that data gets propagated programmatically. *If your system allows the
same data to be entered more than once - it is time to create a new
system which does not allow this (use referenctial integrity to enforce
this). *That is the solution. *This way, even if data gets entered
incorrectly - it will at least be the same throughout the system and
thus, you will be able to query against it.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Rich-
Unfortunately, I work in a company with over 500 employees and a
complete revamp of our accounting system is simply not viable, nor
anywhere near my decision-making powers. Trust me, I know the best
solution in a perfect world, but I'm trying to figure out the best
solution in my hellish, ummalleable corporate reality.

[lyle fairfield]

DanCole42 <da*******@gmail.comwrote in news:3f350630-86ff-4341-9f8d-
7d**********@k30g2000hse.googlegroups.com:

On Jul 7, 2:03*pm, Rich P <[e-mail]:

>Greetings,

Your problem is that your system allows the same data to be entered from
more than one location - entering the same data more than once. *The
whole concept of an RDBMS system is that you enter data only once. *The

n

>that data gets propagated programmatically. *If your system allows the
same data to be entered more than once - it is time to create a new
system which does not allow this (use referenctial integrity to enforce
this). *That is the solution. *This way, even if data gets entered
incorrectly - it will at least be the same throughout the system and
thus, you will be able to query against it.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Rich-
Unfortunately, I work in a company with over 500 employees and a
complete revamp of our accounting system is simply not viable, nor
anywhere near my decision-making powers. Trust me, I know the best
solution in a perfect world, but I'm trying to figure out the best
solution in my hellish, ummalleable corporate reality.

Wouldn’t the best solution in a world of such utter stupidity not be a best
solution but a worst solution? ... plenty of people here to provide that!

[Rich P]

With all due respect - in a situation like that - you may want to weigh
your options. Believe me, I have worked in such a situation -
basically, I was just a warm body filling a spot. Even though I had
mentioned about a revamp - it fell on deaf ears. Finally, someone
higher up noticed some inconsistencies in the data. The revamp
consisted of going back to mainframes, and I had to move on. From that
time forward, if I saw a similar situation and my suggestions for a fix
were not an option - then I would politely turn down the offer and move
on my way.

If fixing the problem is not an option - one other fix is to manually
fix the data - which puts you back on square one because if the system
has this design flaw then there are probably several design flaws, and I
will wager that there probably isn't even any referential integrity in
place and you will have a very difficult time matching up the unmatching
data. On that note - if wish you well.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[CDMAPoster]

On Jul 7, 2:26*pm, lyle fairfield <[e-mail]:

DanCole42 <[e-mail] in news:3f350630-86ff-4341-9f8d-
[e-mail]:

On Jul 7, 2:03*pm, Rich P <[e-mail]:

Greetings,

Your problem is that your system allows the same data to be entered from
more than one location - entering the same data more than once. *The
whole concept of an RDBMS system is that you enter data only once. *The

n

that data gets propagated programmatically. *If your system allows the
same data to be entered more than once - it is time to create a new
system which does not allow this (use referenctial integrity to enforce
this). *That is the solution. *This way, even if data gets entered
incorrectly - it will at least be the same throughout the system and
thus, you will be able to query against it.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Rich-
Unfortunately, I work in a company with over 500 employees and a
complete revamp of our accounting system is simply not viable, nor
anywhere near my decision-making powers. Trust me, I know the best
solution in a perfect world, but I'm trying to figure out the best
solution in my hellish, ummalleable corporate reality.

Wouldn’t the best solution in a world of such utter stupidity not be a best
solution but a worst solution? ... plenty of people here to provide that

Larry Linson, John Vinson and Lyle Fairfield somehow all showed up at
the same time at the pearly gates. All three were told by St. Peter
that they would have to spend one year in purgatory to cleanse their
conscience of some questionable database practices. First, they found
that Larry had a data collision early in his career. Zap! He was
handcuffed to an extremely ugly woman. Then they found that John had
messed up some Referential Integrity once. Zap! He was handcuffed to
an even more ugly woman. They started searching Lyle's records.
Zap! He was handcuffed to an extremely beautiful redhead. "They must
have liked my work," he said. "Don't flatter yourself," she said. "I
never split any of my databases!"

James A. Fortune
CD********@FortuneJames.com

[lyle fairfield]

CD********@fortunejames.com wrote in
news:60**********************************@2g2000hs n.googlegroups.com:

Larry Linson, John Vinson and Lyle Fairfield somehow all showed up at
the same time at the pearly gates. All three were told by St. Peter
that they would have to spend one year in purgatory to cleanse their
conscience of some questionable database practices. First, they found
that Larry had a data collision early in his career. Zap! He was
handcuffed to an extremely ugly woman. Then they found that John had
messed up some Referential Integrity once. Zap! He was handcuffed to
an even more ugly woman. They started searching Lyle's records.
Zap! He was handcuffed to an extremely beautiful redhead. "They must
have liked my work," he said. "Don't flatter yourself," she said. "I
never split any of my databases!"

Only a year? The time would pass so fast, lickety-split!

[Kelii]

Dan,

As others have stated, you have an "issue" with your systems not
talking; that said, here is a possible solution to your query
problems.

First, setup that query you layout where Address from BillCust is
blank, indicating a mismatch. In this query, setup a new field that
displays "Mismatch" if and when the BillCust field is blank. It sounds
like you probably know how to do something like this, in the event
that you don't it goes something like this in the Field "row" of a new
"column" in your Access query builder:
Mismatch: IIf([BillCust] = "", "Mismatch", "")
You may have to play around with the syntax, I can never remember
these things

Now build a second query which includes the first query with the
Mismatch field, as well as the tables that contain your OrderID,
BillCustAddress1 and AccountingAddress1. Populate the query as you
suggest above so that the record mismatches are shown side by side.
Now, set the Criteria for Mismatch equal to <>"" or some other variety
of manual filter; I've used a couple different varieties, and I always
have to screw around with them to get them to work.

At this point you should be all set.

Hope that helps.

Kelii