Issue with SELECT DISTINCT query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm having a problem running a type of query I've used many times before.
To set the stage, I have 2 tables:

tblIncidents - where the field "IncidentCode" is the primary key
tblCARs - where the field "CARNo" is the primary key

A relationship is set up between the two tables through identically named
"IncidentCode" fields. It is a one-to-many (tblIncidents to
tblCARs)relationship, with referential integrity and cascade updating of
related fields.

When I try to run the following query on tblCARs:

SELECT DISTINCT tblCARs.PersonRespCAR
FROM tblCARs
ORDER BY tblCARs.PersonRespCAR;

I get the following error message:

"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplication data, remove
the index, or redefine the index to permit duplicate entries and try again."

I'm not sure what changes to tables this message is refering to. As far as
I know I'm only running a query off the table. Any suggestions or help in
getting this relatively simply query to run would be greatly appreciated!

Thanks,

SM
 
Hi, SM,

Lose the distinct. You only need distinct, or distinctrow, when you're
building off multiple queries or tables and you're afraid of multiple rows
showing up when you only want one; you never need it when you're building a
query from only one object. The correct SQL is:

SELECT tblCARs.PersonRespCAR
FROM tblCARs
GROUP BY PersonRespCAR
ORDER BY tblCARs.PersonRespCAR;

Hope this helps,

Sam
 
SM said:
Hi,

I'm having a problem running a type of query I've used many times
before. To set the stage, I have 2 tables:

tblIncidents - where the field "IncidentCode" is the primary key
tblCARs - where the field "CARNo" is the primary key

A relationship is set up between the two tables through identically
named "IncidentCode" fields. It is a one-to-many (tblIncidents to
tblCARs)relationship, with referential integrity and cascade updating
of related fields.

When I try to run the following query on tblCARs:

SELECT DISTINCT tblCARs.PersonRespCAR
FROM tblCARs
ORDER BY tblCARs.PersonRespCAR;

I get the following error message:

"The changes you requested to the table were not successful because
they would create duplicate values in the index, primary key, or
relationship. Change the data in the field or fields that contain
duplication data, remove the index, or redefine the index to permit
duplicate entries and try again."

I'm not sure what changes to tables this message is refering to. As
far as I know I'm only running a query off the table. Any
suggestions or help in getting this relatively simply query to run
would be greatly appreciated!
Click to expand...

Are you getting this from a VBA code module? Or are you manually running
this query using the Query Builder?
 
OfficeDev18 said:
Hi, SM,

Lose the distinct. You only need distinct, or distinctrow, when you're
building off multiple queries or tables and you're afraid of multiple
rows showing up when you only want one; you never need it when you're
building a query from only one object. The correct SQL is:
Click to expand...

You may want to review the online help about these keywords and rewrite this
response ... :-)
 
Thanks for the help! I've been using the DISTINCT keyword in appropriately
all this time. Using GROUP BY did the trick, and makes more sense after
reading your explanation!
 
I'm using the query builder in this case. I made the change as suggested by
OfficeDev18 and its working now.

Will Access process the query differently depending on how it is built
(query builder vs. VBA)?
 
I just did that.... what did I say wrong?

Sam
You may want to review the online help about these keywords and rewrite this
response ... :-)
Click to expand...
 
That you only need distinct with multiple tables. That is incorrect. Your
statement applies to distinctrow, not distinct.

For example, say you have a table called test containing the following rows:

col1 col2 col3
1 a b
1 a c
2 b b

select distinct col1,col2 from test

will result in:

1 a
2 b


Bob Barrows
 
SM said:
I'm using the query builder in this case. I made the change as
suggested by OfficeDev18 and its working now.
Click to expand...

Strange. I need to try to reproduce this.
Will Access process the query differently depending on how it is built
(query builder vs. VBA)?
Click to expand...

No. I was entertaining the possibility that you were blaming the wrong line
of code for the error.

Bob Barrows
 
SM said:
I'm using the query builder in this case. I made the change as
suggested by OfficeDev18 and its working now.
Click to expand...
Well, I set up your tables as you described and ran your original query with
no error.

Frankly, I'm at a loss.

Bob Barrows
 
Back
Top