hide duplicates in related records

[Harry]

I am hoping to get further assistance from Michel Walsh about an issue
from Aug 19 (hide duplicates in related records) in the query section. I
realize now that the simple solutions are not working for me. Grouping
records together in the header creates extra rows that I can’t have. I am
unable to use a subreport for my report display issue because Access tells me
that I am trying to use an “unsupported objectâ€. This refers to the Union
query I created and the “Name AutoCorrect†feature in Access. I don’t
understand that at all.

I would like your help in creating the query code you mentioned. You
gave me some partial examples but I need you to help me create the code
needed to perform this operation:

If Mailbox is a duplicate (a.Mailbox = b.Mailbox? is True), then “hide†the
display for Protocol and IP Address and Status. (the Mailbox values are not
in an obvious order.)

I believe this would need to be combined with the union query that I created
to list items in one column instead of several:

SELECT Mailbox, 1, [File-01]
FROM [CLIENT TOTAL]
WHERE [File-01] Is Not Null
UNION ALL
SELECT Mailbox, 2, [File-02]
FROM [CLIENT TOTAL]
WHERE [File-02] Is Not Null
UNION ALL
SELECT Mailbox, 3, [File-03]
FROM [CLIENT TOTAL]
WHERE [File-03] Is Not Null
UNION ALL
SELECT Mailbox, 4, [File-04]
FROM [CLIENT TOTAL]
WHERE [File-04] Is Not Null
UNION ALL

I hope you are willing to help me with this. I will certainly provide any
other task-specific information as you need it. Thank you.

 

[Michel Walsh]

Hi,


Remove the last UNION ALL. The problem was quite probably the query, not the
report.


Hoping it may help,
Vanderghast, Access MVP

 

[Harry]

Hi,

I think you're right about the query. I noticed that the single column that
is created from the union query has been automatically given the name of the
first column in the union (File-01). Is there code I can enter to manually
define the name of that union query column? If I can do that, I think my
problem will be solved and I can use the union query as a subreport. Please
help me figure this out.

Hi,


Remove the last UNION ALL. The problem was quite probably the query, not the
report.


Hoping it may help,
Vanderghast, Access MVP

I am hoping to get further assistance from Michel Walsh about an issue
from Aug 19 (hide duplicates in related records) in the query section. I
realize now that the simple solutions are not working for me. Grouping
records together in the header creates extra rows that I can't have. I am
unable to use a subreport for my report display issue because Access tells
me
that I am trying to use an "unsupported object". This refers to the Union
query I created and the "Name AutoCorrect" feature in Access. I don't
understand that at all.

I would like your help in creating the query code you mentioned. You
gave me some partial examples but I need you to help me create the code
needed to perform this operation:

If Mailbox is a duplicate (a.Mailbox = b.Mailbox? is True), then "hide"
the
display for Protocol and IP Address and Status. (the Mailbox values are
not
in an obvious order.)

I believe this would need to be combined with the union query that I
created
to list items in one column instead of several:

SELECT Mailbox, 1, [File-01]
FROM [CLIENT TOTAL]
WHERE [File-01] Is Not Null
UNION ALL
SELECT Mailbox, 2, [File-02]
FROM [CLIENT TOTAL]
WHERE [File-02] Is Not Null
UNION ALL
SELECT Mailbox, 3, [File-03]
FROM [CLIENT TOTAL]
WHERE [File-03] Is Not Null
UNION ALL
SELECT Mailbox, 4, [File-04]
FROM [CLIENT TOTAL]
WHERE [File-04] Is Not Null
UNION ALL

I hope you are willing to help me with this. I will certainly provide any
other task-specific information as you need it. Thank you.

 

[Harry]

By the way, I did take out the the last UNION ALL but there was no change in
its behavior as a subreport. This "Name AutoCorrect" feature is tripping me
up. If I can define the name of the column in the union query, I think I can
get around this "unsupported object".

Hi,

I think you're right about the query. I noticed that the single column that
is created from the union query has been automatically given the name of the
first column in the union (File-01). Is there code I can enter to manually
define the name of that union query column? If I can do that, I think my
problem will be solved and I can use the union query as a subreport. Please
help me figure this out.

Hi,


Remove the last UNION ALL. The problem was quite probably the query, not the
report.


Hoping it may help,
Vanderghast, Access MVP

I am hoping to get further assistance from Michel Walsh about an issue
from Aug 19 (hide duplicates in related records) in the query section. I
realize now that the simple solutions are not working for me. Grouping
records together in the header creates extra rows that I can't have. I am
unable to use a subreport for my report display issue because Access tells
me
that I am trying to use an "unsupported object". This refers to the Union
query I created and the "Name AutoCorrect" feature in Access. I don't
understand that at all.

I would like your help in creating the query code you mentioned. You
gave me some partial examples but I need you to help me create the code
needed to perform this operation:

If Mailbox is a duplicate (a.Mailbox = b.Mailbox? is True), then "hide"
the
display for Protocol and IP Address and Status. (the Mailbox values are
not
in an obvious order.)

I believe this would need to be combined with the union query that I
created
to list items in one column instead of several:

