Several One to Many Relationships to be displayed on form

[KFKruzshak]

I have a query that provides the following structure of
information:
1 1a 1ai
1 1a 1aii
1 1b 1bi
1 1b 1bii
1 1b 1biii
1 1c 1ci
2 2a 2ai
2 2a 2aii
2 2a 2aiii
2 2a 2aiiii
2 2b 2bi
2 2c 2ci
2 2c 2cii
2 2d 2di

I would like to create a form that displays only the form
items listed below in my example. The query created and
the form I can come up with displays each record in its
entirety as illustrated above. The bottom line is I do
not want to display repear information on my form.
1 1a 1ai
1aii
1b 1bi
1bii
1biii
1c 1ci
2 2a 2ai
2aii
2aiii
2aiiii
2b 2bi
2c 2ci
2cii
2d 2di

Hope this is understandable. Does anyone have any
suggestions as to how I can achieve the previous display
of form data? Even a form from another application which
might be similar.

Thanks,

Kurt

 

[John Nurick]

Hi Kurt,

One way to do this is by using a further query containing several
subqueries which compare the values in each field with the values for
the "previous" record:

Assuming the fields in your present query are PK (a field that can be
used to sort the records into the desired order), First, Second and
Third, the query would look like this:

SELECT
IIF(
A.First = (
SELECT B.First
FROM Kruzshak AS B
WHERE B.PK = (SELECT MAX(PK) FROM Kruzshak WHERE PK < A.PK)
),
Null,
A.First) AS fFirst,

IIF(
A.Second = (
SELECT B.Second
FROM Kruzshak AS B
WHERE B.PK = (SELECT MAX(PK) FROM Kruzshak WHERE PK < A.PK)
),
Null,
A.Second) AS fSecond,

IIF(
A.Third = (
SELECT B.Third
FROM Kruzshak AS B
WHERE B.PK = (SELECT MAX(PK) FROM Kruzshak WHERE PK < A.PK)
),
Null,
A.Third) AS fThird

FROM Kruzshak AS A
ORDER BY PK;