473,320 Members | 1,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Distinct problem

Okay guys and gals here is a problem that I need help on, I am trying
to develop a report that will display only duplicate values. Here is an
example of the data that I am working with:
DATE CALL ID MEMBERSHIP TYPE TROUBLE
07/18/05 232 123456789 ERS 6
07/18/05 525 123456789 SVC 6
07/18/05 6585 456789123 ERS 7
07/18/05 5114 787941232 ERS 2
07/18/05 6742 336125522 SVC 3

What I want is a report that will display only the following

DATE CALL ID MEMBERSHIP TYPE TROUBLE
07/18/05 232 123456789 ERS 6
07/18/05 525 123456789 SVC 6

So the main point is that any membership that has more than one call in
a day that it would be reported. I have tried a group by on the
membership but I couldn't get it to work. All of the fields are located
within one table. Thanks in advance!

Nov 13 '05 #1
3 1363
Have you tried the "Find Duplicates" query wizard?

"Anthony Hendrata" <an**************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Okay guys and gals here is a problem that I need help on, I am trying
to develop a report that will display only duplicate values. Here is an
example of the data that I am working with:
DATE CALL ID MEMBERSHIP TYPE TROUBLE
07/18/05 232 123456789 ERS 6
07/18/05 525 123456789 SVC 6
07/18/05 6585 456789123 ERS 7
07/18/05 5114 787941232 ERS 2
07/18/05 6742 336125522 SVC 3

What I want is a report that will display only the following

DATE CALL ID MEMBERSHIP TYPE TROUBLE
07/18/05 232 123456789 ERS 6
07/18/05 525 123456789 SVC 6

So the main point is that any membership that has more than one call in
a day that it would be reported. I have tried a group by on the
membership but I couldn't get it to work. All of the fields are located
within one table. Thanks in advance!

Nov 13 '05 #2
Bruce Rusk wrote:
Have you tried the "Find Duplicates" query wizard?
I try to avoid sub Select statements in Access query for criteria.
Sometimes subselects can really slow things down.

It might be better that a Totals query be created with Date/Membership
fields (maybe Trouble) and select only those with a count > 1

Then link this query using the fields Date/Membership and perhaps
trouble to the main table to create relationships. Those that match are
duplicates.

"Anthony Hendrata" <an**************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Okay guys and gals here is a problem that I need help on, I am trying
to develop a report that will display only duplicate values. Here is an
example of the data that I am working with:
DATE CALL ID MEMBERSHIP TYPE TROUBLE
07/18/05 232 123456789 ERS 6
07/18/05 525 123456789 SVC 6
07/18/05 6585 456789123 ERS 7
07/18/05 5114 787941232 ERS 2
07/18/05 6742 336125522 SVC 3

What I want is a report that will display only the following

DATE CALL ID MEMBERSHIP TYPE TROUBLE
07/18/05 232 123456789 ERS 6
07/18/05 525 123456789 SVC 6

So the main point is that any membership that has more than one call in
a day that it would be reported. I have tried a group by on the
membership but I couldn't get it to work. All of the fields are located
within one table. Thanks in advance!


Nov 13 '05 #3
I didn't even know that this existed, I have probably seen it a million
times but never paid attention to it. Anyways I modified my query a bit
and the people that need the report are happy with it. Thanks alot for
the help!

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Martin Feuersteiner | last post by:
Dear Group I'm having trouble with the clause below. I would like to select only records with a distinct TransactionDate but somehow it still lists duplicates. I need to select the...
1
by: nfrodsham | last post by:
In Microsoft's help literature, it states: "You can filter out non-unique rows by using the DISTINCT option of an aggregate function" I am trying to do this in Access 2003 with the COUNT...
5
by: Daniel Wetzler | last post by:
Dear MSSQL experts, I use MSSQL 2000 and encountered a strange problem wqhile I tried to use a select into statement . If I perform the command command below I get only one dataset which has...
2
bergy
by: bergy | last post by:
Hello MS SQL experts, I'm trying to reorganize some data for a friend and I'm running into this problem. Currently he has some duplicate rows that I need to get rid of - only one of the columns has...
5
zachster17
by: zachster17 | last post by:
Hi everyone, First of all, sorry for the massive amount of SQL I am about to type. I have a database that has a "lives" table of insured employees and then another table (that links to the lives...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.