Can this be done? Query question . . .

[Guest]

I have three small tables that get refreshed occasionally from three
different sources. I need to verify that each is refreshed with the same data
so need to report on any missing records, i.e., a record a exists in one or
two of the tables but not in all three. It's easy enough to show records that
exist in all three, the reverse is a lot harder. I am still in the design
stage so have much leeway on a solution.

 

[josiah.mcdonald]

Can you give a little more detail in your description. The reason I'm
asking is because I'm not sure why you would need three tables with
the exact same information. This goes against DB normalization rules
where your goal is usually to only store information once and link it.
So are the tables exactly the same or do they just have certain fields
in common.

If they just have certain fields in common I think you should create
one seperate table of just the common fields. Using the primary key
(autonumber) just have a field in the other tables called
'CommonFieldsRef' or something. That way the common information is
only stored once.

If thats not the solution you were looking for you should probably
give some more detail.

 

[Guest]

You're right Josiah ... I actually am familiar with building relational
databases but got lost in a manual mindset with this issue. What I am trying
to do is make sure three different files on three different systems all have
the same data ... I don't control those systems, I'm just trying to provide a
"reconciliation" tool. But I see if I combine the data from all three files
into one file with a field that describes the system it came from, I can then
find the record that does not occur three times. Those are the ones I want to
report on.

Does this make sense? Any tips on building the query that will find those
records that do not exist three times?

 

[Guest]

SELECT Count(tbltable.x) AS CountOfx, tbltable.x
FROM tbltable
GROUP BY tbltable.x
HAVING (((Count(tbltable.x))<3));

 

[Guest]

Sweet. I'll give this a try. Thanks.

SELECT Count(tbltable.x) AS CountOfx, tbltable.x
FROM tbltable
GROUP BY tbltable.x
HAVING (((Count(tbltable.x))<3));