JOIN on all fields with same name?

[shaferbus]

Seems like I saw this somewhere, but I've just spent an hour searching and can't find any clear answer...

I am writing a SELECT statement with a LEFT JOIN, and I want to JOIN on all fields which have the same field name in both tables. Naturally, I can specify each of the field names in the ON portion of the JOIN statement, but it seems to me there was a shortcut if the field names happened to be identical in both tables... am I nuts?

Here's an example of what I'm doing:

strSQL = "Select * FROM tblSchedule " & _
strSQL = strSQL & "LEFT JOIN tblTimeStamp " & _
strSQL = strSQL & "ON ([COLOR=red]here's where I'm stuck[/color]);"

What I'm trying to accomplish is to detect when there has been a change in any field in tblSchedule since the last time the user looked at it, so there are about 18 fields that are common to both tables. Is it possible without "ON (s.field1 = t.field1) AND (s.field2 = t.field2) AND .... over and over?

 

[foxbox]

http://www.w3schools.com/sql/sql_union.asp

?

hth,
Foxbox

 

[shaferbus]

Thanks as always foxbox, but I fear I didn't express clearly what I'm trying to accomplish...

The two tables are similar, but not structured exactly the same. Each has about a dozen common fields, but also each has some unique fields. What I want is ALL of the info from each tblSchedule record, PLUS the unique fields from any record in tblTimeStamp that matches ALL of the common fields.

Geez, that's clear as mud huh?

Let's say tblSchedule has fields
A,B,C,D,E,F,G,H,I,J
and tblTimeStamp has fields
D,E,F,G,H,I,J,K,L,M,N.

What I'm looking to do is return A-J from tblschedule PLUS K-N from tblTimeStamp where D-J in both tables match. I could swear I saw somewhere a method where the JOIN statement could be written to compare any like-named fields without listing them all explicitly...

If I understood UNION correctly, it will return only D-J where there is a match... right?

Maybe I should just quit looking for a shortcut and type in all of the common fields in my JOIN statement It's just a drag because different employees have different formats of schedules, so it's not as simple as a "copy and paste" of the SELECT statements (and with my typing skills, it will take three tries for each of them LOL)