Use of Compound or Multifield Keys

  • Thread starter Thread starter Ed Bitzer
  • Start date Start date
E

Ed Bitzer

A compound key provides uniqueness where a single field may not but
beyond that use can the name be used elsewhere. For example a
WHERE cause would be simplified by stating PrimaryKey = " & 'A' and 'B'
rather than [Field1 = " & 'A' & [Field2] = " & 'B'. However that use
would raise the error message that PrimaryKey was an unknown field even
though that name was assigned. So just where can it be used.

Appreciate,

Ed
 
Dear Ed:

In the WHERE clause you're restricted to referencing one column at a
time, as you seem to be explaining. The same thing happens when
describing JOINs. There is a simplified syntax like:

WHERE (A, B, C) < (D, E, F)

which is defined in the standards, but has not been implemented in
Jet, in SQL Server, and in other engines with which I am familiar. I
often write this into my queries as a comment anyway, because I like
how it documents the intent of the much longer code:

WHERE A < D
OR (A = D AND B < E)
OR (A = D AND B = E AND C < F)

This gets especially messy when the set of columns is more than 3.

What you are suggesting sounds like another feature I have long
preferred: to be able to make the above kind of comparison using the
set of columns described in an index. Maybe they're working on the
standard for this, but I may not be operational in our lifetimes.

Dream on! I'm with you!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

Appreciate your detailed answer (and you did "read me" correctly)
although I feared your answer was the fact of life. I was not born with
the greatest memory for detail and find most difficult remembering
multiple field (column) names when coding an SQL statement or even a
WHERE cause with a compound key. It's bad enough placing all the single
and double quotes required by the combo of SQL and VBA syntax. Sure
would be easier to just remember MyCompoundKey name and use fewer key
strokes.

Now even though I use the Query design view typically to code my SQL, I
seem to use a lot of Executes or Action queries including INSERT and
usually leave the Access developed code as a comment. I will now adopt
your practice of simplifying the WHERE logic in that comment.

Lastly I am retired and the computer has become a great hobby and a
most useful for helping local schools, church, clubs and myself.
Therefore I have no "associates," so the Internet web sites and
usegroups, with guys like you, are a real help - my only help.

Ed
 
Dear Ed:

My suggestion to comment the queries does not apply to Jet queries, as
Jet has no syntax for commenting. I was thinking in "MSDE" at the
time I wrote that.

I enjoyed your response and hope you profit from your newsgroup
experience.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top