Select Distinct Query

[Dustin Swartz]

I have two colums in my "Select Distinct" query and they are working, Now
how do I bring along the non distinct information in these rows?

Obviously if I add the non distinct columns into my statement it changes the
select results

This is my current SQL statement

SELECT DISTINCT OsPos.PosNr, OsPos.Bez
FROM OsObj INNER JOIN OsPos ON OsObj.ObjNr = OsPos.ObjNr;

It brings in the correct records but only from OsPos.PosNr, OsPos.Bez

I want to say (I don't know SQL)

SELECT
OsPos.Obsum, OsPos.PosNr, OsPos.Bez

Where OsPos.PosNr, OsPos.Bez ARE a DISTINCT combination

Ideas?

 

[Tom Ellison]

Dear Dustin:

Your initial two column query has apparently reduced the number of
rows shown so there are no duplicates.

When you add a third column, and that third column draws on the same
source rows, it apparently has different values in some rows where the
initial two columns are the same. The query is probably displaying
two or more rows in order to display the varied values in this third
column. Is that right so far?

If you want to display only one of the values from this third column
you will need a rule as to which one you want and which you do not.

At this point it is probably very useful to point out that you can
change the original two column query to a "totals" query:

SELECT OsPos.PosNr, OsPos.Bez
FROM osObj
INNER JOIN OsPos ON OsObj.ObjNr = OsPos.ObjNr;
GROUP BY OsPos.PosNr, OsPos.Bez

While this is an identical approach to the DISTINCT, it is also a
starting point for more query work that may be just what you want.

Now, when you want to add a new column ObPos.Obsum to this, you have
some alternatives using Aggregate Functions. You could show the
smallest value of Obsum:

SELECT OsPos.PosNr, OsPos.Bez, MIN(ObPos.Obsum) AS LeastObSum
FROM osObj
INNER JOIN OsPos ON OsObj.ObjNr = OsPos.ObjNr;
GROUP BY OsPos.PosNr, OsPos.Bez

Replacing MIN with MAX you would see the largest value. Or, you could
add them together:

SELECT OsPos.PosNr, OsPos.Bez, SUM(ObPos.Obsum) AS SumObSum
FROM osObj
INNER JOIN OsPos ON OsObj.ObjNr = OsPos.ObjNr;
GROUP BY OsPos.PosNr, OsPos.Bez

You can also average them.

It is not possible to show them without creating additional rows of
results, as you probably already experienced. But exactly what you
wanted to see is not clear.

Any help here yet?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Gerald Stanley]

What exactly are you after? Are you saying that in the
table OsObj, there are multiple rows for any given PosNr,
Bez combination that have different values in Obsum but you
want only one of these rows to be returned. If so, what
criteria is to be applied to determine which of these
values is to be selected?

Gerald Stanley MCSD

 

[Michel Walsh]

Hi,


SELECT LAST(ObSum), PosNr, Bez
FROM OsPos
GROUP BY PosNr, Bez



If you need the join, add it (but also add the table name in front of fields
that may come from more than one of the tables).

Note that since nothing is tell about ObSum, if there is more than one
possible value for a given couple (PosNr, Bez), I decided to take "one" of
those possible values, using LAST( ).

Hoping it may help,
Vanderghast, Access MVP

 

[Dustin Swartz]

Ahh, Good stuff! Thank you Gentlemen!

Hi,


SELECT LAST(ObSum), PosNr, Bez
FROM OsPos
GROUP BY PosNr, Bez



If you need the join, add it (but also add the table name in front of fields
that may come from more than one of the tables).

Note that since nothing is tell about ObSum, if there is more than one
possible value for a given couple (PosNr, Bez), I decided to take "one" of
those possible values, using LAST( ).

Hoping it may help,
Vanderghast, Access MVP

 

[Dustin Swartz]

Gentlemen is "Group by" the same as "Order By"?


If not how do I add "group by" (Other than just typing it into the SQL)?


Example (ORDER BY OsPos.PosNr, OsPos.Bez

 

[Dustin Swartz]

Nevermind, I figured it out!

Gentlemen is "Group by" the same as "Order By"?


If not how do I add "group by" (Other than just typing it into the SQL)?


Example (ORDER BY OsPos.PosNr, OsPos.Bez