Duplicate query

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

Guest

I only want a query to look at duplicates by MR and sorted by MR.
I want to be able to delete the records I choose to delete (manually).
Is there a query that I use that modified the query I have (this query finds
all records) I only want to display and delete duplicat I choose:

SELECT Table1.MR, Table1.SHEETDATE, Table1.SERVICE, Table1.[TRIAGE DTE],
Table1.[TRIAGE TIME], Table1.[SMAR TIME], Table1.[UPDG DTE], Table1.[UPDG
TIME], Table1.[BED DTE], Table1.[BED TIME], Table1.[REC DTE], Table1.[REC
TIME], Table1.WARD, Table1.[WARD DTE], Table1.[WARD TIME], Table1.[SMAR DTE]
FROM Table1
WHERE (((Table1.MR) Is Not Null) AND ((Table1.[TRIAGE DTE]) Between [Start
Date] And [End Date]) AND ((Table1.WARD) Not Between "16" And "23"));
 
Dear Blade:

I reproduce your original query here, edited to suit my preferences, so I
can study it:

SELECT MR, SHEETDATE, SERVICE, [TRIAGE DTE]
[TRIAGE TIME], [SMAR TIME], [UPDG DTE], [UPDG TIME]
[BED DTE], [BED TIME], [REC DTE], [REC TIME], WARD,
[WARD DTE], [WARD TIME], [SMAR DTE]
FROM Table1
WHERE MR Is Not Null AND
[TRIAGE DTE] Between [Start Date] AND [End Date] AND
WARD Not Between "16" AND "23"

I belive you could simply add this:

AND MR IN (SELECT MR
FROM Table1
GROUP BY MR
HAVING COUNT(*) > 1)
ORDER BY MR

If I have understood correctly, this may do it for you. Please let me know
if this helped and if I can be of any other assistance.

May I venture to suggest you use mixed case column names and eliminate
spaces within them, such as:

SheetDate, Service, TriageDte, TriageTime

Also, using separate columns for the data and the time involved in a single
quantity is usually disadvantageous. The Date/Time data type sotres both in
a single column and is usually indicated.

Tom Ellison
Microsoft Access MVP
 
Hint:

sotres = sorts

woops!

Tom Ellison
Microsoft Access MVP

Tom Ellison said:
Dear Blade:

I reproduce your original query here, edited to suit my preferences, so I
can study it:

SELECT MR, SHEETDATE, SERVICE, [TRIAGE DTE]
[TRIAGE TIME], [SMAR TIME], [UPDG DTE], [UPDG TIME]
[BED DTE], [BED TIME], [REC DTE], [REC TIME], WARD,
[WARD DTE], [WARD TIME], [SMAR DTE]
FROM Table1
WHERE MR Is Not Null AND
[TRIAGE DTE] Between [Start Date] AND [End Date] AND
WARD Not Between "16" AND "23"

I belive you could simply add this:

AND MR IN (SELECT MR
FROM Table1
GROUP BY MR
HAVING COUNT(*) > 1)
ORDER BY MR

If I have understood correctly, this may do it for you. Please let me
know if this helped and if I can be of any other assistance.

May I venture to suggest you use mixed case column names and eliminate
spaces within them, such as:

SheetDate, Service, TriageDte, TriageTime

Also, using separate columns for the data and the time involved in a
single quantity is usually disadvantageous. The Date/Time data type
sotres both in a single column and is usually indicated.

Tom Ellison
Microsoft Access MVP


bladelock said:
I only want a query to look at duplicates by MR and sorted by MR.
I want to be able to delete the records I choose to delete (manually).
Is there a query that I use that modified the query I have (this query
finds
all records) I only want to display and delete duplicat I choose:

SELECT Table1.MR, Table1.SHEETDATE, Table1.SERVICE, Table1.[TRIAGE DTE],
Table1.[TRIAGE TIME], Table1.[SMAR TIME], Table1.[UPDG DTE], Table1.[UPDG
TIME], Table1.[BED DTE], Table1.[BED TIME], Table1.[REC DTE], Table1.[REC
TIME], Table1.WARD, Table1.[WARD DTE], Table1.[WARD TIME], Table1.[SMAR
DTE]
FROM Table1
WHERE (((Table1.MR) Is Not Null) AND ((Table1.[TRIAGE DTE]) Between
[Start
Date] And [End Date]) AND ((Table1.WARD) Not Between "16" And "23"));
Click to expand...
Click to expand...
 
Worked like a charm, thanks. However what do you mean by mixed case columns?

Tom Ellison said:
Hint:

sotres = sorts

woops!

Tom Ellison
Microsoft Access MVP

Tom Ellison said:
Dear Blade:

I reproduce your original query here, edited to suit my preferences, so I
can study it:

