If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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*")); |
#2
|
|||
|
|||
Recurring Names
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*")); |
#3
|
|||
|
|||
Recurring Names
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*")); |
#4
|
|||
|
|||
Recurring Names
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] |
Thread Tools | |
Display Modes | |
|
|