Write a Query or Report in Access that will avoid repition

Hello Experts,

I have attached a simple MS Access Database that has three tables, a query and a report. The report is linked to the query.

When you run the report (just by double clicking on it), you see that the email field will have data that repeats itself for every unique phone field. How can I re-write the query that will not repeat the email data for every unique phone number? Or maybe I have to make some amendments to the report to avoid the repitiion?

Any help will be appreciated.

ref-IT
LVL 2
Mudasir NooraniSenior Analyst ProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
No attachment I'm afraid.

If it's the report the real problem is with then you could probably do something with grouping.

Hard to tell what without seeing the database or knowing more about it.
0
peter57rCommented:
Given that you are showing every unique phone number, what are you expecting to see in the email field?

In a report you can set the Hide duplicates property for the field - does that give you what you want?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mudasir NooraniSenior Analyst ProgrammerAuthor Commented:
Oops ... im sorry experts, I tried to upload the .mdb file but I think it didn't upload. I tried again, but again it failed. I have therefore attached a .zip file.

Please have a look and let me know.

Thanks in advance.
Query-to-Avoid-Field-Duplicate.zip
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NorieVBA ExpertCommented:
If there's only going to be one email address per company move the email field into the Ref header along with Company Name and URL.
0
Mudasir NooraniSenior Analyst ProgrammerAuthor Commented:
Thanks for the comment imnorie.

No, there's not going to be only one email address per company. There can be multiple email addresses per company. I am also aware that if there is going to be multiple email addresses and one phone number for that company, then the phone number will also be repeated for every unique email. Now that is what I want to solve.

I want to be able to write a query where if there is only one email and multiple phones, then the email should not be repeated for every unique phone, and if there are multiple emails and only one phone, then the phone should not be repeated for every unique email. Can that be done?

Maybe - just may be what we could do is have a sub-report (for the emails separately and for the phones separately) inside the report - but I was thinking maybe theres a smarter way to either write the query or design the report.

Any comments will be highly appreciated.

ref-IT
0
NorieVBA ExpertCommented:
Well try what peter57r suggests.

You'll find the Hide Duplicates property on the Format tab of the Properties of the textboxes.

It's the 7th from the bottom.
0
Mudasir NooraniSenior Analyst ProgrammerAuthor Commented:
Fantastic Man - you guys are definately experts. The Hide Duplicates property for the Texbox worked quite well - it did the job.

Just before I sign off and give points to both of you for your help. I only wanted to ask if it was possible to write a query that would give me a similar result. I mean, obviously, this solution worked well, and I am quite satisfied in using it, but just out of curiosity, is it possible to write a query that would give me results as above?

ref-IT
0
peter57rCommented:
You would never do such a thing in a query even if it were possible.
There is no equivalent 'hide duplicates' command and, since the query cannot predict how the data will be sorted in a report or form later, it is inappropriate to try to look at the previous or next record in the query (which is quite difficult anyway)
A query result provides the data for a form or report.  It is not intended to be a 'report' on its own.
0
NorieVBA ExpertCommented:
The query gives you the data you need, but you have to format it how you want it for a report.

Access can be quite helpful with that if you use the wizards.

PS I know reports produced by the wizards are perhaps a bit 'lame' but they give you a start.:)
0
Mudasir NooraniSenior Analyst ProgrammerAuthor Commented:
Fair enough .. I thank you really for all the help and truly do appreciate all your comments.

I have definately learnt something today :)

ref-IT
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.