Need help deleting records that have one equal field

N

normalit

I am working on a database for a police department tracking the weapons
that officers have qualified with at any time. I have two tables -
tbOFFID & Qualify.

Important fields:
[tbOFFID]:
ID# (primary key, also employee number)
Name
Rank
[Qualify]:
OffID (3 digit numerical employee ID, same as tbOFFID.ID#)
WpnTyp (types of weapons: Primary, Backup, AR15, etc)
Make
Serial
Model
QualDate (qualification date)

The major issue I am facing is getting rid of records with duplicates.
The duplicate field I would like to delete is Serial. If an officer
qualifies every year with a particular weapon, each record will have
the same serial number, but different QualDate values. Makes are also
entered differently in some places: "H&K" vs "H & K", so the records
are not true duplicates.

The goal: to get a list (into a report) of all weapons that an officer
has qualified with, but only list the weapons (by serial) once.

I have tried several methods, and am not able to get the final result.
Here are the two queries I tried last. The first is to get all fields
together in one table, which shows duplicate serials. The second was to
attempt to get rid of the duplicate serials. Both are SQL from Access,
with no added code by me.

SELECT Qualify.OffID, tbOFFID.Rank, tbOFFID.Name, Qualify.Make,
Qualify.Model, Qualify.Serial, Qualify.WpnTyp, Qualify.QualDate
FROM Qualify INNER JOIN tbOFFID ON Qualify.OffID = tbOFFID.[ID#]
WHERE (((Qualify.OfcOwn)=True));

******

SELECT [Q All Qualified Weapons].OffID AS [OffID Field], [Q All
Qualified Weapons].Rank AS [Rank Field], [Q All Qualified
Weapons].Name, [Q All Qualified Weapons].Make AS [Make Field], [Q All
Qualified Weapons].Model AS [Model Field], First([Q All Qualified
Weapons].Serial) AS [Serial Field], Last([Q All Qualified
Weapons].QualDate) AS LastOfQualDate, [Q All Qualified Weapons].WpnTyp
FROM [Q All Qualified Weapons]
GROUP BY [Q All Qualified Weapons].OffID, [Q All Qualified
Weapons].Rank, [Q All Qualified Weapons].Name, [Q All Qualified
Weapons].Make, [Q All Qualified Weapons].Model, [Q All Qualified
Weapons].WpnTyp, [Q All Qualified Weapons].OffID, [Q All Qualified
Weapons].Rank, [Q All Qualified Weapons].Make, [Q All Qualified
Weapons].Model, [Q All Qualified Weapons].Serial, [Q All Qualified
Weapons].OffID
HAVING ((([Q All Qualified Weapons].WpnTyp)="Primary Duty Weapon" Or
([Q All Qualified Weapons].WpnTyp)="Backup" Or ([Q All Qualified
Weapons].WpnTyp)="AR15"));

******

I really appreciate any input.
 
G

Guest

Universal duplicate record query,
It will give you your duplicate records:

Select DuplicateField
FROM Tablename
GROUP BY DuplicateField
HAVING COUNT(DuplicateField) > 1

You can also include more fields. Make sure to update the Group BY and
Having with the additional fields in that case.

good luck
e

[email protected] said:
I am working on a database for a police department tracking the weapons
that officers have qualified with at any time. I have two tables -
tbOFFID & Qualify.

Important fields:
[tbOFFID]:
ID# (primary key, also employee number)
Name
Rank
[Qualify]:
OffID (3 digit numerical employee ID, same as tbOFFID.ID#)
WpnTyp (types of weapons: Primary, Backup, AR15, etc)
Make
Serial
Model
QualDate (qualification date)

The major issue I am facing is getting rid of records with duplicates.
The duplicate field I would like to delete is Serial. If an officer
qualifies every year with a particular weapon, each record will have
the same serial number, but different QualDate values. Makes are also
entered differently in some places: "H&K" vs "H & K", so the records
are not true duplicates.

The goal: to get a list (into a report) of all weapons that an officer
has qualified with, but only list the weapons (by serial) once.

I have tried several methods, and am not able to get the final result.
Here are the two queries I tried last. The first is to get all fields
together in one table, which shows duplicate serials. The second was to
attempt to get rid of the duplicate serials. Both are SQL from Access,
with no added code by me.

SELECT Qualify.OffID, tbOFFID.Rank, tbOFFID.Name, Qualify.Make,
Qualify.Model, Qualify.Serial, Qualify.WpnTyp, Qualify.QualDate
FROM Qualify INNER JOIN tbOFFID ON Qualify.OffID = tbOFFID.[ID#]
WHERE (((Qualify.OfcOwn)=True));

******

SELECT [Q All Qualified Weapons].OffID AS [OffID Field], [Q All
Qualified Weapons].Rank AS [Rank Field], [Q All Qualified
Weapons].Name, [Q All Qualified Weapons].Make AS [Make Field], [Q All
Qualified Weapons].Model AS [Model Field], First([Q All Qualified
Weapons].Serial) AS [Serial Field], Last([Q All Qualified
Weapons].QualDate) AS LastOfQualDate, [Q All Qualified Weapons].WpnTyp
FROM [Q All Qualified Weapons]
GROUP BY [Q All Qualified Weapons].OffID, [Q All Qualified
Weapons].Rank, [Q All Qualified Weapons].Name, [Q All Qualified
Weapons].Make, [Q All Qualified Weapons].Model, [Q All Qualified
Weapons].WpnTyp, [Q All Qualified Weapons].OffID, [Q All Qualified
Weapons].Rank, [Q All Qualified Weapons].Make, [Q All Qualified
Weapons].Model, [Q All Qualified Weapons].Serial, [Q All Qualified
Weapons].OffID
HAVING ((([Q All Qualified Weapons].WpnTyp)="Primary Duty Weapon" Or
([Q All Qualified Weapons].WpnTyp)="Backup" Or ([Q All Qualified
Weapons].WpnTyp)="AR15"));

******

I really appreciate any input.
Click to expand...
 
N

normalit

That is helpful, but that only shows the records that have the
duplicate serial numbers. How can I show those, plus the ones that only
appear once (ie all unique serial numbers)
 
G

Gary Walter

If I understand correctly, you don't have
so much "duplicates" as more than one
record for an officer/Serial with different
QualDate's?

Might I suggest a different tact?

I hate losing data...

If you were to add 2 fields to table Qualify:

QID autonumber, primary key (maybe this already exists?)
fActive Yes/No Default -1

qryLatestQualDate

SELECT
t.OffID,
t.Serial,
Max(t.QualDate) As LatestQualDate
FROM
Qualify As t
GROUP BY
t.OffID,
t.Serial;


UPDATE Qualify As Q
SET Q.fActive = 0
WHERE (Q.QID)
In
(SELECT
Tmp.QID
FROM
Qualify AS Tmp
LEFT JOIN
qryLatestQualDate As q
ON
Tmp.OffID = q.OffID
AND
Tmp.Serial = q.Serial
AND
Tmp.QualDate = q.LatestQualDate
WHERE
q.OffID IS NULL);

I could imagine a main form bound to
tbOFFID, with subform bound to query for Qualify
with command button to rewrite recordsource
of subreport to filter for only fActive or not,
sorted by Serial and QualDate DESC.

==========
or, if you don't want to maintain "fActive,"
for a subreport....

qryrptSubQualifiedWeapons

SELECT
t.OffID,
t.WpnTyp,
t.Make,
t.Serial,
t.Model,
t.QualDate
FROM
Qualify AS t
INNER JOIN
qryLatestQualDate As q
ON
t.OffID = q.OffID
AND
t.Serial = q.Serial
AND
t.QualDate = q.LatestQualDate

{assuming main report bound to "tbOFFID"
and master/child link on tblOFFID.[ID#] to
qryrptSubQualifiedWeapons.OffID}

------------
or, if don't want a subreport,
then recordsource for single report.....

SELECT
O.Name,
O.Rank,
t.WpnTyp,
t.Make,
t.Serial,
t.Model,
t.QualDate
FROM
(tbOFFID As O
INNER JOIN
Qualify AS t
ON
O.[ID#] = t.OffID)
INNER JOIN
qryLatestQualDate As q
ON
t.OffID = q.OffID
AND
t.Serial = q.Serial
AND
t.QualDate = q.LatestQualDate;

normalit said:
I am working on a database for a police department tracking the weapons
that officers have qualified with at any time. I have two tables -
tbOFFID & Qualify.

Important fields:
[tbOFFID]:
ID# (primary key, also employee number)
Name
Rank
[Qualify]:
OffID (3 digit numerical employee ID, same as tbOFFID.ID#)
WpnTyp (types of weapons: Primary, Backup, AR15, etc)
Make
Serial
Model
QualDate (qualification date)

The major issue I am facing is getting rid of records with duplicates.
The duplicate field I would like to delete is Serial. If an officer
qualifies every year with a particular weapon, each record will have
the same serial number, but different QualDate values. Makes are also
entered differently in some places: "H&K" vs "H & K", so the records
are not true duplicates.

The goal: to get a list (into a report) of all weapons that an officer
has qualified with, but only list the weapons (by serial) once.

I have tried several methods, and am not able to get the final result.
Here are the two queries I tried last. The first is to get all fields
together in one table, which shows duplicate serials. The second was to
attempt to get rid of the duplicate serials. Both are SQL from Access,
with no added code by me.

SELECT Qualify.OffID, tbOFFID.Rank, tbOFFID.Name, Qualify.Make,
Qualify.Model, Qualify.Serial, Qualify.WpnTyp, Qualify.QualDate
FROM Qualify INNER JOIN tbOFFID ON Qualify.OffID = tbOFFID.[ID#]
WHERE (((Qualify.OfcOwn)=True));

******

SELECT [Q All Qualified Weapons].OffID AS [OffID Field], [Q All
Qualified Weapons].Rank AS [Rank Field], [Q All Qualified
Weapons].Name, [Q All Qualified Weapons].Make AS [Make Field], [Q All
Qualified Weapons].Model AS [Model Field], First([Q All Qualified
Weapons].Serial) AS [Serial Field], Last([Q All Qualified
Weapons].QualDate) AS LastOfQualDate, [Q All Qualified Weapons].WpnTyp
FROM [Q All Qualified Weapons]
GROUP BY [Q All Qualified Weapons].OffID, [Q All Qualified
Weapons].Rank, [Q All Qualified Weapons].Name, [Q All Qualified
Weapons].Make, [Q All Qualified Weapons].Model, [Q All Qualified
Weapons].WpnTyp, [Q All Qualified Weapons].OffID, [Q All Qualified
Weapons].Rank, [Q All Qualified Weapons].Make, [Q All Qualified
Weapons].Model, [Q All Qualified Weapons].Serial, [Q All Qualified
Weapons].OffID
HAVING ((([Q All Qualified Weapons].WpnTyp)="Primary Duty Weapon" Or
([Q All Qualified Weapons].WpnTyp)="Backup" Or ([Q All Qualified
Weapons].WpnTyp)="AR15"));

******

I really appreciate any input.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Displaying records that have one of two fields equal to Master Field 3

Top