Select Distinct prevents editing

  • Thread starter Thread starter Jerry Crosby
  • Start date Start date
J

Jerry Crosby

I have a query as the data source for a form. I need to use the "select
distinct" function in the code, but when I do I am unable to edit the data
on the form. If I remove the "distinct", I'll get more records than I want,
I am able to edit the data on the form. Everything else remains the same.

Is there a work-around?

Thanks in advance.

Jerry
 
You may need to rethink your table structure. A "Distinct" query is used to
group values together so that you see just one of them (as you're doing),
but that query does not allow editing of the data because the "unique
record" from which the data come is no longer identifiable (kinda like if
you had many boxes with red bows in them, and you take a red bow from one
box and tell the user: "Make a mark on this bow and return it to the
original box", how would the user know from which box it came?).

The fact that you have data stored with the same values in multiple records
suggests that you're storing redundant data and not using a database for
what it's designed.

Tell us more about the data, the tables from which they come, and what you
want to accomplish.
 
Thanks, Ken. I'll see if I can explain what I have.

Table 1: Family Data
Fields: Family Last Name, 6 different fields representing dollar amounts for
different categories.

Table 2: Student Data
Fields: Student FirstName, StudentLastName, ActiveStudent (yes/no checkbox)

There is a one-to-many relationship between the Family Data table and the
Student Data table. One family could have more than one student. A student
can be "active" or not.

I want the query to select all the families who have at least one active
student. In my original query (without the "distinct") I would get multiple
identical records if the family had more than one active student. The
number of records usually corresponded to the number of active students in
the family.

Adding the "distinct" gave me what I wanted, but I was unable to edit the
dollar fields. I suppose I could make that section a subform, couldn't I?
Wouldn't that solve the problem? Guess I was just hoping for a single query
to do everything!

Did that help at all? Unfortunately, I don't have the file here at the
office, so I can't send the actual SQL code.

Jerry
 
Ken,

I failed to mention (because I know you'll ask!), but there is a FamilyID
field in both tables that the link is based on.

Jerry
 
Ahhh.. I believe that this query will meet your needs ( it will select only
the families with at least one active student ):

SELECT * FROM [Family Data]
WHERE [Family Data].FamilyID IN
(SELECT [Student Data].FamilyID
FROM [Student Data]
WHERE [Student Data].Active = True);
 
Ahhh.. I believe that this query will meet your needs ( it will select only
the families with at least one active student ):

SELECT * FROM [Family Data]
WHERE [Family Data].FamilyID IN
(SELECT [Student Data].FamilyID
FROM [Student Data]
WHERE [Student Data].Active = True);
 
May I cut in? I've run into the same situation and tried this solution but I
get an error that the * is a syntax error. I'm not an SQL-wise person. Is
the * supposed to be just that or does it represent something else? My
original query is a bit more complicated than Jerry's example so maybe that's
part of the trouble.

Here it is:

SELECT DISTINCT Students_new.EmplID, Students_new.FirstName,
Students_new.MiddleName, Students_new.LastName, Students_new.Street,
Students_new.City, Students_new.ST, Students_new.ZIP, Students_new.Country,
Students_new.Phone1, Students_new.Phone2, Students_new.Email,
Students_new.WantCert, Students_new.Granted, Students_new.Semesters,
Enrollment.CourseID, Enrollment.Semesters, Enrollment.Grade,
Courses_new.CourseNumber
FROM Students_new INNER JOIN (Courses_new INNER JOIN Enrollment ON
Courses_new.CourseID=Enrollment.CourseID) ON
Students_new.EmplID=Enrollment.EmplID
WHERE (((Students_new.LastName) Like [Enter Last Name] & "*") AND
((Students_new.Granted)=No) AND ((Students_new.Semesters) Is Null));

The purpose of this query is to find a student so his/her record can be
edited. I can find 'em - I just can't edit 'em this way. For me, it's not a
problem but I have users who need... easy.

Thanks for any help!


Ken Snell said:
Ahhh.. I believe that this query will meet your needs ( it will select only
the families with at least one active student ):

SELECT * FROM [Family Data]
WHERE [Family Data].FamilyID IN
(SELECT [Student Data].FamilyID
FROM [Student Data]
WHERE [Student Data].Active = True);

--

Ken Snell
<MS ACCESS MVP>



Jerry Crosby said:
Thanks, Ken. I'll see if I can explain what I have.

Table 1: Family Data
Fields: Family Last Name, 6 different fields representing dollar amounts
for different categories.

Table 2: Student Data
Fields: Student FirstName, StudentLastName, ActiveStudent (yes/no
checkbox)

There is a one-to-many relationship between the Family Data table and the
Student Data table. One family could have more than one student. A
student can be "active" or not.

I want the query to select all the families who have at least one active
student. In my original query (without the "distinct") I would get
multiple identical records if the family had more than one active student.
The number of records usually corresponded to the number of active
students in the family.

Adding the "distinct" gave me what I wanted, but I was unable to edit the
dollar fields. I suppose I could make that section a subform, couldn't I?
Wouldn't that solve the problem? Guess I was just hoping for a single
query to do everything!

Did that help at all? Unfortunately, I don't have the file here at the
office, so I can't send the actual SQL code.

Jerry
Click to expand...
Click to expand...
 
Back
Top