SQL query similar to DISTINCT help please

Am working with MS Access 2003 & tables downloaded from Oracle. Am not
a GURU, more of a hacker, but have done a bit of everything in many
languages/programs/etc.

I have data fields in 2 tables that I want, and these are linked by 2
intermediary tables, say tblA, tblB, tblC, & tblD. The fields I want
are in A & D, B & C contain the linking fields.

I put this together in Access Design view, made the links, and it sort
of works. But, I get too many rows as output. I want exactly 1 row of
output for each row of tblA. I just want to pick up additional
attributes from tblD that should have been in tblA in the first place -
uggg.

Currently the linking is A->B->C->D. It turns out the A->B links are
one-to-many Any one of these many A->B links will get to the right
stuff in tablD.

I've tried DISTINCT, TOP 1, DISTINCTROW, but I guess I need this
distinct function to be on tablB and nowhere else.

Do I need a SELECT within a SELECT?

In English what I what is: take a row from tblA, take it pointer to
tblB, pick any one row from tblB that matches, follow its pointer to
tblC, again pick any one row that matches, follow its pointer to tblD
and return a specified filed to the final output.

Any help appreciated!!!

Dick Penny

Could you cut and paste your SQL so we don't have to either guess or be
psychic? I'm not very good at guessing, and my psychic ability is not a
strong point.

Larry Linson
Microsoft Access MVP

"dick" <d_penny@usa.net> wrote in message
news:1150428926.174573.159450@r2g2000cwb.googlegro ups.com...[color=blue]
> Am working with MS Access 2003 & tables downloaded from Oracle. Am not
> a GURU, more of a hacker, but have done a bit of everything in many
> languages/programs/etc.
>
> I have data fields in 2 tables that I want, and these are linked by 2
> intermediary tables, say tblA, tblB, tblC, & tblD. The fields I want
> are in A & D, B & C contain the linking fields.
>
> I put this together in Access Design view, made the links, and it sort
> of works. But, I get too many rows as output. I want exactly 1 row of
> output for each row of tblA. I just want to pick up additional
> attributes from tblD that should have been in tblA in the first place -
> uggg.
>
> Currently the linking is A->B->C->D. It turns out the A->B links are
> one-to-many Any one of these many A->B links will get to the right
> stuff in tablD.
>
> I've tried DISTINCT, TOP 1, DISTINCTROW, but I guess I need this
> distinct function to be on tablB and nowhere else.
>
> Do I need a SELECT within a SELECT?
>
> In English what I what is: take a row from tblA, take it pointer to
> tblB, pick any one row from tblB that matches, follow its pointer to
> tblC, again pick any one row that matches, follow its pointer to tblD
> and return a specified filed to the final output.
>
> Any help appreciated!!!
>
> Dick Penny
>[/color]

It would help if you posted some of the SQL strings you've tried.

"dick" <d_penny@usa.net> wrote in message
news:1150428926.174573.159450@r2g2000cwb.googlegro ups.com...[color=blue]
> Am working with MS Access 2003 & tables downloaded from Oracle. Am not
> a GURU, more of a hacker, but have done a bit of everything in many
> languages/programs/etc.
>
> I have data fields in 2 tables that I want, and these are linked by 2
> intermediary tables, say tblA, tblB, tblC, & tblD. The fields I want
> are in A & D, B & C contain the linking fields.
>
> I put this together in Access Design view, made the links, and it sort
> of works. But, I get too many rows as output. I want exactly 1 row of
> output for each row of tblA. I just want to pick up additional
> attributes from tblD that should have been in tblA in the first place -
> uggg.
>
> Currently the linking is A->B->C->D. It turns out the A->B links are
> one-to-many Any one of these many A->B links will get to the right
> stuff in tablD.
>
> I've tried DISTINCT, TOP 1, DISTINCTROW, but I guess I need this
> distinct function to be on tablB and nowhere else.
>
> Do I need a SELECT within a SELECT?
>
> In English what I what is: take a row from tblA, take it pointer to
> tblB, pick any one row from tblB that matches, follow its pointer to
> tblC, again pick any one row that matches, follow its pointer to tblD
> and return a specified filed to the final output.
>
> Any help appreciated!!!
>
> Dick Penny
>[/color]

