hide duplicates in related records

[Guest]

I have a query that is showing info like this:
Name Thing Date
A A1 MAY
A A2 MAY
A A3 MAY
B B1 JUNE
B B2 JUNE
B B3 JUNE
B B4 JUNE
B B5 JUNE
C C1 JUNE
C C2 JUNE
C C3 JUNE
C C4 JUNE
Now I want to get rid of the duplicates so it looks like this:
Name Thing Date
A A1 MAY
A2
A3
B B1 JUNE
B2
B3
B4
B5
C C1 JUNE
C2
C3
C4
I tried HideDuplicates for the "Date" but it blocks JUNE from being displayed
again in ANY of the records. I'm looking for some code (I think) that says:
If a record has a duplicate "Name" then hide the records "Date" field. I'm
not sure if this would best be done in a query or report. Please help.

 

[Michel Walsh]

Hi,


and if you CAN easily spot the previous row, then


SELECT iif(Nz(b.Name=a.Name, false), "", b.Name) As TheName, b.Thing,
iif(Nz(b.Name=a.Name AND b.Date = a.Date, false) , "", b.date)
FROM b ....


where b is the reference about the actual row, and a the reference about the
previous row.


Hoping it may help
Vanderghast, Access MVP

 

[hazbin via AccessMonster.com]

I want to include this in an already existing query but I keep getting syntax
error messages or 'I'm leaving something out' messages. Help me adapt this.

Hi,

and if you CAN easily spot the previous row, then

SELECT iif(Nz(b.Name=a.Name, false), "", b.Name) As TheName, b.Thing,
iif(Nz(b.Name=a.Name AND b.Date = a.Date, false) , "", b.date)
FROM b ....

where b is the reference about the actual row, and a the reference about the
previous row.

Hoping it may help
Vanderghast, Access MVP

I have a query that is showing info like this:
Name Thing Date

[quoted text clipped - 31 lines]

I'm
not sure if this would best be done in a query or report. Please help.

 

[Michel Walsh]

Hi,



If the query is already existing, you can try to use it as if it was a
table, inside a new query.

Note, and I insist, that if getting the "previous record" is not obvious,
then it is probably NOT the way to go, but then, probably better with a
report.


Hoping it may help,
Vanderghast, Access MVP

I want to include this in an already existing query but I keep getting
syntax
error messages or 'I'm leaving something out' messages. Help me adapt
this.

Hi,

and if you CAN easily spot the previous row, then

SELECT iif(Nz(b.Name=a.Name, false), "", b.Name) As TheName, b.Thing,
iif(Nz(b.Name=a.Name AND b.Date = a.Date, false) , "", b.date)
FROM b ....

where b is the reference about the actual row, and a the reference about
the
previous row.

Hoping it may help
Vanderghast, Access MVP

I have a query that is showing info like this:
Name Thing Date

[quoted text clipped - 31 lines]

I'm
not sure if this would best be done in a query or report. Please help.

 

[Guest]

The previous record is obvious to me. It is just straight data to be
compared. I have been trying to adapt the code you gave me. I'm confused by
the "a" and "b" elements. Are those actual letters supposed to allow/cause a
comparison between the current row and the previous row? When I insert them,
and run the query, I get prompted to enter a parameter value for a.Name. and
b.Name. I don't want to enter values. There are no comparisons of dates like
my previous example query showed. Here is a more accurate example display of
query results that I want to alter:

Mailbox Slot Protocol
AD157 this FTP
AD157 that FTP
AD157 other FTP
AD159 some VPN
AD159 that VPN
AD160 this FTP
AD160 auto FTP
AD160 other FTP
AD160 all FTP

to look like this:

Mailbox Slot Protocol
AD157 this FTP
that
other
AD159 some VPN
that
AD160 this FTP
thing
other
object

Hi,



If the query is already existing, you can try to use it as if it was a
table, inside a new query.

Note, and I insist, that if getting the "previous record" is not obvious,
then it is probably NOT the way to go, but then, probably better with a
report.


Hoping it may help,
Vanderghast, Access MVP

I want to include this in an already existing query but I keep getting
syntax
error messages or 'I'm leaving something out' messages. Help me adapt
this.

Hi,

and if you CAN easily spot the previous row, then

SELECT iif(Nz(b.Name=a.Name, false), "", b.Name) As TheName, b.Thing,
iif(Nz(b.Name=a.Name AND b.Date = a.Date, false) , "", b.date)
FROM b ....

where b is the reference about the actual row, and a the reference about
the
previous row.

Hoping it may help
Vanderghast, Access MVP

I have a query that is showing info like this:
Name Thing Date
[quoted text clipped - 31 lines]
I'm
not sure if this would best be done in a query or report. Please help.

 

[Guest]

OK, I solved this issue with a report. The grouping option that Michel
mentioned previously. I grouped the records by mailbox. Then I could hide any
duplicates for each record associated with the mailbox. Exactly what I
wanted. Thank you for your assistance, Michel. I definitely learned a few
things!

 

[Michel Walsh]

Hi,


a and b are aliases. If your data would have been like:

id MailBox Slot Protocol ' fields
1 AS157 this FTP
2 ...
3 ...
4 ... ' data



where data, under id, is a continuous sequence of integers, then:


SELECT ...
FROM myTable As a RIGHT JOIN myTable As b ON b.id = a.id-1


would have do the job. Note that b.id = a.id -1 means that while b refer,
to, say, 3, then a refers to its own id = 3-1 = 2, so, in this case,
a.Mailbox is the Mailbox value for the line preceding b.Mailbox, whatever
"b - line" you consider as "current" line.


With your data, you don't have any obvious sequence of not-interrupted
integers, neither any obvious way to produce it, so using a report is much
easier and faster.


Hoping it may help,
Vanderghast, Access MVP

The previous record is obvious to me. It is just straight data to be
compared. I have been trying to adapt the code you gave me. I'm confused
by
the "a" and "b" elements. Are those actual letters supposed to allow/cause
a
comparison between the current row and the previous row? When I insert
them,
and run the query, I get prompted to enter a parameter value for a.Name.
and
b.Name. I don't want to enter values. There are no comparisons of dates
like
my previous example query showed. Here is a more accurate example display
of
query results that I want to alter:

Mailbox Slot Protocol
AD157 this FTP
AD157 that FTP
AD157 other FTP
AD159 some VPN
AD159 that VPN
AD160 this FTP
AD160 auto FTP
AD160 other FTP
AD160 all FTP

to look like this:

Mailbox Slot Protocol
AD157 this FTP
that
other
AD159 some VPN
that
AD160 this FTP
thing
other
object

Hi,



If the query is already existing, you can try to use it as if it was a
table, inside a new query.

Note, and I insist, that if getting the "previous record" is not obvious,
then it is probably NOT the way to go, but then, probably better with a
report.


Hoping it may help,
Vanderghast, Access MVP

I want to include this in an already existing query but I keep getting
syntax
error messages or 'I'm leaving something out' messages. Help me adapt
this.

Michel Walsh wrote:
Hi,

and if you CAN easily spot the previous row, then

SELECT iif(Nz(b.Name=a.Name, false), "", b.Name) As TheName, b.Thing,
iif(Nz(b.Name=a.Name AND b.Date = a.Date, false) , "", b.date)
FROM b ....

where b is the reference about the actual row, and a the reference
about
the
previous row.

Hoping it may help
Vanderghast, Access MVP

I have a query that is showing info like this:
Name Thing Date
[quoted text clipped - 31 lines]
I'm
not sure if this would best be done in a query or report. Please
help.