report number

[Richard]

Hi

I have a report that, on the detail format event checks the record for
duplicates, if yes print the first record and skip the duplicate record.

The problem is that the number (text box with running sum) doesn't skip ie

first record,

1 Comment one
2 Comment two (dup so third record skipped)
4 Comment four (the record is skipped but so is the number)

How do I overcome skipping the record and keeping the number 3? Hope you
understand what I am trying to say here.

Many thanks in advance
Richard

 

[Guest]

Hi Richard,

Can't you use the Hide Duplicates property of the control(s) on the report,
instead of using a detail format event?


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Richard]

Hi Tom

Thanks for taking the time to reply. I need to use the format event to check
for duplicates and format them as:

1 comments
2 comments(2) - if there are duplicates, i add a bracket with the number of
duplicates instead of showing two similar comments. Using the Hide
Duplicates property of the control will not achieve this.

Why is it that when the print section is cancelled but the line counter goes
on?

Richard

 

[Richard]

Hi Tom

I tried using the Hide Duplicates property of the text box. The report came
out like this:

1 comment
2 comment2
3
4 comment3

I thought if I could use it, I may just concanate the bracket to the
duplicate comment.

Richard

 

[Guest]

Okay, getting back to your original method, are you cancelling the format of
the detail section when you detect a duplicate? Can you not use a grouped
query, with a count if desired, to eliminate the duplicates but still provide
a count?


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Richard]

Hi Tom

IT is based on a union query. The union query will omit duplicates by
itself, so it has been workingvery well for the past 2 years. Then one
person enter a comment that is more than 255 characters, and UNION query
truncates the comments to 255 chr. Therefore I changed the query to a UNION
ALL to retrieve the full comment.

I had a previous post (28 Feb, "Union query truncates data"). Rick Brandt
has been helping me to find a solution but I couldn't manage to do it. So I
kept to UNION ALL query and worked on it at the report stage.

Richard

 

[Guest]

In answer to one of your last questions in the thread you mentioned:

"Would the second query be a union query also?"

The answer is no. The first Union query should eliminate the duplicates as
Rick indicated. The second query is a normal SELECT query, which includes
your Union query as a source table. Join this Union query to your table and
add the memo field from the table. It should then not be truncated.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Richard]

Hi Tom

I think I explained wrongly to Rick (my mistake).

Okay

the table is made up of 5 memo fields: any of it can be duplicated and may
exceed 255.

i need it to show up on one column in the report hence the need for a union
query.

How would I join the Union Query to the table to get the untruncated data if
I don't know which field it will be on?

The table consists of these fields:

1 Favourite Topic
2 Least Favo... Topic
3 Blah...
4 Other Comments

How would I go about joining the query?

Thanks again
Richard

 

[Guest]

If I understand you correctly, and I'm not sure that I do, you have five memo
fields in a table that can essentially store the same data? If so, it sounds
like you are storing a one-to-many relationship within a single table,
instead of storing these comments in a single memo field in a related table.
Perhaps a re-examination of your database design is in order.

How would I join the Union Query to the table to get the untruncated data if
I don't know which field it will be on?

I dunno. It sounds like you are up the proverbial s__t creek without a paddle.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Richard]

Hi Tom

The fields are to store comments of 5 different topics. The comments in each
topic may dup. For eg. there are 10 participants. 5 of them may input "good
workshop" in topic1 field.

Union query, output fileds - Topic, Comments

So the report will show like this

Topic1(Field Header)
Comments (Details secton)

Topic 2
Comments

and so on....

Is this corrent or as you say, i designed it wrongly?

Richard

 

[Guest]

Hi Richard,

The fields are to store comments of 5 different topics.

I believe you have designed it incorrectly. You should not have multiple
fields that store similar data. What happens if you need to track a sixth
comment in the future? Your design would require that you add another field
to the table. You'd then have to modify all queries, forms & reports that
involved these fields. A good rule of thumb to remember is the following:

Fields are expensive, records are cheap.

In other words, your design should allow you to add (or remove) topics
simply by adding (or deleting) records to tables.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Richard]

Hi Tom

Very true of your comment. I will need to look very carefully at the design
and taking note of your advice. Many thanks and appreciate your time and
help.

I might have solved the problem on hand. I stop using auto line counter and
did it manually. Seems to work, will try it out further...

Thanks again
Richard