* dick:[color=blue]
> Am working with MS Access 2003 & tables downloaded from Oracle. Am not
> a GURU, more of a hacker, but have done a bit of everything in many
> languages/programs/etc.
>
> I have data fields in 2 tables that I want, and these are linked by 2
> intermediary tables, say tblA, tblB, tblC, & tblD. The fields I want
> are in A & D, B & C contain the linking fields.
>
> I put this together in Access Design view, made the links, and it sort
> of works. But, I get too many rows as output. I want exactly 1 row of
> output for each row of tblA. I just want to pick up additional
> attributes from tblD that should have been in tblA in the first place -
> uggg.
>
> Currently the linking is A->B->C->D. It turns out the A->B links are
> one-to-many Any one of these many A->B links will get to the right
> stuff in tablD.
>
> I've tried DISTINCT, TOP 1, DISTINCTROW, but I guess I need this
> distinct function to be on tablB and nowhere else.
>
> Do I need a SELECT within a SELECT?
>
> In English what I what is: take a row from tblA, take it pointer to
> tblB, pick any one row from tblB that matches, follow its pointer to
> tblC, again pick any one row that matches, follow its pointer to tblD
> and return a specified filed to the final output.
>
> Any help appreciated!!!
>
> Dick Penny
>[/color]

Use an aggregate query. Use Group By for the field in TableA that you
want to appear only once. For the other fields, use First or Max.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Larry Linson wrote:[color=blue]
> Could you cut and paste your SQL so we don't have to either guess or be
> psychic? I'm not very good at guessing, and my psychic ability is not a
> strong point.
>
> Larry Linson
> Microsoft Access MVP
>[/color]

OK, here the whole mess, this gives 7 rows with 6 identical columns.
Two of the "wheres" are just to pair data quantity down, while the
third on "file" is because I want the linkage to only utilize those
rows (in the 1st link table).

