Distinctrow/Continuous form issue

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

Guest

I have an odd issue going on and I am not certain what is up. I have
searched around and I can not seem to find out what is going on.

First I have 2 tables, the first table has employee information in it and
the second has information for work locations. So my tables would look like
this (this is simplified as there are more columns, but the example should
highlight the issue).....

Table1
EmployeeID Employee OT
1 Bob yes
2 Tony no
3 Pat Yes

Table2
EmployeeID Location
1 Paris
2 Detroit
1 Rome
2 Hackensack

Now I built a query using the distinct keyword. The query gets the all the
employee information from table1, only for the employees listed in table2. I
don't need the information for each time the employee listed, therefore I
used "distinct". So in regards to my example, I would get two records, Bob
and Tony. The query returns the information I need and displays it in a
continuous form. The problem is that none of the data in the resultset is
updateable.

So I changed the "distinct" keyword in the query to "distinctrow" so the
results could be updated. The issue I am having now however is that the
continuous form doesn't show the results. I haven't changed anything else
with the query or with the form, in fact if I change the query back to
"distinct" instead of "distinctrow" then it displays the required information
(all be it unupdateable). I have checked the query itself to see if it
returns a dataset and it does, the form just doesn't display it.

I know this is an odd one, but any help would be appreciated. I have gone
through all I can think of and I am starting to pull my hair out.
 
I don't think you can do this. Using "Distinct" is similar to using a
sum query with a "Group By".

Just to be sure, make sure your tables have a primary key. Sometimes
Access won't let you update information if the tables don't have
primary keys.
 
From some reference materials I have read "Distinct" returns recordsets that
are not updateable, and that "Distinctrow" will return one that is. So far
just running the queries as is seems to confirm that information. The problem
I have, and I am not sure why, is when I attempt to display the results of
the "distinctrow" query in a continuous form - nothing displays. However
using the "Distinct" query, no problems, the information displays.

I need the employee information being displayed to be updateable so
"distinctrow" seems like the answer, but I don't know why my form won't show
the information.
 
OK - I have a work around. I have set up the query as a subform and it will
display the data in datasheet view. I was hoping to have it look better with
a custom form but that doesn't seem to be possible, unless someone has an
idea of what I missed.
 
Back
Top