Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Write a Query or Report  in Access that will avoid repition

Posted on 2011-10-03
10
Medium Priority
?
232 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:ref-IT
  • 4
  • 4
  • 2
10 Comments
 
LVL 36

Expert Comment

by:Norie
ID: 36903526
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
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 36903693
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
 
LVL 2

Author Comment

by:ref-IT
ID: 36903809
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 36

Expert Comment

by:Norie
ID: 36903873
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
 
LVL 2

Author Comment

by:ref-IT
ID: 36905784
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
 
LVL 36

Assisted Solution

by:Norie
Norie earned 500 total points
ID: 36905930
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
 
LVL 2

Author Comment

by:ref-IT
ID: 36910157
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 500 total points
ID: 36911114
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
 
LVL 36

Assisted Solution

by:Norie
Norie earned 500 total points
ID: 36911379
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
 
LVL 2

Author Closing Comment

by:ref-IT
ID: 36913423
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Implementing simple internal controls in the Microsoft Access application.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question