SELECT DISTINCT OPENPAGES_SX_AUDIT3.RESOURCEID,
OPENPAGES_SX_AUDIT3.WHO, OPENPAGES_SX_AUDIT3.WHEN,
OPENPAGES_SX__ASSOCIATION.CHILDTYPE,
OPENPAGES_SX_PROCESS.PR_MEGA_PROCESS,
OPENPAGES_SX_PROCESS.PR_DESCRIPTION
FROM ((OPENPAGES_SX_AUDIT3 INNER JOIN OPENPAGES_SX__ASSOCIATION ON
OPENPAGES_SX_AUDIT3.RESOURCEID = OPENPAGES_SX__ASSOCIATION.PARENT)
INNER JOIN OPENPAGES_SX__PROCESS_FILE ON
OPENPAGES_SX__ASSOCIATION.CHILD = OPENPAGES_SX__PROCESS_FILE.FILE_ID)
INNER JOIN OPENPAGES_SX_PROCESS ON
OPENPAGES_SX__PROCESS_FILE.PROCESS_ID = OPENPAGES_SX_PROCESS.PROCESS_ID
WHERE (((OPENPAGES_SX_AUDIT3.RESOURCEID)="4798") AND
((OPENPAGES_SX_AUDIT3.WHEN)>#6/6/2006#) AND
((OPENPAGES_SX__ASSOCIATION.CHILDTYPE)="File"));

Realizing where I think things have gone wrong, I omitted the first
table, and began with link tbl1 into link tbl2 into "required data
column" table. Thinking that if I could control the one-to-manyness
problem in a smaller task, I could always expand it.

This SQL is:

SELECT DISTINCT OPENPAGES_SX__ASSOCIATION.PARENT,
OPENPAGES_SX__ASSOCIATION.CHILDTYPE,
OPENPAGES_SX_PROCESS.PR_MEGA_PROCESS,
OPENPAGES_SX_PROCESS.PR_DESCRIPTION
FROM (OPENPAGES_SX__ASSOCIATION INNER JOIN OPENPAGES_SX__PROCESS_FILE
ON OPENPAGES_SX__ASSOCIATION.CHILD =
OPENPAGES_SX__PROCESS_FILE.FILE_ID) INNER JOIN OPENPAGES_SX_PROCESS ON
OPENPAGES_SX__PROCESS_FILE.PROCESS_ID = OPENPAGES_SX_PROCESS.PROCESS_ID
WHERE (((OPENPAGES_SX__ASSOCIATION.PARENT)="4798") AND
((OPENPAGES_SX__ASSOCIATION.CHILDTYPE)="File"));

I get effectively the same results as previously.

Then I tried a query on ONLY the tbl that has the one-to-many links, it
too fails (for me). To simplify:

Link table1
----------------

A | a1 | dog
A | a2 | dog
A | a3 | dog
B | b1 | dog
B | b2 | dog
C | C1 | dog

I asked for a DISTINCT query showing columns 1 & 3, and I rec'd all 6
rows where I expected only
A | dog
B | dog
C | dog

Am I using DISTINCT wrong?
Is this problem because all these tables came from Oracle?
Where all the link fields are in fact "text" not integers?
There are NO relationships in my *.mbd, I just drag & drop linkages I
want in design view.

Please keep the comments coming.

Dick

Larry Linson wrote:[color=blue]
> Could you cut and paste your SQL so we don't have to either guess or be
> psychic? I'm not very good at guessing, and my psychic ability is not a
> strong point.
>
> Larry Linson
> Microsoft Access MVP
>[/color]

OK, here the whole mess, this gives 7 rows with 6 identical columns.
Two of the "wheres" are just to pair data quantity down, while the
third on "file" is because I want the linkage to only utilize those
rows (in the 1st link table).

SELECT DISTINCT OPENPAGES_SX_AUDIT3.RESOURCEID,
OPENPAGES_SX_AUDIT3.WHO, OPENPAGES_SX_AUDIT3.WHEN,
OPENPAGES_SX__ASSOCIATION.CHILDTYPE,
OPENPAGES_SX_PROCESS.PR_MEGA_PROCESS,
OPENPAGES_SX_PROCESS.PR_DESCRIPTION
FROM ((OPENPAGES_SX_AUDIT3 INNER JOIN OPENPAGES_SX__ASSOCIATION ON
OPENPAGES_SX_AUDIT3.RESOURCEID = OPENPAGES_SX__ASSOCIATION.PARENT)
INNER JOIN OPENPAGES_SX__PROCESS_FILE ON
OPENPAGES_SX__ASSOCIATION.CHILD = OPENPAGES_SX__PROCESS_FILE.FILE_ID)
INNER JOIN OPENPAGES_SX_PROCESS ON
OPENPAGES_SX__PROCESS_FILE.PROCESS_ID = OPENPAGES_SX_PROCESS.PROCESS_ID
WHERE (((OPENPAGES_SX_AUDIT3.RESOURCEID)="4798") AND
((OPENPAGES_SX_AUDIT3.WHEN)>#6/6/2006#) AND
((OPENPAGES_SX__ASSOCIATION.CHILDTYPE)="File"));

Realizing where I think things have gone wrong, I omitted the first
table, and began with link tbl1 into link tbl2 into "required data
column" table. Thinking that if I could control the one-to-manyness
problem in a smaller task, I could always expand it.

This SQL is:

SELECT DISTINCT OPENPAGES_SX__ASSOCIATION.PARENT,
OPENPAGES_SX__ASSOCIATION.CHILDTYPE,
OPENPAGES_SX_PROCESS.PR_MEGA_PROCESS,
OPENPAGES_SX_PROCESS.PR_DESCRIPTION
FROM (OPENPAGES_SX__ASSOCIATION INNER JOIN OPENPAGES_SX__PROCESS_FILE
ON OPENPAGES_SX__ASSOCIATION.CHILD =
OPENPAGES_SX__PROCESS_FILE.FILE_ID) INNER JOIN OPENPAGES_SX_PROCESS ON
OPENPAGES_SX__PROCESS_FILE.PROCESS_ID = OPENPAGES_SX_PROCESS.PROCESS_ID
WHERE (((OPENPAGES_SX__ASSOCIATION.PARENT)="4798") AND
((OPENPAGES_SX__ASSOCIATION.CHILDTYPE)="File"));

I get effectively the same results as previously.

Then I tried a query on ONLY the tbl that has the one-to-many links, it
too fails (for me). To simplify:

Link table1
----------------

A | a1 | dog
A | a2 | dog
A | a3 | dog
B | b1 | dog
B | b2 | dog
C | C1 | dog

I asked for a DISTINCT query showing columns 1 & 3, and I rec'd all 6
rows where I expected only
A | dog
B | dog
C | dog

Am I using DISTINCT wrong?
Is this problem because all these tables came from Oracle?
Where all the link fields are in fact "text" not integers?
There are NO relationships in my *.mbd, I just drag & drop linkages I
want in design view.

Please keep the comments coming.

Dick

Randy's advice seems good to me.

Larry