SELECT Mailbox, 1, [File-01]
FROM [CLIENT TOTAL]
WHERE [File-01] Is Not Null
UNION ALL
SELECT Mailbox, 2, [File-02]
FROM [CLIENT TOTAL]
WHERE [File-02] Is Not Null
UNION ALL
SELECT Mailbox, 3, [File-03]
FROM [CLIENT TOTAL]
WHERE [File-03] Is Not Null
UNION ALL
SELECT Mailbox, 4, [File-04]
FROM [CLIENT TOTAL]
WHERE [File-04] Is Not Null
UNION ALL

I hope you are willing to help me with this. I will certainly provide any
other task-specific information as you need it. Thank you.

 

[Harry]

Ok. I actually saw it work this time exactly the way I want it. I just went
back and re-named and re-created some things including this union query. Now
Access accepts the union query as a subreport. I think I changed the name of
something and Access didn't like that.

By the way, I did take out the the last UNION ALL but there was no change in
its behavior as a subreport. This "Name AutoCorrect" feature is tripping me
up. If I can define the name of the column in the union query, I think I can
get around this "unsupported object".

Hi,

I think you're right about the query. I noticed that the single column that
is created from the union query has been automatically given the name of the
first column in the union (File-01). Is there code I can enter to manually
define the name of that union query column? If I can do that, I think my
problem will be solved and I can use the union query as a subreport. Please
help me figure this out.

Hi,


Remove the last UNION ALL. The problem was quite probably the query, not the
report.


Hoping it may help,
Vanderghast, Access MVP




I am hoping to get further assistance from Michel Walsh about an issue
from Aug 19 (hide duplicates in related records) in the query section. I
realize now that the simple solutions are not working for me. Grouping
records together in the header creates extra rows that I can't have. I am
unable to use a subreport for my report display issue because Access tells
me
that I am trying to use an "unsupported object". This refers to the Union
query I created and the "Name AutoCorrect" feature in Access. I don't
understand that at all.

I would like your help in creating the query code you mentioned. You
gave me some partial examples but I need you to help me create the code
needed to perform this operation:

If Mailbox is a duplicate (a.Mailbox = b.Mailbox? is True), then "hide"
the
display for Protocol and IP Address and Status. (the Mailbox values are
not
in an obvious order.)

I believe this would need to be combined with the union query that I
created
to list items in one column instead of several:

SELECT Mailbox, 1, [File-01]
FROM [CLIENT TOTAL]
WHERE [File-01] Is Not Null
UNION ALL
SELECT Mailbox, 2, [File-02]
FROM [CLIENT TOTAL]
WHERE [File-02] Is Not Null
UNION ALL
SELECT Mailbox, 3, [File-03]
FROM [CLIENT TOTAL]
WHERE [File-03] Is Not Null
UNION ALL
SELECT Mailbox, 4, [File-04]
FROM [CLIENT TOTAL]
WHERE [File-04] Is Not Null
UNION ALL

I hope you are willing to help me with this. I will certainly provide any
other task-specific information as you need it. Thank you.

 

[Michel Walsh]

Hi,


SELECT Mailbox, 1 AS FieldName1, [File-01] As FieldName2
FROM [CLIENT TOTAL]
WHERE [File-01] Is Not Null
UNION ALL
....


ie, supply alias to the expression, in the first SELECT.



Hoping it may help,
Vanderghast, Access MVP

Hi,

I think you're right about the query. I noticed that the single column
that
is created from the union query has been automatically given the name of
the
first column in the union (File-01). Is there code I can enter to manually
define the name of that union query column? If I can do that, I think my
problem will be solved and I can use the union query as a subreport.
Please
help me figure this out.

Hi,


Remove the last UNION ALL. The problem was quite probably the query, not
the
report.


Hoping it may help,
Vanderghast, Access MVP

I am hoping to get further assistance from Michel Walsh about an
issue
from Aug 19 (hide duplicates in related records) in the query section.
I
realize now that the simple solutions are not working for me. Grouping
records together in the header creates extra rows that I can't have. I
am
unable to use a subreport for my report display issue because Access
tells
me
that I am trying to use an "unsupported object". This refers to the
Union
query I created and the "Name AutoCorrect" feature in Access. I don't
understand that at all.

I would like your help in creating the query code you mentioned.
You
gave me some partial examples but I need you to help me create the code
needed to perform this operation:

If Mailbox is a duplicate (a.Mailbox = b.Mailbox? is True), then "hide"
the
display for Protocol and IP Address and Status. (the Mailbox values are
not
in an obvious order.)

I believe this would need to be combined with the union query that I
created
to list items in one column instead of several:

SELECT Mailbox, 1, [File-01]
FROM [CLIENT TOTAL]
WHERE [File-01] Is Not Null
UNION ALL
SELECT Mailbox, 2, [File-02]
FROM [CLIENT TOTAL]
WHERE [File-02] Is Not Null
UNION ALL
SELECT Mailbox, 3, [File-03]
FROM [CLIENT TOTAL]
WHERE [File-03] Is Not Null
UNION ALL
SELECT Mailbox, 4, [File-04]
FROM [CLIENT TOTAL]
WHERE [File-04] Is Not Null
UNION ALL

I hope you are willing to help me with this. I will certainly provide
any
other task-specific information as you need it. Thank you.