Duplicate Values

[Brethser]

Hello i'm relatively new to Access and am looking for a way to filter
duplicate values.

I have a database that stores the repair info for PDA's. Each PDA is not
unique and can be shipped to any location regardless of where it came from.
When i ship out multiple units to one location they are then swapped for old
units, refurbished and sent to the next site.

I have a query that shows the movement of all devices but there are
duplicates due to sites shipping back previously refurbished units. Is it
possible to use an IF statement that compares all the Serial numbers and
where there are duplicates, only display the one which was shipped to the
site last.

Like
IF(TableName[S/N=S/N], Display S/N >Date)

This way it will only display the location of where the unit is now rather
than displaying 2 or 3 Locations when the PDA is Mistakenly shipped back and
refurbished..

Any help would be greatly appreciated,
Regards
Brethser

 

[Allen Browne]

Create a query that returns the latest date for the serial number, e.g.:

Select [S/N], Max([ServiceDate]) AS LastServiced
FROM Table1
GROUP BY [S/N];

You can now create another query, using this one and your origional table as
input 'tables.' Join them on both fields. It shows only the most recent
record.

Suggestions to make this bombproof:

a) Don't use Date as a field name.
(Access may misunderstand it as the system date.)

b) Consider creating a unique index on the combination of [S/N] +
ServiceDate. Without that, you can't be sure which is the 'last' record for
a serial number.

 

[Hans- Anders Damkier]

Hello i'm relatively new to Access and am looking for a way to filter
duplicate values.

I have a database that stores the repair info for PDA's. Each PDA is not
unique and can be shipped to any location regardless of where it came
from.
When i ship out multiple units to one location they are then swapped for
old
units, refurbished and sent to the next site.

I have a query that shows the movement of all devices but there are
duplicates due to sites shipping back previously refurbished units. Is it
possible to use an IF statement that compares all the Serial numbers and
where there are duplicates, only display the one which was shipped to the
site last.

Like
IF(TableName[S/N=S/N], Display S/N >Date)

This way it will only display the location of where the unit is now rather
than displaying 2 or 3 Locations when the PDA is Mistakenly shipped back
and
refurbished..

Any help would be greatly appreciated,
Regards
Brethser i DONT ANYTHING ABOUT YUOR PROBLEMS FOR ME ITS ONLY
AAANNHDJFUJKGKG,G,

 

[Brethser]

Hello Allen,
Thanks for the suggestion but i run into a problem everytime i try and use
it in another query, I am trying to Design a query that shows the movement of
all units. The Max Function works fine but when i link it to another table to
get the other info required it returns duplicates again.

BuildTable *Build Table contains duplicates due to repaired units
being
ShipID returned and rebuilt. The MAX function works but
when i link
Asset Number it back to the Build table to get the other fields it
returns the
S/N the duplicates.
Status
Date

ShipTable *Ship Table contains details on the Site and is
linked to the
ShipID Build Table via the Ship ID. When this is added
to the query
Location it returns more info on the duplicates due to
their different
Address ShipID.
Date

RepairTable *Repair Table contains similar data to Build Table
but with
Asset Number the units sim number included. This also contains the
S/N same duplicates and is linked via the asset
number.
Sim My overall query will show the Status(BT),
Asset(BT),
Date S/N(BT), Location(ST), Sim(RT) and Date(ST)


*BT = Build Table etc...

WHen the 3 tables are linked together in the query the 2 units which have
duplicates return with 8 versions each. so asset number 007 is repeated 8
times with different Location, Sim, S/N(S/N change after repair, Asset
don't). So id i have unique 80 units repaired but have really repaired and
shipped 90, how do i just show where the 80 units are now

Regards
Brethser

Create a query that returns the latest date for the serial number, e.g.:

Select [S/N], Max([ServiceDate]) AS LastServiced
FROM Table1
GROUP BY [S/N];

You can now create another query, using this one and your origional table as
input 'tables.' Join them on both fields. It shows only the most recent
record.

Suggestions to make this bombproof:

a) Don't use Date as a field name.
(Access may misunderstand it as the system date.)

b) Consider creating a unique index on the combination of [S/N] +
ServiceDate. Without that, you can't be sure which is the 'last' record for
a serial number.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello i'm relatively new to Access and am looking for a way to filter
duplicate values.

I have a database that stores the repair info for PDA's. Each PDA is not
unique and can be shipped to any location regardless of where it came
from.
When i ship out multiple units to one location they are then swapped for
old
units, refurbished and sent to the next site.

I have a query that shows the movement of all devices but there are
duplicates due to sites shipping back previously refurbished units. Is it
possible to use an IF statement that compares all the Serial numbers and
where there are duplicates, only display the one which was shipped to the
site last.

Like
IF(TableName[S/N=S/N], Display S/N >Date)

This way it will only display the location of where the unit is now rather
than displaying 2 or 3 Locations when the PDA is Mistakenly shipped back
and
refurbished..

Any help would be greatly appreciated,
Regards
Brethser

 

[Brethser]

####### EASIER TO READ#################
####### EASIER TO READ#################
####### EASIER TO READ#################
####### EASIER TO READ#################
Hello Allen,
Thanks for the suggestion but i run into a problem everytime i try and use
it in another query, I am trying to Design a query that shows the movement of
all units. The Max Function works fine but when i link it to another table to
get the other info required it returns duplicates again.

BuildTable
ShipID
Asset Number
S/N
Status
Date

*Build Table contains duplicates due to repaired unitsbeing returned and
rebuilt. The MAX function works butwhen i link it back to the Build table to
get the other fields it returns the duplicates.

ShipTable
ShipID
Location
Address
Date

*Ship Table contains details on the Site and islinked to the Build Table
via the Ship ID. When this is added to the query it returns more info on the
duplicates due to their different ShipID.


RepairTable
Asset Number
S/N
Sim
Date

*Repair Table contains similar data to Build Table but with the units sim
number included. This also contains the same duplicates and is linked via the
asset
number. My overall query will show the Status(BT), Asset(BT), Date S/N(BT),
Location(ST), Sim(RT) and Date(ST)


*BT = Build Table etc...

WHen the 3 tables are linked together in the query the 2 units which have
duplicates return with 8 versions each. so asset number 007 is repeated 8
times with different Location, Sim, S/N(S/N change after repair, Asset
don't). So id i have unique 80 units repaired but have really repaired and
shipped 90, how do i just show where the 80 units are now

Regards
Brethser

 

[Allen Browne]

You will need to define some way to choose which record is the most recent.

Making the date unique is one way. Otherwise you will need other techniques.

4 techniques are discussed here:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm