If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
hide duplicates in related records
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. |
#2
|
|||
|
|||
hide duplicates in related records
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 "Harry" wrote in message ... 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. |
#3
|
|||
|
|||
hide duplicates in related records
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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200608/1 |
#4
|
|||
|
|||
hide duplicates in related records
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 "hazbin via AccessMonster.com" u25197@uwe wrote in message news:651c036bd5f30@uwe... 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200608/1 |
#5
|
|||
|
|||
hide duplicates in related records
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 "Michel Walsh" wrote: 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 "hazbin via AccessMonster.com" u25197@uwe wrote in message news:651c036bd5f30@uwe... 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200608/1 |
#6
|
|||
|
|||
hide duplicates in related records
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! "Harry" wrote: 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. |
#7
|
|||
|
|||
hide duplicates in related records
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 "Harry" wrote in message ... 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 "Michel Walsh" wrote: 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 "hazbin via AccessMonster.com" u25197@uwe wrote in message news:651c036bd5f30@uwe... 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200608/1 |
Thread Tools | |
Display Modes | |
|
|