Dear B Roberts:
Before discussing how to do this, I'd like you to think about the
limitations in doing it.
If you want a list of the values in one column without duplicates, you
can simply:
SELECT DISTINCT Field1 FROM YourTable
If you want to show any other columns in the query, and if the values
in Field1 are duplicated in the table, then you must provide a rule
that says WHICH of the values in each additional column will be
displayed. After all, if the table has two rows:
A B
A C
And you want to show A only once, do you show it with B in the other
column, or with C? The query cannot and will not choose for you! You
must provide the solution.
Perhaps you want to show A with the "least" value of the second
column, so you write:
SELECT Field1, MIN(Field2) AS MinimumField2
FROM YourTable
GROUP BY ColumnName
Now, consider the problem with a third column of values:
A B 7
A C 4
Now, if you extend the rule above to show the minimum values of the
second and third columns:
SELECT Field1, MIN(Field2) AS MinimumField2,
MIN(Field3) AS MinimumField3
FROM YourTable
GROUP BY Field1
This would give a result:
A B 4
This is not representative of any of the rows in the original query,
although all the values are there.
One very common desire is to pick the ROW which has the minimum value
in the second column. This can be done in various ways. I suggest:
SELECT Field1, Field2, Field3
FROM YourTable T
WHERE Field2 = (SELECT MIN(Field2) FROM YourTable T1
WHERE T1.Field1 = T.Field1)
Now you will see:
A B 7
Do any of the above reflect what you want? If not, some more
description of what you want might be in order.
In any case, the point is that YOU must provide all the rules
necessary to decide which values are to be shown in the additional
columns. If you know for certain that Field2 and Field3 will always
have the same values for any given value of Field1, then you can just
make the whole query DISTINCT. But, if your assumption ever proves
untrue, you will then see more than one row for some value in Field1.
This forces you to design exactly what must be done to choose the
values in other rows. To not do so would leave the situation
ambiguous, and computers don't function in ambiguity. The onus is
always on you!
Please let me know if this helped, and if I can be of any other
assistance.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
I am trying to limit the value of a field in a query
to one time only so, if I have a field value of john doe,
I only want to see that value once only, in other words,
no duplicate field values.
-----Original Message-----
Why do you need the subquery?
B.R. said:
Hi Steve,
I tried:
In(SELECT DISTINCT [Field1] FROM [tablenameofField1])
This doesn't work and I am not sure why not?
Brian.
-----Original Message-----
See GROUP BY in the help file.
i.e. Select Field1, Count(Field2), Sum(Field3) from
tablename GROUP BY
Field1
--
HTH,
Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
I am trying to create a query and eventually a report
that performs a count on one field value once only.
eg. Field 1 Field 2 Field 3
Value 1 data data
Value 1 data data
Value 1 data data
Value 2 data data
Value 2 data data
Value 3 data data
So I would like Value 1, Value 2 and Value 3 to be
counted
or displayed once only.
.
.
