hide duplicates in related records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Hi,


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


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.

Michel Walsh said:
Hi,


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


Hoping it may help,
Vanderghast, Access MVP




Harry said:
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.
Click to expand...
Click to expand...
 
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".

Harry said:
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.

Michel Walsh said:
Hi,


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


Hoping it may help,
Vanderghast, Access MVP




Harry said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.

Harry said:
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".

Harry said:
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.

Michel Walsh said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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


Harry said:
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.

Michel Walsh said:
Hi,


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


Hoping it may help,
Vanderghast, Access MVP




Harry said:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union query producing duplicate records 7
Duplicates in union query 3
Union Query Access 2007 SP2 MSO 7
Duplicate Values in Query 1
Losing Records Between Union Query and Select Query 3
Union query not displaying select statement 1
merging 2 queries 3
Single WHERE and ORDER clause in UNION? 4

Back
Top