Recurring Names

I am an access novice running a database. Why am I getting recurring names
for the following sequence? They should only occur once each.

SELECT Demographics.LAST, Demographics.FIRST, Demographics.DOB,
StudyIndex.DATE, [ICD Implant].[ICD Type], *
FROM [ICD Implant], Demographics INNER JOIN StudyIndex ON Demographics.MR =
StudyIndex.MR
WHERE (((StudyIndex.DATE)#1/1/2003# And (StudyIndex.DATE)#12/31/2007#) AND
(([ICD Implant].[ICD Type]) Like "*Dual*"));

A join may duplicate rows from the other tables. As example, if you have a
table, dices, with one field, diceValue, with 6 records, one record for each
value from 1 to 6, then:


SELECT a.*, b.*
FROM dices AS a, dices AS b


return 36 rows, one row for each possible match (we asked for nothing
special).



SELECT a.*, b.*
FROM dices AS a INNER JOIN dices AS b
ON a.diceValue = b.diceValue


should return 21 rows, one row for each possible match ( we asked for the
first dice to be larger or equal, in value, to the second dice).


Using:

SELECT a.*, b.*
FROM dices AS a INNER JOIN dices AS b
ON a.diceValue = b.diceValue



will return only 6 rows, but if the table would have a duplicate value, say,
twice the value 6, we would have got 9 rows.

The logical result should be equivalent to the following procedu

Take all possible combination between records from the two tables, then,
eliminate those not satisfying the ON clause.

So, with

Dices
-------
diceValue
------
1
2
3
3



Evaluation of

SELECT a.*, b.*
FROM dices AS a INNER JOIN dices AS b
ON a.diceValue = b.diceValue


is logically equivalent to :


step 1:
a.diceValue b.diceValue
1 1 'first record and first record
1 2 ' first and second
1 3 ' first and third
1 3 ' first and fourth
2 1 ' ...
2 2
2 3
2 3
3 1
3 2
3 3
3 3
3 1
3 2
3 3
3 3



step 2: evaluate the ON clause and keep only those where it evaluates to
true. In this case, that keeps 6 rows:

1 1
2 2
3 3
3 3
3 3
3 3


NOTE: the result of an INNER JOIN must be LOGICALLY equivalent to that but
that does NOT MEAN the PROCEDURE to get it is exactly like the one I just
used.


So, you should see, now, that if you have a problem of "multiplication" of
records, that is probably because there is a DUP common in both involved
tables, (or because you want a GROUP BY query, and forgot to use one). If I
would have just said this simple sentence, the reason why whould have been
nebulous, so, excuse that lengthily comment.


Vanderghast, Access MVP



"armtwist" wrote in message
...
I am an access novice running a database. Why am I getting recurring names
for the following sequence? They should only occur once each.

SELECT Demographics.LAST, Demographics.FIRST, Demographics.DOB,
StudyIndex.DATE, [ICD Implant].[ICD Type], *
FROM [ICD Implant], Demographics INNER JOIN StudyIndex ON Demographics.MR
=
StudyIndex.MR
WHERE (((StudyIndex.DATE)#1/1/2003# And (StudyIndex.DATE)#12/31/2007#)
AND
(([ICD Implant].[ICD Type]) Like "*Dual*"));

You need a JOIN between [ICD Implant] and at least one of the other tables.
What is it related to?
--
KARL DEWEY
Build a little - Test a little


"armtwist" wrote:

I am an access novice running a database. Why am I getting recurring names
for the following sequence? They should only occur once each.

SELECT Demographics.LAST, Demographics.FIRST, Demographics.DOB,
StudyIndex.DATE, [ICD Implant].[ICD Type], *
FROM [ICD Implant], Demographics INNER JOIN StudyIndex ON Demographics.MR =
StudyIndex.MR
WHERE (((StudyIndex.DATE)#1/1/2003# And (StudyIndex.DATE)#12/31/2007#) AND
(([ICD Implant].[ICD Type]) Like "*Dual*"));

On Fri, 30 Jan 2009 08:36:09 -0800, armtwist
wrote:

I am an access novice running a database. Why am I getting recurring names
for the following sequence? They should only occur once each.

SELECT Demographics.LAST, Demographics.FIRST, Demographics.DOB,
StudyIndex.DATE, [ICD Implant].[ICD Type], *
FROM [ICD Implant], Demographics INNER JOIN StudyIndex ON Demographics.MR =
StudyIndex.MR
WHERE (((StudyIndex.DATE)#1/1/2003# And (StudyIndex.DATE)#12/31/2007#) AND
(([ICD Implant].[ICD Type]) Like "*Dual*"));

You have a "Cartesian Join" he for each record in [ICD Implant] you'll see
every record in the query joining Demographics to StudyIndex, and vice versa.
How are the tables related? Should there be a join from [ICD Implant] to one
of the other tables?
--

John W. Vinson [MVP]