By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,735 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

SQL query similar to DISTINCT help please

P: n/a
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

Jun 16 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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.net> wrote in message
news:11**********************@r2g2000cwb.googlegro ups.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

Jun 16 '06 #2

P: n/a
It would help if you posted some of the SQL strings you've tried.

"dick" <d_*****@usa.net> wrote in message
news:11**********************@r2g2000cwb.googlegro ups.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

Jun 16 '06 #3

P: n/a
* 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.
Jun 16 '06 #4

P: n/a

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_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

Jun 17 '06 #5

P: n/a

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_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

Jun 17 '06 #6

P: n/a
Randy's advice seems good to me.

Larry
Jun 18 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.