SQL query similar to DISTINCT help please

[dick]

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

[Larry Linson]

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_*****@usa.ne t> wrote in message
news:11******** **************@ r2g2000cwb.goog legroups.com...

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

[John Welch]

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

"dick" <d_*****@usa.ne t> wrote in message
news:11******** **************@ r2g2000cwb.goog legroups.com...

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

[Randy Harris]

* dick:

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

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.

[dick]

Larry Linson wrote:

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

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_AU DIT3.RESOURCEID ,
OPENPAGES_SX_AU DIT3.WHO, OPENPAGES_SX_AU DIT3.WHEN,
OPENPAGES_SX__A SSOCIATION.CHIL DTYPE,
OPENPAGES_SX_PR OCESS.PR_MEGA_P ROCESS,
OPENPAGES_SX_PR OCESS.PR_DESCRI PTION
FROM ((OPENPAGES_SX_ AUDIT3 INNER JOIN OPENPAGES_SX__A SSOCIATION ON
OPENPAGES_SX_AU DIT3.RESOURCEID = OPENPAGES_SX__A SSOCIATION.PARE NT)
INNER JOIN OPENPAGES_SX__P ROCESS_FILE ON
OPENPAGES_SX__A SSOCIATION.CHIL D = OPENPAGES_SX__P ROCESS_FILE.FIL E_ID)
INNER JOIN OPENPAGES_SX_PR OCESS ON
OPENPAGES_SX__P ROCESS_FILE.PRO CESS_ID = OPENPAGES_SX_PR OCESS.PROCESS_I D
WHERE (((OPENPAGES_SX _AUDIT3.RESOURC EID)="4798") AND
((OPENPAGES_SX_ AUDIT3.WHEN)>#6/6/2006#) AND
((OPENPAGES_SX_ _ASSOCIATION.CH ILDTYPE)="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__A SSOCIATION.PARE NT,
OPENPAGES_SX__A SSOCIATION.CHIL DTYPE,
OPENPAGES_SX_PR OCESS.PR_MEGA_P ROCESS,
OPENPAGES_SX_PR OCESS.PR_DESCRI PTION
FROM (OPENPAGES_SX__ ASSOCIATION INNER JOIN OPENPAGES_SX__P ROCESS_FILE
ON OPENPAGES_SX__A SSOCIATION.CHIL D =
OPENPAGES_SX__P ROCESS_FILE.FIL E_ID) INNER JOIN OPENPAGES_SX_PR OCESS ON
OPENPAGES_SX__P ROCESS_FILE.PRO CESS_ID = OPENPAGES_SX_PR OCESS.PROCESS_I D
WHERE (((OPENPAGES_SX __ASSOCIATION.P ARENT)="4798") AND
((OPENPAGES_SX_ _ASSOCIATION.CH ILDTYPE)="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

[dick]

Larry Linson wrote:

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

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_AU DIT3.RESOURCEID ,
OPENPAGES_SX_AU DIT3.WHO, OPENPAGES_SX_AU DIT3.WHEN,
OPENPAGES_SX__A SSOCIATION.CHIL DTYPE,
OPENPAGES_SX_PR OCESS.PR_MEGA_P ROCESS,
OPENPAGES_SX_PR OCESS.PR_DESCRI PTION
FROM ((OPENPAGES_SX_ AUDIT3 INNER JOIN OPENPAGES_SX__A SSOCIATION ON
OPENPAGES_SX_AU DIT3.RESOURCEID = OPENPAGES_SX__A SSOCIATION.PARE NT)
INNER JOIN OPENPAGES_SX__P ROCESS_FILE ON
OPENPAGES_SX__A SSOCIATION.CHIL D = OPENPAGES_SX__P ROCESS_FILE.FIL E_ID)
INNER JOIN OPENPAGES_SX_PR OCESS ON
OPENPAGES_SX__P ROCESS_FILE.PRO CESS_ID = OPENPAGES_SX_PR OCESS.PROCESS_I D
WHERE (((OPENPAGES_SX _AUDIT3.RESOURC EID)="4798") AND
((OPENPAGES_SX_ AUDIT3.WHEN)>#6/6/2006#) AND
((OPENPAGES_SX_ _ASSOCIATION.CH ILDTYPE)="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__A SSOCIATION.PARE NT,
OPENPAGES_SX__A SSOCIATION.CHIL DTYPE,
OPENPAGES_SX_PR OCESS.PR_MEGA_P ROCESS,
OPENPAGES_SX_PR OCESS.PR_DESCRI PTION
FROM (OPENPAGES_SX__ ASSOCIATION INNER JOIN OPENPAGES_SX__P ROCESS_FILE
ON OPENPAGES_SX__A SSOCIATION.CHIL D =
OPENPAGES_SX__P ROCESS_FILE.FIL E_ID) INNER JOIN OPENPAGES_SX_PR OCESS ON
OPENPAGES_SX__P ROCESS_FILE.PRO CESS_ID = OPENPAGES_SX_PR OCESS.PROCESS_I D
WHERE (((OPENPAGES_SX __ASSOCIATION.P ARENT)="4798") AND
((OPENPAGES_SX_ _ASSOCIATION.CH ILDTYPE)="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]

Randy's advice seems good to me.

Larry