Irritating Simple Query

[Chris Gorham]

Hi,

simply can't get this to work...

I have a table (Table A) containing records with about 20 different fields.
One of the fields is going to be matched with an equivalent field in another
table (Table B) using the "join" facility - so far so good.

However this field in Table A is not unique since it contains both single
and multiple occurances.

What I want to do is to write a query that will output the matching records
from Table A based on this field, but will do so only once per record thus
ignoring the multiple occurances. For the case of mutiple occurances where
the field matches the equivalent field in Table B, the record from Table A
with the earliest data should be output (using FIRST..??)

Hope this makes sense...Rgds Chris Gorham

 

[Tom Ellison]

Dear Chris:

When you have a multiple occurance in Table A, are the rows potentially
identical, or only identical in certain columns? Is there any combination
of columns for Table A that is guaranteed to be unique? And, using that set
of unique columns (if there is such a set) do you have a rule that you want
to use to pick just one row, based on the values in that set of unique
columns, that is the one you want?

If you can answer all the above questions, then you have only to program
that rule into the query. With sufficient information about this, I think
we can help you here, but you must divulge all the information about your
intention as outlined above.

If you just want any old row out of the set being shown, then we can use
FIRST(). I'm not sure, but I believe FIRST(Column1), FIRST(Column2),
FIRST(Column3) will return values for those 3 columns that are from the same
row, but there is no control over which row it will be. It may even change
over time. And don't be fooled. FIRST() will not necessarily return the
oldest row, as though the system were keeping track of the date/time the
rows were entered. FIRST() is not meant in a chronological sense, but in a
rather confusing (and usually meaningless) storage sense, and is not stable.

Tom Ellison

 

[Chris Gorham]

Tom,

thanks for getting back to me...

To clarify:

the field in Table B is unique (i.e. there are no reoccurances). The
equivalent field to be "joined" in Table A (as I've described) is not unique.

For those records in Table A where there are going to be matches, due to the
joined field with Table B, and are subject to multiple reoccurances - the
other fields COULD contain data which maybe the same in some fields and
different in others, but may also be identical records in all fields.

I hear you on the "FIRST" issue. To be honest any ONE of the records out of
a multiple occurance that match Table B should be output. It's selection
isn't critical, although I thought about time of entry to database. The
example below simply takes the first record of a multiple occurance in the
Table.

Like this:

Table A

Field 1...Field 2...Field 3...Field 4

A..........1...........2...........3
B...........3..........2...........1
C..........4...........5...........6
C..........4...........6...........6
C..........3...........6...........6
B...........3..........2...........1
D...........7..........3...........2
E............1.........7...........8

Table B

Field 1

B
C
D

Desired Result of Query matching Field 1 Table A and Field 1 Table B

Field 1...Field 2...Field 3...Field 4

B...........3..........2...........1
C..........4...........5...........6
D...........7..........3...........2

Hope this helps...Chris

 

[Tom Ellison]

Dear Chris:

OK, then, if FIRST() or just any row will do, then what's your question?

Frankly, in terms of Information Management, picking some information at
random doesn't usually appeal to me. Is it just me? I say that because
this question seems to appear very frequently in the NGs, and I never seem
to get it. A database isn't, for me, a random information generator. It
is, I expect, a disciplined way of specifying exactly what you want, and
getting it. No ambiguity. Only one correct answer.

Please forgive my rant. I'm just extremely puzzled.

Tom Ellison

 

[JAA149]

Dear All,

Table1
Field1 Field2 Field3 Field4
A 1 2 3
B 3 2 1
B 3 2 1
C 4 5 6
C 4 6 6
C 3 6 6
D 7 3 2
E 1 7 8

Table2
Field1
B
C
D

1 - This query
SELECT Table2.Field1, Table1.Field2, Table1.Field3, Table1.Field4
FROM Table2 LEFT JOIN Table1 ON Table2.Field1=Table1.Field1;
Returns

Field1 Field2 Field3 Field4
B 3 2 1
B 3 2 1
C 4 5 6
C 4 6 6
C 3 6 6
D 7 3 2

2 - This query
SELECT DISTINCT Table2.Field1, Table1.Field2, Table1.Field3, Table1.Field4
FROM Table2 LEFT JOIN Table1 ON Table2.Field1=Table1.Field1;
returns
Field1 Field2 Field3 Field4
B 3 2 1
C 3 6 6
C 4 5 6
C 4 6 6
D 7 3 2

Now some where here we need to put
"HAVING Count(Field1) = 1" OR "HAVING Count(Table2.Field1) = 1"

I am not sure about the syntax and where to place it in the SQL expression.
I tried but MS Access gives an error that Field1 belongs to Table 1 as well
as Table 2. I think this may be the way to go but lack the exact narration.
Please seach for "Duplicate Records" in this discussion Group.

It would be interesting to know the answer to this one.

Regards

Jawad

 

[Chris Gorham]

Tom,

its to do with telecom circuits and identifying those that are in the
engineering database and those that are in the billing database - I have over
100,000 of them and I wanted to eliminate the duplicates. Sometimes the many
occurence of a record merely reflects a small change in the circuit status -
although there is common information in the other fields.

I'm still feeling my way on this study so the requirement might change.
Nonethless I figured this was a quick one line query - if the example I gave
could be solved easily then fine, if not then I tacke it from a different
angle.

Chris

 

[Tom Ellison]

Dear Chris:

Well, a "one line" query is, the way I choose to format them, shorter than
anything I've ever written. But I think I have a concept of what you mean.

Now, it would seem there might be a date/time column in the data which would
almost certainly be unique. Given some uniqueness, we can build a 5-6 line
query, which is still quite modest (by my standards anyway, as I consider
100 lines to be the break point between simple and moderately complex, but
I'm sure I'm an oddball).

Let me know if we have any basis to continue, or if the FIRST() approach has
it nailed for you.

Tom Ellison