select distinct

[papa jonah]

I have a query that works with one problem, the query searches tables
that have a relationship with the orps data table. a couple of the
tables have many entries for each of the records in the orps data
table. As a result, I get several repeats of data from the orps data
table, when I only want it listed once - regardless of the number of
reason are listed.

I thought select distinct took care of that but apparently not.


SELECT DISTINCT [ORPS Data].Discovery_Date, [Zlookup: Type].Type,
[Zlookup: Reason].Reason, [Zlookup: SubReason].SubReason
FROM [Zlookup: Type] INNER JOIN ([Zlookup: Subtype] INNER JOIN
([Zlookup: SubReason] INNER JOIN ([Zlookup: Reason] INNER JOIN ((([ORPS
Data] INNER JOIN QryDivision ON [ORPS Data].[ORPS Designator] =
QryDivision.[ORPS Designator]) INNER JOIN TblTypeSubType ON [ORPS
Data].[ORPS Designator] = TblTypeSubType.[ORPS Designator]) INNER JOIN
TblReasonSubReason ON [ORPS Data].[ORPS Designator] =
TblReasonSubReason.[ORPS Designator]) ON [Zlookup: Reason].ReasonID =
TblReasonSubReason.Reason) ON [Zlookup: SubReason].SubReasonID =
TblReasonSubReason.SubReason) ON [Zlookup: Subtype].SubtypeID =
TblTypeSubType.idSubtype) ON [Zlookup: Type].TypeID =
TblTypeSubType.Type
WHERE ((([Zlookup: Type].Type) Like "*" & [Forms]![query
form]![txttopicinput] & "*")) OR ((([Zlookup: Reason].Reason) Like "*"
& [Forms]![query form]![txttopicinput] & "*")) OR ((([Zlookup:
SubReason].SubReason) Like "*" & [Forms]![query form]![txttopicinput] &
"*"))
ORDER BY [ORPS Data].Discovery_Date;

 

[Tom Ellison]

Dear Jonah:

DISTINCT will eliminate all but one row from a set of rows in which all the
columns are identical.

Now, when you create INNER JOINs, and when you have subsidiary tables in
that join that have multiple rows, the query must show a row of results for
every row in the subsidiary table that meets the JOIN requirements.

I believe your expectations are unrealistic. Given that the query must show
the data in every row from every JOINed table that meets the JOIN
requirements, how could you expect anything else. The values the "parent"
table's columns must be repeated for each different subsidiary table's rows
that relate to it.

If you want only one row from the parent table, and there are multiple child
table rows for that row, you must supply the conditions that filter out all
but one of the child table rows. The query engine will not arbitrarily
remove all but one for you.

If you want to supply a basis on which to limit the child tables to one row
each, we can likely proceed.

Tom Ellison

 

[papa jonah]

Thanks for giving an explanation for me to build on. I really
appreciate it.
To make sure that I am properly understanding you, if I ask the query
to show each of the child table results, I should expect the multiple
listing of the parent table.
However, if I limit the display of child table stuff, I can expect to
limit the parent table data to one row per record?
That seems to work.

With your well-phrased (based on my level of stupid with Access)
explanation, I think I understand why it wasn't working like I thought
it should.

THANK YOU VERY MUCH!!

 

[Tom Ellison]

Dear Jonah:

It seems you have the correct understanding now. I'm glad for any help you
received.

Tom Ellison