How automatically setup joins for all fields?

S

Sam

I have 2 identical tables with LOTS of fields. Is there a way to setup joins
for each of the fields automatically without having to drag each field one at
a time or creating the relationship table 1 field at a time?

With Regards-
Sam
 
A

Allen Browne

Sam said:
I have 2 identical tables with LOTS of fields. Is there a way to setup
joins
for each of the fields automatically without having to drag each field one
at a time or creating the relationship table 1 field at a time?
Click to expand...

Do you have repeating fields, such as Week1, Week2, ... or Employee1,
Employee2, ... or where each field is the name of something (e.g. a sport)?
If so, the problem is with the table design. You need to normalize the
tables by creating a related table with many records instead of having many
repeating fields in one table.

If that is not the case, do these LOTS of fields together form the primary
key? If so, you may be able to solve the problem by introducing an
artificial key (AutoNumber) instead of the complex primary key.

If the JOINs do not represent links to the key fields, it probably does not
redesigning. (The issue is much more important than just the time it takes
to drag fields around in a query.)
 
S

Sam

Thanks for your post Allen. I am comparing 2 identical tables
(structure-wise). The 2 tables are "Before" and "After". I am trying to
identify records where fields have changed. I am doing this by joining all
the fields. Because of the number of fields, that would be a lot of dragging
each month (the layout can change from month to month)

SO, is there a way to get around the manual join process?

Regards-
 
A

Allen Browne

Hmm. Handling nulls gets quite messy here. You can't just use an inner join,
becuase that won't identify records where a field contents were deleted
(became null) or filled in (where it was previously null) compared to the
other table.

So you are writing expressions like this for each field:
((Table1.Field1 = Table1.Field2)
OR ((Table1.Field1 Is Null) AND (Table1.Field1 Is Null)))

You can write code to so this, but I'm not sure it would be productive. I
still don't understand
the layout can change from month to month
That still doesn't sound quite normalized (e.g. if the number of columns
varies depending on the number of days in the month.)

If this is important, there are commercial utilities you can buy to compare
tables. Might be worth it.
 
S

Sam

Hi Allen-

Thanks - I appreciate your contribution to this forum. I found 2 responses
of yours to another questions posted on 8/22/08 ("Comparing two tables")
that taught me the inner join method on all fields with the nz() function to
do what I want.

NOW, I am simply looking for a method to automatically compare 2 tables in
the future without having to do the same grunt work, ESPECIALLY for those
tables that have gazzillions of fields: i.e. do an inner join on ALL fields
between 2 identical tables (i.e. the structure/field names are identical),
AND do an NZ() for all fields except for the key.

I am simply trying to automate the process of creating the query manually.
In my 38 years of tech experience, I find such a compare to be common. I am
hoping there is a simple automated way to say "see these 2 tables - create a
query to do a compare on all fields and replace any nulls with zero-length
during the compare... I am really hoping that the only responses are NOT
"create it manually each time" and "buy a commercial product" ; )

With Regards-
Sam
 
P

Phil Smith

I think I have an idea, if I understand your problem.
First of all, I have two tables. Say they are customer tables, with name
address, city state zip and phone#.

If I understand your goal, it is to find when a record for John Doe has
a a different zipcode between the two tables, or a different City, or
any field which differes between the two databases. Correct? Or when
there is no John Doe record on the other table.

I am not sure why you need to make all of these joins. I would think
that this would be counterproductive. A join tends to drop records
which do not match.

I am also confused as to why the structure of this table would change
each month. Maybe it is just the data that might change each month?

If I were to be comparing two tables with the same records, in order to
make sure the data is complete, without a lot of manual stuff, I would
probably:

Create a Union query to add those two tables together.

Then, create a query to sort and sum the results, sorting and grouping
by every field and adding a CountOf field at the end.Doesn't matter what
ou are counting.

Results: Every set of identical (good) records will show once, with a
count of two.

Any record which does not have a match, (regardless of which table it is
in), or have a match but have differeing data, will have a count of
one. These are the exceptions.

Pull a third query based on the second, only giving you only those
records which have a count of one.

You now have a list of exceptions, which you can then do what you will with.


Phil
 
S

Sam

Phil and Allen-

Much thanks for your replies.

Allen - thanks for the code. Seems this is what I am looking for. I am
still surprised that a mature product like ACCESS doesn't have such a feature
out of the box...
 
T

Tom van Stiphout

On Sun, 1 Feb 2009 14:53:01 -0800, Sam <[email protected]>
wrote:

(you probably already suspected this:) No.

"2 identical tables" sounds like a possible database design problem.
"Lots of fields" does as well.
 
S

Sam

"2 identical tables" sounds like a possible database design problem.
Trying to compare BEFORE and AFTER (i.e. 2 versions of the same table)
 
Top