SELECT MR, SHEETDATE, SERVICE, [TRIAGE DTE]
[TRIAGE TIME], [SMAR TIME], [UPDG DTE], [UPDG TIME]
[BED DTE], [BED TIME], [REC DTE], [REC TIME], WARD,
[WARD DTE], [WARD TIME], [SMAR DTE]
FROM Table1
WHERE MR Is Not Null AND
[TRIAGE DTE] Between [Start Date] AND [End Date] AND
WARD Not Between "16" AND "23"

I belive you could simply add this:

AND MR IN (SELECT MR
FROM Table1
GROUP BY MR
HAVING COUNT(*) > 1)
ORDER BY MR

If I have understood correctly, this may do it for you. Please let me
know if this helped and if I can be of any other assistance.

May I venture to suggest you use mixed case column names and eliminate
spaces within them, such as:

SheetDate, Service, TriageDte, TriageTime

Also, using separate columns for the data and the time involved in a
single quantity is usually disadvantageous. The Date/Time data type
sotres both in a single column and is usually indicated.

Tom Ellison
Microsoft Access MVP


bladelock said:
I only want a query to look at duplicates by MR and sorted by MR.
I want to be able to delete the records I choose to delete (manually).
Is there a query that I use that modified the query I have (this query
finds
all records) I only want to display and delete duplicat I choose:

SELECT Table1.MR, Table1.SHEETDATE, Table1.SERVICE, Table1.[TRIAGE DTE],
Table1.[TRIAGE TIME], Table1.[SMAR TIME], Table1.[UPDG DTE], Table1.[UPDG
TIME], Table1.[BED DTE], Table1.[BED TIME], Table1.[REC DTE], Table1.[REC
TIME], Table1.WARD, Table1.[WARD DTE], Table1.[WARD TIME], Table1.[SMAR
DTE]
FROM Table1
WHERE (((Table1.MR) Is Not Null) AND ((Table1.[TRIAGE DTE]) Between
[Start
Date] And [End Date]) AND ((Table1.WARD) Not Between "16" And "23"));
Click to expand...
Click to expand...
Click to expand...
 
Dear Blade:

Your column names are all capital letters. You have spaces inside them. It
is much easier to use terms like:

SheetDate
TriageDate
SmarTime

If you write queries in text form, most programmers will agree these terms
are much easier to read and to type. It avoids needing the square brackets,
for one thing.

This is a point that is not essential, but just a suggestion nearly all
experienced query designers will follow.

Tom Ellison
Microsoft Access MVP


bladelock said:
Worked like a charm, thanks. However what do you mean by mixed case
columns?

Tom Ellison said:
Hint:

sotres = sorts

woops!

Tom Ellison
Microsoft Access MVP

Tom Ellison said:
Dear Blade:

I reproduce your original query here, edited to suit my preferences, so
I
can study it:

SELECT MR, SHEETDATE, SERVICE, [TRIAGE DTE]
[TRIAGE TIME], [SMAR TIME], [UPDG DTE], [UPDG TIME]
[BED DTE], [BED TIME], [REC DTE], [REC TIME], WARD,
[WARD DTE], [WARD TIME], [SMAR DTE]
FROM Table1
WHERE MR Is Not Null AND
[TRIAGE DTE] Between [Start Date] AND [End Date] AND
WARD Not Between "16" AND "23"

I belive you could simply add this:

AND MR IN (SELECT MR
FROM Table1
GROUP BY MR
HAVING COUNT(*) > 1)
ORDER BY MR

If I have understood correctly, this may do it for you. Please let me
know if this helped and if I can be of any other assistance.

May I venture to suggest you use mixed case column names and eliminate
spaces within them, such as:

SheetDate, Service, TriageDte, TriageTime

Also, using separate columns for the data and the time involved in a
single quantity is usually disadvantageous. The Date/Time data type
sotres both in a single column and is usually indicated.

Tom Ellison
Microsoft Access MVP


I only want a query to look at duplicates by MR and sorted by MR.
I want to be able to delete the records I choose to delete (manually).
Is there a query that I use that modified the query I have (this query
finds
all records) I only want to display and delete duplicat I choose:

SELECT Table1.MR, Table1.SHEETDATE, Table1.SERVICE, Table1.[TRIAGE
DTE],
Table1.[TRIAGE TIME], Table1.[SMAR TIME], Table1.[UPDG DTE],
Table1.[UPDG
TIME], Table1.[BED DTE], Table1.[BED TIME], Table1.[REC DTE],
Table1.[REC
TIME], Table1.WARD, Table1.[WARD DTE], Table1.[WARD TIME],
Table1.[SMAR
DTE]
FROM Table1
WHERE (((Table1.MR) Is Not Null) AND ((Table1.[TRIAGE DTE]) Between
[Start
Date] And [End Date]) AND ((Table1.WARD) Not Between "16" And "23"));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top